A common ask I’ve heard from several users, is the ability to fill gaps in your data in Kusto/App Analytics/DataExplorer (lots of names these days!):
@assaf___ any best practice how to “fill time gaps” in a kusto query after a summarize on timestamp? (a timechart will draw the line between the known points and I want a missing point to be drawn as 0)
— Tom Kuijsten (@tomkuijsten) December 17, 2018
If your data has gaps in time in it, the default behavior for App Analytics is to “connect the dots”, and not really reflect that there was no data in these times. In lots of cases we’d like to fill these missing dates with zeros.
The way to go to handle this, is to use the “make-series” operator. This operator exists to enable advanced time-series analysis on your data, but we’ll just use it for the simple use-case of adding missing dates with a “0” value.
Some added sophistication is converting the series back to a *regular* summarize using “mvexpand”, so we can continue to transform the data as usual.
Here’s the query (Thanks Tom for helping refine this query!) :
let start=floor(ago(3d), 1d); let end=floor(now(), 1d); let interval=5m; requests | where timestamp > start | make-series counter=count() default=0 on timestamp in range(start, end, interval) | mvexpand timestamp, counter | project todatetime(timestamp), toint(counter) | render timechart
2 thoughts on “Back-fill Missing Dates With Zeros in a Time Chart”
The team misses you, Assaf 😉
LikeLiked by 1 person
🙂 Me too.. But I hear the team is doing great!