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
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.
@pavanae - if the answer has solved your issue, please accept the answer so the question will show as closed.
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.
One more option I just thought of:
| inputlookup total_hosts.csv | search NOT [ | inputlookup hostnames.csv | format ]
I think that's the proper syntax.
@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.
Thanks for the feedback. 🙂 Getting this kind of guidance is the best perk of participating in Splunk Answers.