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

[turnerde]

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

[jnussbaum_splun]

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]

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)

[turnerde]

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.

[elliotproebstel]

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.