Kusto查询需求：合并多时间范围的消息计数查询（含Azure/未授权消息）

需求说明 #

需求一 #

获取过去7天每日、过去30天每日的Unauthorized消息计数，输出单一格式的结果。

需求二 #

获取以下三类数据的统一输出结果：

• 过去24小时内message列包含"azure"的消息总数；
• 过去7天内每日message列包含"azure"的消息计数；
• 过去30天内每日message列包含"azure"的消息计数。

现有独立查询语句 #

针对需求二，目前有三个独立的Kusto查询：

过去24小时查询 #

| where message has "azure" 
| where timestamp  > ago(24h)   
| summarize count() by message = "azure"

过去7天每日计数查询 #

| where message has "azure"
| where timestamp >= startofday(ago(7d))
| where timestamp  < startofday(now())
| summarize count() by startofday(timestamp) , message ="azure" 

过去30天每日计数查询 #

| where message has "azure"
| where timestamp >= startofday(ago(30d))
| where timestamp  < startofday(now())
| summarize count() by startofday(timestamp) ,  message = "azure" 

合并查询请求 #

是否可以将上述三个查询合并为一个，输出如下格式的单一结果？要求结果覆盖过去30天的所有日期。

示例输入数据 #

预期输出格式 #

解决方案：合并后的Kusto查询 #

可以通过生成日期序列结合条件聚合实现合并查询，确保覆盖过去30天的所有日期，同时输出三个维度的计数。以下是完整查询语句：

// 生成过去30天的每日日期序列，保证无数据日期也能输出
let date_range = range day from startofday(ago(30d)) to startofday(now()) step 1d;
// 筛选包含azure的消息并按日期分组预处理
let azure_data = your_table_name
| where message has "azure"
| extend day = startofday(timestamp);
// 关联日期序列，计算各维度计数
date_range
| join kind=leftouter (azure_data) on day
| summarize 
    count_24hrs = countif(timestamp > ago(24h)),
    count_7days = countif(timestamp >= startofday(ago(7d))),
    count_30days = count()
    by day, message = "azure"
// 处理空值，匹配预期输出格式
| project 
    Timestamp = iif(isnotempty(day), day, datetime(null)),
    message,
    count_24hrs = iif(count_24hrs == 0, int(null), count_24hrs),
    count_7days = iif(count_7days == 0, int(null), count_7days),
    count_30days = iif(count_30days == 0, int(null), count_30days)
// 按日期倒序排列
| sort by Timestamp desc

关键逻辑说明 #

1. 日期序列生成：用range函数生成过去30天的每日起始时间，确保结果覆盖所有日期，避免无数据日期丢失。
2. 左连接关联：将日期序列与筛选后的azure数据左连接，保证每个日期都出现在结果集中。
3. 条件聚合：通过countif分别统计符合24小时、7天范围的消息数，count()直接统计30天内的每日总数。
4. 空值格式化：用iif将0值转换为null，匹配预期输出中的空单元格样式。

需求一的扩展实现 #

针对需求一（Unauthorized消息的7天/30天每日计数），只需修改筛选条件与聚合逻辑：

let date_range = range day from startofday(ago(30d)) to startofday(now()) step 1d;
let unauthorized_data = your_table_name
| where message has "Unauthorized"
| extend day = startofday(timestamp);
date_range
| join kind=leftouter (unauthorized_data) on day
| summarize 
    count_7days = countif(timestamp >= startofday(ago(7d))),
    count_30days = count()
    by day, message = "Unauthorized"
| project 
    Timestamp = day,
    message,
    count_7days = iif(count_7days == 0, int(null), count_7days),
    count_30days = iif(count_30days == 0, int(null), count_30days)
| sort by Timestamp desc

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