Make the value as 0, if rows not available in Kusto

user_7851305 0
Viewed 971

My query has count function which returns the count of rows summarized by day. Now, when there are no rows from that table, I'm not getting any result, instead I need, rows with all days and count as zero. I tried with coalesc but didnt work. Any help is much appreciated!

Thanks!

Here is my query:

exceptions
 | where name == 'my_scheduler' and timestamp > ago(30d)
 | extend day = split(tostring(timestamp + 19800s), 'T')[0]
 | summarize schedulerFailed = coalesce(count(),tolong("0")) by tostring(day)
kusto-explorer kql azure-data-explorer
1 Answers

Instead of summarize you need to use make-series which will fill the gaps with a default value for you.

exceptions
| where name == 'my_scheduler' and timestamp > ago(30d)
| extend day = split(tostring(timestamp + 19800s), 'T')[0]
| make-series count() on tolong(x) step 1

You might want to add from and to to make-series in order for it to also fill gaps at the beginning and the end of the 30d period.

user_21450970
Related