Splunk Search

Limit search results to items from lookup table

Path Finder

I have a lookup table which includes a list of IP addresses (field name = ip). I am trying to compose a search which will display only items from my lookup table with a status indicating whether they were found in the index or not.

For example if my lookup table contains 192.168.1.100, 192.168.1.101 and then the index has events with 192.168.1.100 and 192.168.1.102, my results should be something like this:

192.168.1.100 - ipExists
192.168.1.101 - ipNotExists

Note that values 192.168.102 was not listed since it was not found in the lookup table.

I can get the matching results easy enough but I'm not sure how to get the results that don't match without it including all items in the index, even if they are not found in my lookup table.

This is what I have so far which shows matches, but the non matches include items not in the lookup table. In the end, I just want to have an output containing all the IPs listed in my lookup table with a status indicating that they were found/not found.

eventtype=myindex
| lookup mylookup local=true ip OUTPUT ip as matched
| eval matched=if(isnull(matched), "ipExists", "ipNotExists") 
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Going by your requirment of

 I am trying to compose a search which will search an index for these addresses and show me the IPs which were found and those that were not found but exclude results that were not listed in the table.

Give this a try (Updated removed NOT from base search)

eventtype=myindex [| inputlookup mylookup | table ip]
| stats count by ip 
| append [| inputlookup mylookup | table ip | eval count=0]
| stats sum(count) as count by ip
| eval matched=if(count>0,"ipExists", "ipNotExists") 

View solution in original post

SplunkTrust
SplunkTrust

Going by your requirment of

 I am trying to compose a search which will search an index for these addresses and show me the IPs which were found and those that were not found but exclude results that were not listed in the table.

Give this a try (Updated removed NOT from base search)

eventtype=myindex [| inputlookup mylookup | table ip]
| stats count by ip 
| append [| inputlookup mylookup | table ip | eval count=0]
| stats sum(count) as count by ip
| eval matched=if(count>0,"ipExists", "ipNotExists") 

View solution in original post

Path Finder

Thanks for the response. I updated my question to make it more clear. Your answer returns results that were not found in my lookup table. In the end, I only want to see the results that contain IP addresses from my lookup table. All others should be excluded.

0 Karma

SplunkTrust
SplunkTrust

I made the mistake (had NOT in the base search which was returning data which were NOT in lookup). Try the updated answer.

0 Karma

Path Finder

That's works perfectly. Thanks for the help!

0 Karma

SplunkTrust
SplunkTrust

change that to isnotnull()

0 Karma

Path Finder

Thanks for responding. I updated my question to make it more clear. Changing to isnotnull() did not achieve the end result I was looking for. The results still contained items not found in my lookup table.

0 Karma