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
Revered Legend

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
Revered Legend

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!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...