topic Re: Display hosts that didn't have events only in Getting Data In

Display hosts that didn't have events only

Raghav2384 — Mon, 28 Sep 2020 16:28:15 GMT

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

Re: Display hosts that didn't have events only

sideview — Fri, 25 Apr 2014 07:24:23 GMT

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

Re: Display hosts that didn't have events only

Raghav2384 — Fri, 25 Apr 2014 14:56:21 GMT

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

Re: Display hosts that didn't have events only

somesoni2 — Mon, 28 Sep 2020 16:28:37 GMT

Try the second option (subsearch) OR try this

Re: Display hosts that didn't have events only

sideview — Fri, 25 Apr 2014 18:22:43 GMT

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.

Re: Display hosts that didn't have events only

Raghav2384 — Thu, 01 May 2014 16:24:10 GMT

Excellent That worked!!!!

Re: Display hosts that didn't have events only

somesoni2 — Thu, 01 May 2014 17:26:03 GMT

Please accept the answer if there are not followup quetions.