How to filter out the list of hosts that are in the lookup but not in my search result?

[pavanae]

I have a query as follows which displays the list of hosts and their host details as follows

host    field_A   field_B    field_C

Now I have an Excel sheet which I'm trying to use as a lookup "hosts_list.csv"

Now I am trying to filter out the list of hosts that are in the lookup but not in my query result.

[gcusello · Accepted solution]

Hi pavanae,
try something like this

your_search
| eval host=upper(host)
| stats count by host
| append [ | inputlookup host_lookup.csv | eval host=upper(host), count=0 | fields host count ]
| stats sum(count) AS Total by host
| where Total=0

in this way you have the list of the hosts from your lookup that doesn't match in the main search.
Bye.
Giuseppe

[cmerriman]

the set command can help you, i think. your question didn't post the search query, make sure you use the 101 010 code button to format it properly.

|set diff [search 1] [|inputlookup hosts_list.csv]

http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Set

[MuS]

and should this not work, just read this answer https://answers.splunk.com/answers/73268/search-for-hosts-in-a-lookup-but-not-in-splunk.html to get a different approach to make it work 😉

cheers, MuS

[lloydknight]

Hello, Can you share your search query?