'calculate downtime based on status values and should get only the first downtime start and downtime end until the status changes
ID Status Time
12987679 0 2022-04-24 10:40:00.000
12987679 1 2022-04-24 10:50:00.000
12987679 1 2022-04-24 11:05:00.000
12987679 0 2022-04-24 11:49:00.000
12987679 0 2022-04-24 12:45:00.000
12987679 0 2022-04-24 12:48:00.000
12987679 1 2022-04-24 13:00:00.000
12987679 0 2022-04-27 10:40:00.000
12987679 1 2022-04-27 11:00:00.000
12987679 1 2022-04-27 11:05:00.000
12987679 0 2022-04-27 11:49:00.000
12987679 0 2022-04-27 12:45:00.000
12987679 0 2022-04-27 12:48:00.000
12987679 1 2022-04-27 13:00:00.000
Consider the above data. In this the device 12987679 is recording its device status every now and then. Here status 0 is down and 1 is Up. I need to capture the downtime start when it started as 0 and end when it started to receive a 1. So I need only one start and end entry for a sequence of downtime. For example "12987679 0 2022-04-24 11:49:00.000" started as down and ended at "12987679 1 2022-04-24 13:00:00.000" when it came as up. So, I need only downtime starttime as 12987679 1 2022-04-24 13:00:00.000 and end time as 12987679 1 2022-04-24 13:00:00.000 and and remaining entries of 0's needs to be skipped. The idea of skipping this is because its a streaming data and we will have lot of entries coming every one min. So, we need just the downtime start and downtime end captured. Could someone please share some insights.
I referred below bog but I get downtime for each time it is 0. calculate downtime based on status values
My result should be:
ID startime endtime Downtime
12987679 2022-04-27 10:40:00.000 2022-04-27 11:00:00.000 20
12987679 2022-04-27 11:49:00.000 2022-04-27 13:00:00.000 71
Below is the kusto query that I'm trying to achieve https://techcommunity.microsoft.com/t5/azure-data-explorer-blog/calculating-downtime-using-window-functions-and-time-series/ba-p/1345430
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
