Display hosts that didn't have events only

[Raghav2384]

Hey There,
I have a list of 150 servers which listed in a csv file (lookup table). Here's my current search earliest = -15m latest=now [inputlookup "Corp_Hosts.csv"|rename Host_Name as host|fields host]|stats count by host and i get 70 hosts as a result with events. How can i table list of hosts that didn't have any events? In this case the rest of the 80 only?

Thank you in advance,

Cheers,
Raghav

[sideview · Accepted solution]

To qualify this answer -- join is almost never the right answer - generally anything you might think to do with join is better done with stats or with a lookup. However here is one case where join gives you a nice short path and as long as the lookup does not become very large it will be reliable.

earliest = -15m latest=now 
| stats count by host 
| join type="outer" [ | inputlookup "Corp_Hosts.csv" | rename Host_Name as host | fields host]
| stats sum(count) as count by host
| search count=0

For more normal caveats about the join command - http://answers.splunk.com/answers/822/simulating-a-sql-join-in-splunk

For fun here's a second way that uses a subsearch. The use of the format command here will yield NOT host=host1 NOT host=host2 NOT host=host3 etc. so the end result is you'll get search results that are the hosts that had no events.

| inputlookup "Corp_Hosts.csv" 
| rename Host_Name as host 
[ search earliest = -15m latest=now 
| stats count by host
| fields host
| format "" "NOT" "" "" "" ""]

More about subsearches - http://docs.splunk.com/Documentation/Splunk/6.0.3/Search/Aboutsubsearches

[somesoni2]

Please accept the answer if there are not followup quetions.

[Raghav2384]

Excellent That worked!!!!

[sideview]

Oh sorry - yea I made a bad assumption that the hosts lookup contained all indexed hosts - somesoni2's modified version of the first search will work for you.

[somesoni2]

Try the second option (subsearch) OR try this

| inputlookup "Corp_Hosts.csv"
| rename Host_Name as host | search NOT [search earliest = -15m latest=now
| stats count by host
| fields - count
| format]

[Raghav2384]

Hi,
I tried the first part, instead of comparing the eventful hosts with the csv, it actually lists the entire host list in the environment.Any other suggestions?

Thanks,
Raghav