Splunk Search

How to search for all IP's not in a lookup table.

turnerde
New Member

Basically I want to use the inputlookup myspreadsheet.csv and I want to find all IP's that are not in that .csv file.

Tags (1)
0 Karma

jnussbaum_splun
Splunk Employee
Splunk Employee

Assuming that you're wanting to exclude IPs that you're sourcing from a lookup against an index or other lookup, you could achieve this by doing:

   index=myindex sourcetype=mysourcetype src_ip=* NOT [|inputlookup mylookup.csv | stats count by src_ip | fields - count] | stats count by src_ip | fields - count

If the IP field in your lookup differs from your indexed data, you can change via |eval

Hope this helps.

elliotproebstel
Champion

One way, assuming the events contain a field called ip and the lookup contains a field called ip_address:

index=something NOT
[| inputlookup myspreadsheet.csv
 | fields ip
 | rename ip AS ip_address
 | format ]
| stats values(ip_address)

Another way:

index=something 
| stats values(ip_address) AS ip_address
| lookup myspreadsheet.csv ip AS ip_address OUTPUT ip AS flag
| where isnull(flag)
0 Karma

turnerde
New Member

Just as a "so I know" follow up. What do the [ ] brackets do/represent in the query? I did modify it a bit to have it organized and charted out but for the most part I believe this is working.

0 Karma

elliotproebstel
Champion

Brackets are used in a Splunk query as the syntax for a subsearch. In this case, the subsearch is returning a list of ip addresses to be used as a search filter.

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 ...