topic Re: Find values in lookup that are not in index in Splunk Search

Find values in lookup that are not in index

katmagee — Fri, 01 May 2020 15:02:48 GMT

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

Re: Find values in lookup that are not in index

masonmorales — Fri, 01 May 2020 22:29:48 GMT

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

Re: Find values in lookup that are not in index

to4kawa — Fri, 01 May 2020 22:59:07 GMT

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.

Re: Find values in lookup that are not in index

katmagee — Mon, 11 May 2020 16:06:38 GMT

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+

Re: Find values in lookup that are not in index

Taruchit — Wed, 18 May 2022 12:46:16 GMT

Hi @katmagee

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

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.