'Check if value is between threshold limits for a defined time window - KQL query

I want to write a query in Kusto which checks if the speed of a car is ALWAYS between a certain speed limit (e.g. b/w 100 and 150 km/h) for a window of 5 mins. Any leads shall be highly appreciated.

The following dataset can be used as an example:

Timestamp Speed Temperature
2022-01-01 00:01:00.0000000 142.5 25.5
2022-01-01 00:02:00.0000000 133.2 25.4
2022-01-01 00:03:00.0000000 124.8 25.6
2022-01-01 00:04:00.0000000 115.3 25.5
2022-01-01 00:05:00.0000000 98.4 25.4
2022-01-01 00:06:00.0000000 106.3 26.3
2022-01-01 00:07:00.0000000 119.6 26.5
2022-01-01 00:08:00.0000000 134.7 25.4
2022-01-01 00:09:00.0000000 128.2 26.6
2022-01-01 00:10:00.0000000 137.5 25.5
2022-01-01 00:11:00.0000000 139.9 27.4
2022-01-01 00:12:00.0000000 140.1 26.3
2022-01-01 00:13:00.0000000 145.4 25.7
2022-01-01 00:14:00.0000000 158.7 24.4
2022-01-01 00:15:00.0000000 155.8 25.6

The desired result would be as follows:

Timestamp Speed Temperature
2022-01-01 00:06:00.0000000 106.3 26.3
2022-01-01 00:07:00.0000000 119.6 26.5
2022-01-01 00:08:00.0000000 134.7 25.4
2022-01-01 00:09:00.0000000 128.2 26.6
2022-01-01 00:10:00.0000000 137.5 25.5

The first and last 5 mins of the data are filtered out because in those 5 min windows the speed was found to be out of the 100 - 150 km/h range.

azure-data-explorerkqlsliding-windowkusto-explorer


Solution 1:[1]

let t = datatable (Timestamp:datetime ,Speed:real ,Temperature:real)
[
     '2022-01-01 00:01:00.0000000' ,142.5 ,25.5
    ,'2022-01-01 00:02:00.0000000' ,133.2 ,25.4
    ,'2022-01-01 00:03:00.0000000' ,124.8 ,25.6
    ,'2022-01-01 00:04:00.0000000' ,115.3 ,25.5
    ,'2022-01-01 00:05:00.0000000' ,98.4  ,25.4
    ,'2022-01-01 00:06:00.0000000' ,106.3 ,26.3
    ,'2022-01-01 00:07:00.0000000' ,119.6 ,26.5
    ,'2022-01-01 00:08:00.0000000' ,134.7 ,25.4
    ,'2022-01-01 00:09:00.0000000' ,128.2 ,26.6
    ,'2022-01-01 00:10:00.0000000' ,137.5 ,25.5
    ,'2022-01-01 00:11:00.0000000' ,139.9 ,27.4
    ,'2022-01-01 00:12:00.0000000' ,140.1 ,26.3
    ,'2022-01-01 00:13:00.0000000' ,145.4 ,25.7
    ,'2022-01-01 00:14:00.0000000' ,158.7 ,24.4
    ,'2022-01-01 00:15:00.0000000' ,155.8 ,25.6
];
let min_Timestamp = toscalar(t | summarize min(Timestamp));
t
| summarize make_list(Timestamp), make_list(Speed), make_list(Temperature) by bin_at(Timestamp, 5m, min_Timestamp)
| extend list_Speed_stat = series_stats_dynamic(list_Speed)  
| where list_Speed_stat["min"] >= 100 and list_Speed_stat["max"] <= 150
| mv-expand Timestamp = list_Timestamp, Speed = list_Speed, Temperature  = list_Temperature  
| project todatetime(Timestamp), toreal(Speed), toreal(Temperature)
Timestamp Speed Temperature
2022-01-01T00:06:00Z 106.3 26.3
2022-01-01T00:07:00Z 119.6 26.5
2022-01-01T00:08:00Z 134.7 25.4
2022-01-01T00:09:00Z 128.2 26.6
2022-01-01T00:10:00Z 137.5 25.5

Fiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David דודו Markovitz

Related Questions