topic How do I get a TRUE average event count per hour grouped by a single field? in Splunk Search

How do I get a TRUE average event count per hour grouped by a single field?

RDurica — Wed, 24 Jul 2019 20:38:59 GMT

I'd like to assess how many events I'm getting per hour for each value of the signature field. However, stats calculates an average that excludes the hours that don't return any events (i.e., this isn't a true average of events per hour). I know how to accomplish this if I'm using a static time scope - however, I'd really like to leverage this search in a dashboard with a timepicker. My search is as follows...

| mvexpand signature 
| bucket _time span=1hour 
| stats count by signature,_time
| stats avg(count) as average by signature
| eval average=round(average,2)
| sort - average

Re: How do I get a TRUE average event count per hour grouped by a single field?

woodcock — Fri, 26 Jul 2019 21:04:25 GMT

Just switch to timechart which fills timeslots with 0 values by default:

... | mvexpand signature 
| timechart span=1h limit=0 count BY signature
| untable _time signature count
| stats avg(count) AS average BY signature
| eval average=round(average, 2)
| sort 0 - average

Re: How do I get a TRUE average event count per hour grouped by a single field?

RDurica — Wed, 31 Jul 2019 13:34:24 GMT

Thank you much woodcock! This did the trick. I wasn't aware of the "untable" command, so thank you for the quick lesson.