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.
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.
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
Woodcock,
Thanks for you help as well. I will keep this in mind if variables in my environment change.
Posters cannot accept more than on answer but anything can get UpVoted
...
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.
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!
Martin,
Thank you for the prompt feedback. I will give this a shot and let you know how it goes.
This worked out thanks again. Greatly appreciated.