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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!