Add a value "0" and make it visible on a graph

[Rajaion]

Hello community,

I'm encountering a problem that's probably simple to correct, but no matter how hard I try, I can't do it. I have a query that returns several results that I count according to the time range.

This allows me to provide a graph showing the hourly load. However, I noticed that when there was no result over a time range (for example between 3:00 a.m. and 4:00 a.m.), the graph does not appear in full, I am missing the time range in question :

Here is my current query:

index="oncall_hp" currentPhase=UNACKED routingKey=*event* entityDisplayName!=*Local-Self-Monitoring*
| dedup incidentNumber
| eval Heure = strftime(_time, "%H")
| stats count by Heure
| rename count AS Events
| sort Heure

I tried to force the appearance of a "0" value if there was nothing but that didn't change:

index="oncall_hp" currentPhase=UNACKED routingKey=*event* entityDisplayName!=*Local-Self-Monitoring*
| dedup incidentNumber
| eval Heure = strftime(_time, "%H")
| stats count by Heure
| rename count AS Events
| eval Events=if(isnull(Events) OR len(Events)==0, "0", Events) 
| sort Heure

I looked on the forum to see if other people had had this problem but I couldn't find it (or I didn't look well).

Do you have an idea to simply add a "0" value if a time slot is empty, and that adds it to the graph?

Best regards,

Rajaion

[Rajaion]

Hello @ITWhisperer,

Thank you for your help. Indeed, it displays empty columns. However, I notice the time is added to the graph but for each day present in the graph:

I can delete the display for each day but it only works well on one day:

Because over several days, we group by time slot but it does not add the values ​​of the same time slot. I tried adding everything up but it doesn't respond at all after that.

Do you know how to just add everything up for each time slot?

Best regards,

Rajaion

[ITWhisperer · Accepted solution]

index="oncall_hp" currentPhase=UNACKED routingKey=*event* entityDisplayName!=*Local-Self-Monitoring*
| dedup incidentNumber
| timechart count span=1h
| fillnull value=0 count
| eval Heure = strftime(_time, "%H")
| stats sum(count) as Events by Heure

[Rajaion]

Oh yes, I had forgotten the use of sum, in fact, it works perfectly.

Thank you very much for your help.

Best regards,

Rajaion

[ITWhisperer]

The issue is not the chart, it is with your stats table. Because no data exists for those hours, there are no rows (and therefore no corresponding column). Using timechart instead of stats will fill in the missing time slots, you then just need to convert the null counts to zeroes. Try something like this

index="oncall_hp" currentPhase=UNACKED routingKey=*event* entityDisplayName!=*Local-Self-Monitoring*
| dedup incidentNumber
| timechart count span=1h
| fillnull value=0 count
| eval Heure = strftime(_time, "%H")
| rename count AS Events
| sort 0 Heure