How to "fill" missing hours from a search where there are no results with a value of 0 in a chart?

[bbaisley]

I have a simple search where we are searching the logs for a specific event. We want to chart out the count of how many times that event is found each hour, irrespective of the day. We are looking to see which hours are the busiest hour.

Meaning, if the event happened at 5:00 Monday, 5:00 Tuesday and 6:00 Friday, I expect it to chart out a count of 2 for the 5:00 hour and a count of 1 for the 6:00 hour.

This query does work and counts what we need:

<search_string_here>
| eval hour = strftime(_time,"%H")
| chart count by hour

The issue, though, is if there are gaps in the hours, they are not in the chart. So the above example will have a chart with only bars for the 5 and the 6 hour. We want to see all hours (0 - 23) on the chart, and if there was no data for that hour, obviously the count would be 0.

I can't figure out how to "fill" in the missing hours. Any suggestions?

[somesoni2 · Accepted solution]

Try like this (timechart will ensure there exist a row for each hour, even if the count it 0)

<search_string_here> 
| timechart span=1h count
| eval hour = strftime(_time,"%H") 
| chart sum(count) as count by hour

[bbaisley]

This works. Thanks!

[kmaron]

try this:

| stats count 
| eval hour="00,01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23" 
| makemv delim="," hour
| mvexpand hour
| append 
    [search <search_string_here> | eval hour = strftime(_time,"%H") ]
| stats sum(eval(if(isnull(_time),0,1))) as count by hour