topic Re: How do you make a search that returns hosts that haven't checked in to an external source within a certain time frame? in 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 — Tue, 20 Nov 2018 04:41:56 GMT

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.

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

martin_mueller — Tue, 29 Sep 2020 22:05:32 GMT

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.

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

jj39501 — Tue, 20 Nov 2018 21:36:57 GMT

Martin,

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

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

mstjohn_splunk — Tue, 20 Nov 2018 23:09:58 GMT

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!

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

woodcock — Tue, 20 Nov 2018 23:54:52 GMT

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

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

jj39501 — Wed, 21 Nov 2018 02:36:39 GMT

This worked out thanks again. Greatly appreciated.

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

jj39501 — Wed, 21 Nov 2018 02:37:40 GMT

Woodcock,

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

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

woodcock — Wed, 21 Nov 2018 02:51:11 GMT

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