Splunk Search

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

Builder

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.

0 Karma
1 Solution

Legend

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

View solution in original post

Legend

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

View solution in original post

Super Champion

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

SplunkTrust
SplunkTrust

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

0 Karma

Builder

Hello, Can you share your search query?

0 Karma