Splunk Search

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

RDurica
Engager

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
Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

View solution in original post

RDurica
Engager

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!