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

[andrew_burnett]

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?

[ITWhisperer]

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

[andrew_burnett]

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

[yuanliu]

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.