Kusto for sliding window

user_17892621 0
Viewed 451

I am new to Kusto Query language. Requirement is to alert when the continuous 15 minute value of machine status is 1. I have two columns with column1:(timestamp in every second) and column2:machine status(values 1 and 0).How can I use a sliding window to find if the machine is 1 for continuous 15 minutes. Currently I have used the bin function, but it does not seem to be the proper one. summarize avg_value = avg(status) by customer, machine,bin(timestamp,15m) What could be the better solution for this.

Thanks in advance

kusto-explorer sliding-window kql azure-data-explorer
2 Answers

Here is another option using time series functions:

let dt = 1s;
let n_bins = tolong(15m/dt);
let coeffs = repeat(1, n_bins);
let T = view(M:string) {
    range Timestamp from datetime(2022-01-11) to datetime(2022-01-11 01:00) step dt
    | extend machine = M
    | extend status = iif(rand()<0.002, 0, 1)
};
union T("A"), T("B")
| make-series status=any(status) on Timestamp step dt by machine
| extend rolling_status = series_fir(status, coeffs, false)
| extend alerts = series_equals(rolling_status, n_bins)
| project machine, Timestamp, alerts
| mv-expand Timestamp to typeof(datetime), alerts to typeof(bool)
| where alerts == 1

You can also do it using the scan operator.

thanks

user_41108650

Here is one way to do it, the example uses generated data, hopefully it fits in your scenario:

let view = range x from datetime(2022-01-10 13:00:10) to datetime(2022-01-10 13:10:10) step 1s
| extend status = iif(rand()<0.01, 0, 1)
| extend current_sum = row_cumsum(status)
| extend prior_sum = prev(current_sum, 15)
| extend should_alert = (current_sum-prior_sum != 15 and isnotempty(prior_sum))

If you have multiple machines you need to sort it first by machines and restart the row_cumsum operation:

let T = view(M:string) {
    range Timestamp from datetime(2022-01-10 13:00:10) to datetime(2022-01-10 13:10:10) step 1s
    | extend machine = M
    | extend status = iif(rand()<0.01, 0, 1)
};
union T("A"), T("B")
| sort by machine asc, Timestamp asc 
| extend current_sum = row_cumsum(status, machine != prev(machine))
| extend prior_sum = iif(machine == prev(machine, 15),  prev(current_sum, 15), int(null))
| extend should_alert = (current_sum-prior_sum != 15 and isnotempty(prior_sum))
user_101679140
Related