Splunk Search

Limit search results to items from lookup table

yemyslf
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

somesoni2
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

somesoni2
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") 

yemyslf
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

somesoni2
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

yemyslf
Path Finder

That's works perfectly. Thanks for the help!

0 Karma

starcher
SplunkTrust
SplunkTrust

change that to isnotnull()

0 Karma

yemyslf
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
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...