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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...