Iot Hub sends connected and disconnected events to a log analytics workspace.
How can we create a diagram using a log analytics kusto query that shows the availability of a device during the day with a 5 minute sampling?
Source data looks like this:
| OperationName | DeviceId | TimeGenerated |
|---|---|---|
| deviceConnect | device1 | 2022-09-22T09:43:20 |
| deviceDisconnect | device1 | 2022-09-22T09:53:20 |
| deviceDisconnect | device2 | 2022-09-22T09:55:20 |
| deviceConnect | device3 | 2022-09-22T10:00:20 |
| deviceConnect | device4 | 2022-09-22T10:43:20 |
...
Resulting data set should be like:
| Interval | DeviceId | Status |
|---|---|---|
| ... assuming all devices disconnected | ||
| 2022-09-22T09:40:00 | device1 | Disconnected |
| 2022-09-22T09:40:00 | device2 | Disconnected |
| 2022-09-22T09:40:00 | device3 | Disconnected |
| 2022-09-22T09:40:00 | device4 | Disconnected |
| 2022-09-22T09:45:00 | device1 | Connected |
| 2022-09-22T09:45:00 | device2 | Disconnected |
| 2022-09-22T09:45:00 | device3 | Disconnected |
| 2022-09-22T09:45:00 | device4 | Disconnected |
| 2022-09-22T09:50:00 | device1 | Connected |
| 2022-09-22T09:50:00 | device2 | Connected |
| 2022-09-22T09:50:00 | device3 | Disconnected |
| 2022-09-22T09:50:00 | device4 | Disconnected |
| 2022-09-22T09:55:00 | device1 | Disconnected |
| 2022-09-22T09:55:00 | device2 | Connected |
| 2022-09-22T09:55:00 | device3 | Disconnected |
| 2022-09-22T09:55:00 | device4 | Disconnected |
| 2022-09-22T09:00:00 | device1 | Disconnected |
| 2022-09-22T09:00:00 | device2 | Disconnected |
| 2022-09-22T09:00:00 | device3 | Connected |
| 2022-09-22T09:00:00 | device4 | Connected |
| ... |
Status column can then be projected to Availability 100% or 0%.