Splunk Search

Help with Search for sporadic servers in the past 14 days?

andrew_burnett
Path Finder

Trying to build a search looking for sporadic servers in the past 14 days, here is my search so far.

| tstats count as hourcount where (index=_* OR index=*) by _time, host span=1h
| appendpipe [
| stats count by host
| addinfo
| eval _time = mvappend(info_mintime,info_maxtime)
| stats values(_time) as Time by host
| mvexpand Time
| rename Time as _time
]
| sort 0 _time host
| streamstats time_window=24h count as skipno by host
| where skipno = 1
| stats sum(skipno) as count by host
| eval mySporadicFlag = if(count=1,"no","yes")

 

But how the streamstats is set up, and the filtering. Every host starts at 1, the first time an event was encountered in the first 14 days. So it's flagging all my hosts as sporadic despite  there being no gap. Any assistance?

Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I am not 100% sure what it is that you are trying to do - assuming you want to count how many hours per day each host has no events, you could try something like this

| tstats count as hourcount where (index=_* OR index=*) by _time, host span=1h
| appendpipe [
| stats count by _time host
| timechart count by host span=1h usenull=f useother=f limit=0
| untable _time host count
]
| where count=0
| timechart span=24h count by host
| untable _time host count
0 Karma

andrew_burnett
Path Finder

@ITWhisperer  So I am trying to find sporadic hosts, or hosts that will have over 24-hour gaps or maybe just 24-hour gaps in between sending data to indexers. My search looks like this 

| tstats count as hourcount where (index=_* OR index=*) by _time,host span=1h
| appendpipe [
| stats count by host
| addinfo
| eval _time = mvappend(info_mintime,info_maxtime)
| stats values(_time) as Time by host
| mvexpand Time
| rename Time as _time
]
| sort 0 _time host
| streamstats time_window=24h count as skipno by host
| where skipno=1 AND _time>relative_time(now(),"-13d@d")
| stats sum(skipno) as count by host
| eval mySporadicFlag = if(count=1,"yes","no")

Except the problem is if it reports every 48 hours in a 14-day period, that's sporadic but the streamstats count would be higher than 1. But if you reversed the yes and no, than everything would be sporadic even hosts that only have minute gaps in data. So I'm stuck on how to improve this search from here to find actually sporadic hosts.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Like @ITWhisperer , I struggle to understand the exact requirement.  If you have a search window of 14 days, and your criterion for sporadic flag is not reporting in 24 hours, would it suffice to just count how many days each server has report?

| tstats count as hourcount where (index=_* OR index=*) earliest=-14d by _time, host span=24h
| stats dc(_time) by host
| eval mySporadicFlag = if('dc(_time)' < 14, "yes", "no")

If you want to use natural day boundary, bound search window and span accordingly. 

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...