Search code examples
kql

How to summarize count() by bin of 1 day and add default value for the missing days in the timeframe in KQL

I have this KQL:

traces
| where timestamp between (['_startTime'] .. ['_endTime'])
| where message == "Operation Success"
| summarize count() by bin(timestamp, 1d)

Now the query is for 7 days, but there are timestamps only in 2 days out of the 7. I wish the other days will appear in the summarize but as zero's

E.g. current result:

timestamp count
2023-04-25T00:00:00Z 3
2023-04-27T00:00:00Z 4

requested result:

timestamp count
2023-04-23T00:00:00Z 0
2023-04-24T00:00:00Z 0
2023-04-25T00:00:00Z 3
2023-04-26T00:00:00Z 0
2023-04-27T00:00:00Z 4
2023-04-28T00:00:00Z 0
2023-04-29T00:00:00Z 0
Solution 
  • traces
| where timestamp between (['_startTime'] .. ['_endTime'])
| where message == "Operation Success"
| make-series count() default = 0 on timestamp from _startTime to _endTime step 1d
| project-reorder timestamp, count_
| render timechart