Splunk Search

How to filter list of hosts from 2 lookup tables?

pavanae
Builder

I have a lookup search as follows

|inputlookup hostnames.csv

Which displays the results as follows

my_hostname

abc.com
fgb.com

Now I have another lookup as follows

| inputlookup total_hosts.csv | rename Hostname as my_hostname | table my_hostname

Which displays the results as follows

my_hostname

xyz.abc.com
abc.com
fgb.com
yhk
kjhgd.com

Now how can I filter the list of hosts that were in total_hosts.csv and not in the hostnames.csv. I'm just trying to see the list of hosts that were missing in hostnames.csv by comparing with hosts on total_hosts.csv

0 Karma
1 Solution

elliotproebstel
Champion

Here's how I would do that:
| inputlookup total_hosts.csv | lookup hostnames.csv my_hostname OUTPUT my_hostname AS found_hostname | where isnull(found_hostname)

Or if you are certain that all all the hostnames in hostnames.csv are in total_hosts.csv (so total_hosts.csv list is a strict superset of hostnames.csv), then I believe this should work:
| set diff [ | inputlookup total_hosts.csv ] [| inputlookup hostnames.csv]
But be forewarned that if there are any entries in hostnames.csv that weren't in total_hosts.csv, then they will also show up in this result set.

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

@pavanae - if the answer has solved your issue, please accept the answer so the question will show as closed.

0 Karma

elliotproebstel
Champion

Here's how I would do that:
| inputlookup total_hosts.csv | lookup hostnames.csv my_hostname OUTPUT my_hostname AS found_hostname | where isnull(found_hostname)

Or if you are certain that all all the hostnames in hostnames.csv are in total_hosts.csv (so total_hosts.csv list is a strict superset of hostnames.csv), then I believe this should work:
| set diff [ | inputlookup total_hosts.csv ] [| inputlookup hostnames.csv]
But be forewarned that if there are any entries in hostnames.csv that weren't in total_hosts.csv, then they will also show up in this result set.

elliotproebstel
Champion

One more option I just thought of:
| inputlookup total_hosts.csv | search NOT [ | inputlookup hostnames.csv | format ]
I think that's the proper syntax.

DalJeanis
SplunkTrust
SplunkTrust

@elliotproebstel - Your first answer is the best technically, in my opinion. NOTs are inefficient, and set diff, as you noted, doesn't give any indication of which set the extra record may have been in.

0 Karma

elliotproebstel
Champion

Thanks for the feedback. 🙂 Getting this kind of guidance is the best perk of participating in Splunk Answers.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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