Getting Data In

Display hosts that didn't have events only

Raghav2384
Motivator

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

Tags (3)
1 Solution

sideview
SplunkTrust
SplunkTrust

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

View solution in original post

sideview
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

Please accept the answer if there are not followup quetions.

Raghav2384
Motivator

Excellent That worked!!!!

0 Karma

sideview
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...