How to filter list of hosts from 2 lookup tables?

[pavanae]

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

[DalJeanis]

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

[elliotproebstel · Accepted 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.

[elliotproebstel]

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]

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

[elliotproebstel]

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