topic Re: How to search for all IP's not in a lookup table. in Splunk Search

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

turnerde — Wed, 21 Mar 2018 20:56:22 GMT

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

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

elliotproebstel — Wed, 21 Mar 2018 21:36:10 GMT

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)

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

jnussbaum_splun — Wed, 21 Mar 2018 21:38:02 GMT

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.

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

turnerde — Thu, 22 Mar 2018 13:53:50 GMT

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.

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

elliotproebstel — Thu, 22 Mar 2018 16:10:06 GMT

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.