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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...