Splunk Search

How do you make a search that returns hosts that haven't checked in to an external source within a certain time frame?

jj39501
New Member

Hello All,

I am relatively new to Splunk and need some help on this search query. I have hosts that are required to check in periodically to an external source. However, I want to know what host have failed to do so in, lets say, the last 24 hours. Here is what I have so far.

sourcetype="web" src_requires_av=true dest=requiredsite.com | table src_ip, src_nt_host, src_mac src_bunit | dedup src_ip, src_mac

This outputs all devices that have successfully checked in, but I want the output to be for devices that have not checked in.

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Make a list of hosts that should check in and compare the list with your search results, or search over more than 24 hours and filter for hosts that have checked in but their latest check-in is older than 24 hours. Examples:

sourcetype="web" src_requires_av=true dest=requiredsite.com earliest=-48h latest=now | dedup src_ip, src_mac | table _time src_ip, src_nt_host, src_mac src_bunit | where _time < relative_time(now(), "-24h")

sourcetype="web" src_requires_av=true dest=requiredsite.com earliest=-24h latest=now | dedup src_ip, src_mac | table _time src_ip, src_nt_host, src_mac src_bunit | inputlookup append=t hosts_that_should_check_in | stats latest(_time) as latest_time by src_ip, src_nt_host, src_mac src_bunit | where isnull(latest_time)

Note, I've swapped dedup and table for significantly better performance in a distributed search environment. This way indexers can dedup before sending the table back to the search head.
The list in my example would need to contain src_ip, src_nt_host, src_mac src_bunit.

View solution in original post

0 Karma

woodcock
Esteemed Legend

Run this search for at least Last 24-hours:

| tstats max(_indextime) AS lastSentTime WHERE index=* OR index=_* BY host sourcetype
| eval silentTime = now() - lastSentTime
| eval silentForTwelveHours = if(silentTime > (12 * 60 * 60), "***YES***", "no")
| eval silentTime = tostring(silentTime, "duration")
| eventstats max(lastSentTime) AS lastSentTimeHost count(eval(silentForTwelveHours=="***YES***")) AS silent BY host
| where silent>0
| stats list(sourcetype) list(lastSentTime) list(silentTime) list(silentForTwelveHours) first(lastSentTimeHost) BY host

There are MANY ways to crack this nut. Also, be aware that this is the Sentinel Search problem discussed (with solution) here:

https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

0 Karma

jj39501
New Member

Woodcock,

Thanks for you help as well. I will keep this in mind if variables in my environment change.

0 Karma

woodcock
Esteemed Legend

Posters cannot accept more than on answer but anything can get UpVoted...

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Make a list of hosts that should check in and compare the list with your search results, or search over more than 24 hours and filter for hosts that have checked in but their latest check-in is older than 24 hours. Examples:

sourcetype="web" src_requires_av=true dest=requiredsite.com earliest=-48h latest=now | dedup src_ip, src_mac | table _time src_ip, src_nt_host, src_mac src_bunit | where _time < relative_time(now(), "-24h")

sourcetype="web" src_requires_av=true dest=requiredsite.com earliest=-24h latest=now | dedup src_ip, src_mac | table _time src_ip, src_nt_host, src_mac src_bunit | inputlookup append=t hosts_that_should_check_in | stats latest(_time) as latest_time by src_ip, src_nt_host, src_mac src_bunit | where isnull(latest_time)

Note, I've swapped dedup and table for significantly better performance in a distributed search environment. This way indexers can dedup before sending the table back to the search head.
The list in my example would need to contain src_ip, src_nt_host, src_mac src_bunit.

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

hi @jj39501

Did this answer solve your problem? If so, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

0 Karma

jj39501
New Member

Martin,

Thank you for the prompt feedback. I will give this a shot and let you know how it goes.

0 Karma

jj39501
New Member

This worked out thanks again. Greatly appreciated.

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