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
Contributor

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...