Splunk Search

Find values in lookup that are not in index

katmagee
Engager

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
0 Karma

to4kawa
Ultra Champion
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.

0 Karma

masonmorales
Influencer

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
0 Karma

katmagee
Engager

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+

0 Karma

Taruchit
Communicator

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.

 

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...