Find values in lookup that are not in index

[katmagee]

I've searched and tried what i can find online and nothing is returning so i thought I'd try here: i need to return the 45 or so values that are in my csv lookup, but NOT in my firewall index. One of the issues I ran into was I had to transform the index values to be able to match the string from the csv (hence all the rex) Thoughts are welcome - thank you!!

index=gcp_firewall
| rename data.jsonPayload.rule_details.reference as FW
| search FW = "network:prod*"
| rex field=FW "network:prod-a/firewall:(?.*)"
| rex field=FW "network:prod-b/firewall:(?.*)"
| rex field=FW "network:prod-c/firewall:(?.*)"
| rex field=FW "network:prod-d/firewall:(?.*)"
| rex field=FW "network:prod-e/firewall:(?.*)"
| lookup firewall-exception-prod.csv firewall_rule as fw OUTPUT firewall_rule as fw
| dedup fw
| table fw

[to4kawa]

index=gcp_firewall data.jsonPayload.rule_details.reference="network:prod*"
 | rename data.jsonPayload.rule_details.reference as FW
 | rex field=FW "network:prod-\w+/firewall:(?<<fw>>.*)"
 | lookup firewall-exception-prod.csv firewall_rule as fw OUTPUT firewall_rule as fw
 | dedup fw
 | table fw

Using rex ,between " can be able to use REGEX.
your many rex can be one liner, I guess.

[masonmorales]

It sounds like you only have one column in your lookup file. Add a second column to it, call it something like "lookup_match" and set the value to "1" for every row. Then, adjust your query like this:

 index=gcp_firewall
 | rename data.jsonPayload.rule_details.reference as FW
 | search FW = "network:prod*"
 | rex field=FW "network:prod-a/firewall:(?.*)"
 | rex field=FW "network:prod-b/firewall:(?.*)"
 | rex field=FW "network:prod-c/firewall:(?.*)"
 | rex field=FW "network:prod-d/firewall:(?.*)"
 | rex field=FW "network:prod-e/firewall:(?.*)"
 | lookup firewall-exception-prod.csv firewall_rule as fw OUTPUT lookup_match
 | search NOT lookup_match=*
 | dedup fw
 | table fw

[katmagee]

It seems like this gives me all the firewalls from the index that aren't in the csv - how do i switch that? I need the firewalls from the csv (that arent in the index)... i should get around 50 and that query gave me 1000+

[Taruchit]

Hi @katmagee 

I have a similar scenario. I tried the below approach: -

|inputlookup dummyTable.csv where index="abc" 
|fields index, host
|search NOT
[search index="abc" |dedup host |table index, host]

Please replace: -
dummyTable.csv with your lookup table name
In my case, index and host are the two common fields between lookup table and index. 

Please do share if the above approach helps to answer your problem statement.