Search code examples
azure-data-explorerkqlkusto-explorer

Kusto Timeseries fill up missing values for timechart

I want to render a timechart which counts the SoftwareVersion based on 1 day steps. I have to fill up forward missing values per day and serial.

The data to start with is:

let swVersions = datatable(Date: datetime, SoftwareVersion: string, Serial: string) [
datetime(2022-01-24T13:18:20.8450657Z), '1.29.0', '310160039',
datetime(2022-01-26T06:01:41.8742421Z), '1.30.0', '310160039',
datetime(2022-01-26T12:12:23.2342343Z), '1.31.0', '310160039',
datetime(2022-01-28T12:10:14.3620707Z), '1.17.0', '310160039',
datetime(2022-01-24T05:48:58.9000481Z), '1.29.0', '310160040',
datetime(2022-01-24T10:22:23.4457354Z), '1.30.0', '310160040',
datetime(2022-01-24T15:52:16.2342152Z), '1.29.0', '310160040',
datetime(2022-01-25T05:48:58.9012738Z), '1.30.0', '310160040'];

So i need the data like this for rendering the timechart (expected):

let swVersions = datatable(Date: datetime, SoftwareVersion: string, Serial: string) [
datetime(2022-01-24T00:00:00.0000000Z), '1.29.0', '310160039',
datetime(2022-01-25T00:00:00.0000000Z), '1.29.0', '310160039',
datetime(2022-01-26T00:00:00.0000000Z), '1.31.0', '310160039',
datetime(2022-01-27T00:00:00.0000000Z), '1.31.0', '310160039',
datetime(2022-01-28T00:00:00.0000000Z), '1.17.0', '310160039',
datetime(2022-01-24T00:00:00.0000000Z), '1.29.0', '310160040',
datetime(2022-01-25T00:00:00.0000000Z), '1.30.0', '310160040',
datetime(2022-01-26T00:00:00.0000000Z), '1.30.0', '310160040',
datetime(2022-01-27T00:00:00.0000000Z), '1.30.0', '310160040',
datetime(2022-01-28T00:00:00.0000000Z), '1.30.0', '310160040'];

And summarize and rendering timechart like this:

let swVersions = datatable(Date: datetime, SoftwareVersion: string, Serial: string) [
datetime(2022-01-24T00:00:00.0000000Z), '1.29.0', '310160039',
datetime(2022-01-25T00:00:00.0000000Z), '1.29.0', '310160039',
datetime(2022-01-26T00:00:00.0000000Z), '1.31.0', '310160039',
datetime(2022-01-27T00:00:00.0000000Z), '1.31.0', '310160039',
datetime(2022-01-28T00:00:00.0000000Z), '1.17.0', '310160039',
datetime(2022-01-24T00:00:00.0000000Z), '1.29.0', '310160040',
datetime(2022-01-25T00:00:00.0000000Z), '1.30.0', '310160040',
datetime(2022-01-26T00:00:00.0000000Z), '1.30.0', '310160040',
datetime(2022-01-27T00:00:00.0000000Z), '1.30.0', '310160040',
datetime(2022-01-28T00:00:00.0000000Z), '1.30.0', '310160040'];
swVersions
| summarize count() by Date, SoftwareVersion
| render timechart

How can I do this? I am very appreciated for your help.

Solution

  • render timechart with (accumulate=true)

    let swVersions = datatable(Date: datetime, SoftwareVersion: string, Serial: string) 
[
    datetime(2022-01-24T13:18:20.8450657Z), '1.29.0', '310160039',
    datetime(2022-01-26T06:01:41.8742421Z), '1.30.0', '310160039',
    datetime(2022-01-26T12:12:23.2342343Z), '1.31.0', '310160039',
    datetime(2022-01-28T12:10:14.3620707Z), '1.17.0', '310160039',
    datetime(2022-01-24T05:48:58.9000481Z), '1.29.0', '310160040',
    datetime(2022-01-24T10:22:23.4457354Z), '1.30.0', '310160040',
    datetime(2022-01-24T15:52:16.2342152Z), '1.29.0', '310160040',
    datetime(2022-01-25T05:48:58.9012738Z), '1.30.0', '310160040'
];
let swVersions_daily_version = swVersions | summarize arg_max(Date, *) by Serial, Date = startofday(Date);
let swVersions_adds = swVersions_daily_version | extend delta = 1;
let swVersions_drops =
swVersions_daily_version
|   extend delta = -1
|   partition hint.strategy=native by Serial
    (
            order by Date asc
        |   extend Date = next(Date)
        |   where isnotnull(Date)
    )
;
let Date_start  = toscalar(swVersions | summarize startofday(min(Date)));
let Date_end    = toscalar(swVersions | summarize max(Date));
union swVersions_adds, swVersions_drops
| make-series sum(delta) on Date from Date_start to Date_end step 1d by SoftwareVersion
| render timechart with (accumulate=true)

    Timechart

    Fiddle