topic Re: How to filter list of hosts from 2 lookup tables? in Splunk Search

How to filter list of hosts from 2 lookup tables?

pavanae — Tue, 29 Sep 2020 16:21:07 GMT

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

Re: How to filter list of hosts from 2 lookup tables?

elliotproebstel — Tue, 29 Sep 2020 16:17:49 GMT

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.

Re: How to filter list of hosts from 2 lookup tables?

elliotproebstel — Thu, 19 Oct 2017 19:27:32 GMT

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

Re: How to filter list of hosts from 2 lookup tables?

DalJeanis — Mon, 23 Oct 2017 02:57:34 GMT

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

Re: How to filter list of hosts from 2 lookup tables?

DalJeanis — Mon, 23 Oct 2017 02:59:14 GMT

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

Re: How to filter list of hosts from 2 lookup tables?

elliotproebstel — Mon, 23 Oct 2017 18:35:57 GMT

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