Fill empty fields with previous values in Kusto query in Azure Data Explorer

user_457123 0
Viewed 2070

Background

We have a dataset with the following format in Azure Data Explorer.

sensorid timestamp value
valve1 24-03-2021 123
valve1 23-03-2021 234
cylinderspeed 23-03-2021 1.2
valvestatus 23-03-2021 open
valvestatus 24-03-2021 closed
cylinderspeed 25-03-2021 2

The different sensors have different reporting intervals, some report every second, some a few times per day.

By using this query

datatable (sourcetimestamp: datetime, sensorid:string, value:dynamic)
    [datetime(2021-03-23), "valve1", 123,
    datetime(2021-03-24), "valve1", 234,
    datetime(2021-03-23), "cylinderspeed", 1.2,
    datetime(2021-03-23), "valvestatus", "open",
    datetime(2021-03-24), "valvestatus", "closed",
    datetime(2021-03-25), "cylinderspeed", 2]
| summarize average=any(value) by bin(sourcetimestamp, 1s), sensorid
| evaluate pivot(sensorid, any(average))

I can generate this table

timestamp valve1 cylinderspeed valvestatus
23-03-2021 123 1,2 open
24-03-2021 234 closed
25-03-2021 2

The problem

How can I continue on the above query so I can fill empty cells with the previous value for from that column?

kql azure-data-explorer
1 Answers

You can use one of the series_fill functions such as series_fill_forward. Note that the easiest way to get the arrays to fill is by using the make-series operator

since timeseries expects numeric values in the series I translated the enum of the valvestatus to double.

datatable (sourcetimestamp: datetime, sensorid:string, value:dynamic)
[datetime(2021-03-23), "valve1", 123, 
datetime(2021-03-24), "valve1", 234, 
datetime(2021-03-23), "valvestatus", "open",
datetime(2021-03-24), "valvestatus", "closed",
datetime(2021-03-23), "cylinderspeed", 1.2,
datetime(2021-03-24), "cylinderspeed", 2]
| extend value = case(value=="open", double(1), value=="closed", double(0), value)
| make-series values = any(value) default=double(null) on sourcetimestamp from(datetime(2021-03-23 00:00:00.0000000)) to(datetime(2021-03-24 00:00:00.0000000)) step 1h by sensorid
| extend values = series_fill_forward(values)
user_101679140
Related