Splunk Search

How to filter list of hosts from 2 lookup tables?

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

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

SplunkTrust
SplunkTrust

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

0 Karma

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

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

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

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!