topic Re: Stats Count with Moving AVG for a given time in Splunk Search

Stats Count with Moving AVG for a given time

hartfoml — Mon, 28 Sep 2020 12:49:11 GMT

OK this one might be a challenge

I 7 services that restart at midnight. I have a report that comes out at 7 AM that shows the volume of events since midnight. easy so far "start at 7 check back -7 hours stats count by host" simple right. well here is the hard part. I would like to put an AVG event number next to each reported event number line this "host Todays_count AVG_count". And harder the avg count should get the avg count of events for each host from 00:00 to 07:00 each day for the last 7 days. this will make it posable from the morning report to see A. is the server reporting event (this is the primary goal) B. is the server reporting about the right number of events (Secondary goal).

Any takers!!!

this is what I have now

sourcetype=foo earliest=-7h@h | stats count as events by host | sort host

Thanks in advance for your help

Mike H.

Re: Stats Count with Moving AVG for a given time

sdaniels — Fri, 16 Nov 2012 15:04:50 GMT

This isn't elegant or efficient and someone else can hopefully make this better. This is for the last two days and you'd just have to expand it for the 7 days prior avg. I didn't know of a better way to reference your time period for the past days 7 hour window.

  sourcetype=foo earliest=-7h@h | stats count as currentDayCount by host 
    | appendcols [search sourcetype=foo earliest=-31h@h latest=-24h@h  | stats count as daybefore by host]
    | appendcols [search sourcetype=foo earliest=-55h@h latest=-48h@h | stats count as 2daysbefore by host]
    | eval avgLast2Days = (daybefore + 2daysbefore)/2 
    | table host currentDayCount avgLast2Days

Re: Stats Count with Moving AVG for a given time

jonuwz — Mon, 19 Nov 2012 14:35:41 GMT

What about :

earliest=-14d@d <other search stuff here> date_hour>0 date_hour<7 | bin _time span=1d | stats count as "Todays Count" by host _time | streamstats window=7 avg("Todays Count") as "7 day Average" by host | tail 7

add current=false to streamstats if you dont want todays values affecting todays 7day average

This gives the the 7 day average derived from the previous 7 days, per day.

If this is just an operations check and you dont really need a 'rolling' average, just the average over the previous 7 days for the current day :

earliest=-7d@d date_hour>0 date_hour<7 | bin _time span=1d | stats count by host _time | eventstats avg(count) as average by host | eval average=round(average) | where _time >= relative_time(now(),"@d") | eval Day=strftime(_time,"%Y-%m-%d") | table Day host count average

Re: Stats Count with Moving AVG for a given time

sdaniels — Mon, 19 Nov 2012 23:24:42 GMT

That looks a lot cleaner to me. @hartfomi - let us know how it goes.

Re: Stats Count with Moving AVG for a given time

pierrejordonnel — Tue, 11 Aug 2015 20:20:17 GMT

Worked like a champ for me! Love it!