动态追踪Top5高消耗日志日用量的KQL优化需求

此前日志资源消耗过高时的追踪与告警存在缺陷，导致成本超支。目前已实现动态追踪消耗最高的5类日志日用量的Azure Monitor KQL查询，但希望得到更优实现方案，且代码需保持动态性，无需手动硬编码日志类型。现有查询代码如下：

let TWmonthEnd = endofmonth(datetime(now),-2); 
let twoweek =ago(14d);

let histbase=Usage
| where TimeGenerated > TWmonthEnd 
| project TimeGenerated, DataType, Solution, Quantity, IsBillable
| where IsBillable =='True'
| summarize TWmonthEnd = sumif(Quantity, TimeGenerated >TWmonthEnd)
            by DataType;

let hisresults=histbase
|top 5 by TWmonthEnd
|project DataType;

let currentresult = Usage
| where TimeGenerated > twoweek 
| project TimeGenerated, DataType, Solution, Quantity, IsBillable
| where IsBillable =='True'
| summarize weekly = sum(Quantity) by bin(TimeGenerated,1d), DataType;

currentresult
| join kind = inner hisresults on DataType
|where TimeGenerated > twoweek 
| project TimeGenerated, DataType, weekly
| sort by TimeGenerated

优化后的KQL查询 #

// 定义时间范围：过去2个月的起始点（用于筛选历史高消耗日志类型）、过去14天（当前统计周期）
let historicalStart = endofmonth(now(), -2);
let currentPeriod = ago(14d);

// 筛选过去2个月内消耗最高的Top5可计费日志类型
let top5DataTypes = Usage
| where TimeGenerated > historicalStart and IsBillable == 'True'
| summarize totalConsumption = sum(Quantity) by DataType
| top 5 by totalConsumption
| project DataType;

// 统计过去14天内这些Top5类型的每日用量
Usage
| where TimeGenerated > currentPeriod and IsBillable == 'True'
| where DataType in (top5DataTypes)
| summarize dailyUsage = sum(Quantity) by bin(TimeGenerated, 1d), DataType
| sort by TimeGenerated asc, DataType

优化说明 #

• 减少重复扫描：原查询两次全量扫描Usage表并通过join关联，优化后用in操作替代join，避免冗余的数据读取与关联计算
• 简化冗余逻辑：移除重复的TimeGenerated过滤（原sumif的条件与外层where完全重复，直接用sum即可），删除不必要的project字段投影，减少数据处理开销
• 提升可读性：将变量命名改为语义化名称（如*TWmonthEnd*改为*historicalStart*），添加注释说明各段逻辑，便于维护
• 优化结果规整性：补充DataType排序规则，让输出结果更整齐

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