stats by date_hour add zero count for hours with no events

[ickymettle]

Hi folks,

I'm working on a search to return the number of events by hour over any specified time period. At the moment i've got this on the tail of my search:

... | stats count by date_hour | sort date_hour

I want this search to return the count of events grouped by hour for graphing.

This for the most part works. However if the search returns no events for a given hour, that hour doesn't appear in the resulting table.

Is there a way to modify this to essentially add 0's for the hours with no events? Given stats is only aggregating on fields that exist in the result data and it isn't really a "time" aware function I can't see a solution.

Is there even a better way do do this? This is for a dashboard where I want to graph the busiest time of day across a given time range and want the query flexible enough to just be able to change the time range (7d, last month, last year).

Thanks, Marcus

[ziegfried · Accepted solution]

I had a similar problem in the past and solved it by implementing a custom search commands that added the missing elements (days in my case). But I think your query can be solved using a combination of timechart, eval and stats:

... | timechart span=1h count | eval hour=strftime(_time,"%H:00") | stats sum(count) as count by hour

[ickymettle]

this did the trick nicely, thanks!

[ftk]

If you are just doing this for graphing, I recommend using timechart instead of stats. You can tell timechart to use spans of 1 hour, and for every hour with 0 events you can configure whether you want to treat this as 0 (null) or simply connect with the other data data points.

Try using it like so:

... | timechart span=1h count

[ickymettle]

this works fine for a 24 hour time range, however in my particular use case I needed to sum the events by hour over multiple days to graph the busiest hour of the day over more than 1 day