I am new in KQL and right now I am looking for a solution to calculate the time that the device spent with different statuses.

For example, I have a table from one device with statuses 1-device active, 0-device inactive.

datatable (Date: datetime, Status: string) [
    "11/22/2021, 7:22:12.825 PM", "0",
    "11/22/2021, 7:25:13.413 PM", "1",
    "11/22/2021, 7:26:13.282 PM", "1",
    "11/22/2021, 7:31:13.823 PM", "1",
    "11/22/2021, 7:34:13.606 PM", "0",
    "11/22/2021, 7:52:14.206 PM", "1",
    "11/22/2021, 7:55:13.764 PM", "0",
    "11/22/2021, 7:57:14.029 PM", "0",
    "11/26/2021, 11:22:47.466 AM", "1",
    "11/26/2021, 11:25:19.455 PM", "0",
    "11/26/2021, 11:26:19.070 PM", "1",
    "11/27/2021, 4:21:32.515 AM", "1"
]

I would like to calculate how much time the device spent in active and how much is in inactive status.

I have tried different ways using iff and others but didn’t get proper results as the data has the flow that the device can send several active statuses one after another or inactive, so I can’t find the proper way to deal with that duplications or remove them.

Thanks for any solution

CodePudding user response：

Here you go:

datatable (Date: datetime, Status: string) [
    "11/22/2021, 7:22:12.825 PM", "0",
    "11/22/2021, 7:25:13.413 PM", "1",
    "11/22/2021, 7:26:13.282 PM", "1",
    "11/22/2021, 7:31:13.823 PM", "1",
    "11/22/2021, 7:34:13.606 PM", "0",
    "11/22/2021, 7:52:14.206 PM", "1",
    "11/22/2021, 7:55:13.764 PM", "0",
    "11/22/2021, 7:57:14.029 PM", "0",
    "11/26/2021, 11:22:47.466 AM", "1",
    "11/26/2021, 11:25:19.455 PM", "0",
    "11/26/2021, 11:26:19.070 PM", "1",
    "11/27/2021, 4:21:32.515 AM", "1"
]
| order by Date asc
| extend Period = next(Date) - Date
| summarize sum(Period) by Status

Result: