如何用Log Analytics Kusto查询生成5分钟采样的设备可用性可视化

假设IoT Hub已将deviceConnect和deviceDisconnect事件同步至Log Analytics工作区，要以5分钟为采样间隔统计设备当日的可用性（Connected对应100%可用，Disconnected对应0%可用），且默认初始状态为断开，可通过以下Kusto查询实现：

完整Kusto查询 #

// 1. 定义当日时间范围，获取所有涉及的设备ID
let targetDate = startofday(now());
let deviceList = YourIoTHubTableName
| where TimeGenerated between (targetDate .. targetDate + 1d)
| distinct DeviceId;

// 2. 生成当日的5分钟时间间隔序列
let timeIntervals = range Interval from targetDate to targetDate + 1d step 5m;

// 3. 生成设备与时间间隔的笛卡尔积，确保每个设备在每个间隔都有记录
let deviceTimeGrid = deviceList
| join kind=cross timeIntervals on $left.empty = $right.empty;

// 4. 获取每个设备的所有状态变更事件，并标记状态
let statusEvents = YourIoTHubTableName
| where TimeGenerated between (targetDate .. targetDate + 1d)
| where OperationName in ("deviceConnect", "deviceDisconnect")
| extend Status = case(OperationName == "deviceConnect", "Connected", "Disconnected")
| project DeviceId, TimeGenerated, Status;

// 5. 关联状态事件，确定每个时间间隔内设备的最新状态
deviceTimeGrid
| join kind=leftouter statusEvents on DeviceId
| where TimeGenerated <= Interval
| summarize LatestStatus = arg_max(TimeGenerated, Status).Status by DeviceId, Interval
// 6. 填充初始状态（无事件时默认Disconnected）
| extend Status = coalesce(LatestStatus, "Disconnected")
// 7. 映射为可用性百分比
| extend Availability = case(Status == "Connected", 100, 0)
| project Interval, DeviceId, Status, Availability
| sort by Interval, DeviceId

查询说明 #

• 替换表名：将查询中的YourIoTHubTableName替换为你Log Analytics中IoT Hub事件对应的表名（通常是AzureDiagnostics，需根据实际配置调整）。
• 时间范围：targetDate定义为当日零点至次日零点，确保只统计当日数据。
• 笛卡尔积生成网格：通过cross join确保每个设备在每个5分钟间隔都有一条记录，避免因设备无事件导致数据缺失。
• 状态关联逻辑：用arg_max获取每个时间间隔前的最后一次状态变更，以此确定当前间隔的设备状态；无任何事件时默认填充Disconnected。
• 可用性映射：将Connected状态转为100%，Disconnected转为0%，方便后续生成图表。

生成图表 #

执行查询后，在Log Analytics的可视化面板中：

• 选择折线图或面积图作为图表类型
• 将Interval设为X轴，Availability设为Y轴，DeviceId设为图例字段
• 即可展示各设备当日每5分钟的可用性变化趋势

内容的提问来源于stack exchange，提问作者Carl in 't Veld