Splunk Search

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

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

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

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

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