Azure Monitor日志告警咨询：工作时段连续2小时无订单触发告警

不需要生成非工作时段的虚拟数据，最优做法是先筛选出周一至周五9:00-17:00的工作时段数据，再通过make-series生成包含无订单时段（值为0）的连续小时序列，最后检测连续2小时以上无订单的情况。

步骤1：生成包含无订单时段的连续统计数据 #

使用make-series可以自动填充没有订单的时段为0，解决你之前遇到的双列数据集问题，语法如下：

let time_range_start = ago(7d); // 根据告警需求调整时间范围，比如最近7天
let time_range_end = now();
BoomiOrderTracking_CL
| where MessageType_s == 'Order Dispatch Invoice'
// 过滤周一至周五（Kusto中dayofweek返回0=周日，1=周一，…，5=周五）
| where dayofweek(TimeGenerated) between (1 .. 5)
// 过滤9:00-17:00（对应小时9到16，因为17:00的bin属于下一个时段）
| where hourofday(TimeGenerated) between (9 .. 16)
// 生成连续小时序列，无数据时段默认填充0
| make-series invoices=count() default=0 on TimeGenerated from time_range_start to time_range_end step 1h
// 将序列展开为单行数据格式
| mv-expand TimeGenerated, invoices to typeof(long)
| order by TimeGenerated desc

步骤2：检测连续2小时无订单的时段 #

基于上面的结果，添加逻辑识别连续的无订单时段，用于触发告警：

let time_range_start = ago(7d);
let time_range_end = now();
BoomiOrderTracking_CL
| where MessageType_s == 'Order Dispatch Invoice'
| where dayofweek(TimeGenerated) between (1 .. 5)
| where hourofday(TimeGenerated) between (9 .. 16)
| make-series invoices=count() default=0 on TimeGenerated from time_range_start to time_range_end step 1h
| mv-expand TimeGenerated, invoices to typeof(long)
| order by TimeGenerated asc
// 标记是否为无订单时段
| extend is_no_invoice = (invoices == 0)
// 用row_window_session划分连续的无订单会话
| extend session_id = row_window_session(TimeGenerated, 1h, 1h, not(is_no_invoice))
// 统计每个会话的连续时长
| summarize 
    start_time = min(TimeGenerated),
    end_time = max(TimeGenerated),
    consecutive_hours = count()
    by session_id, is_no_invoice
// 筛选出连续2小时以上的无订单时段
| where is_no_invoice and consecutive_hours >= 2
| project start_time, end_time, consecutive_hours

关于生成虚拟数据的方案（非最优） #

如果确实需要生成虚拟数据（比如特殊报表场景），可以通过range生成工作时段的时间序列，再与实际数据左连接填充0：

// 生成工作时段的虚拟时间点序列
let work_hours_sequence = range TimeGenerated from ago(7d) to now() step 1h
| where dayofweek(TimeGenerated) between (1 .. 5)
| where hourofday(TimeGenerated) between (9 .. 16);

// 实际订单统计
let actual_invoices = BoomiOrderTracking_CL
| where MessageType_s == 'Order Dispatch Invoice'
| summarize invoices = count() by bin(TimeGenerated, 1h);

// 左连接填充无数据时段为0
work_hours_sequence
| left join actual_invoices on TimeGenerated
| extend invoices = coalesce(invoices, 0)
| order by TimeGenerated desc

但这个方案相比make-series更繁琐，需要手动维护时间序列，因此不推荐作为告警的最优方案。

内容的提问来源于stack exchange，提问作者perpetual confusion