Projecting each day of the week in KQL

user_410622 0
Viewed 172

Within an Azure dashboard I'm wanting to create a tile which shows exceptions over the last 7 days, however the KQL below will obviously only return a data point where there has been an exception on a particular day.

How do I get it to return zero on a day when there were no exceptions?

exceptions
| where (* has 'insights')
    and timestamp >= ago(7d)
| summarize Count=count() 
     by Date2=bin(timestamp, 1d)
| project Date=(format_datetime(Date2 , 'dd-MM-yyyy')), Count
| sort by Date
kql azure-application-insights azure
1 Answers

Use make_series instead of summarize.

Here's an example:

range x from 0 to 99 step 1
| where x !between(60 .. 79)
| make-series count() on x step 10
| mv-expand count_, x
| project x, count_

Output:

| x  | count_ |
|----|--------|
| 0  | 10     |
| 10 | 10     |
| 20 | 10     |
| 30 | 10     |
| 40 | 10     |
| 50 | 10     |
| 60 | 0      |
| 70 | 0      | 
| 80 | 10     |
| 90 | 10     |
user_21450970
Related