KQL Render Columnchart忽略ycolumns与ysplit属性问题求助

原查询核心问题分析 #

1. 冗余Join与数据结构混乱：两次fullouter join导致数据出现大量空值，后续make_set+mv-expand的嵌套结构让渲染引擎无法正确识别双面板所需的数值列关联逻辑
2. 未明确指定X轴分组：render未指定xcolumn，默认以Reason作为X轴，破坏了多数值列的展示逻辑
3. 数值列识别失效：空值和嵌套结构干扰了引擎对ycolumns参数的解析，最终仅渲染了最后一列（Percentage）

优化后的KQL查询（单次查询实现双面板） #

// 定义时间偏移量（此处为上月）
let offset = -1;
// 预提取Top10高频安全事件（含总数量，避免重复扫描）
let top10Incidents = 
    SecurityIncident
    | summarize arg_max(TimeGenerated, *) by IncidentNumber // 保留事件最新状态
    | where Status =~ "Closed" and Severity != "Informational"
    | where TimeGenerated between (startofmonth(now(), offset) .. endofmonth(now(), offset))
    | summarize TotalCount = count() by Title
    | sort by TotalCount desc
    | take 10;

// 关联关闭原因数据，计算数量与占比
SecurityIncident
| summarize arg_max(TimeGenerated, *) by IncidentNumber
| where Status =~ "Closed" and Severity != "Informational"
| where TimeGenerated between (startofmonth(now(), offset) .. endofmonth(now(), offset))
| where Title in (top10Incidents.Title) // 仅保留Top10事件
| summarize ClosureCount = count() by Title, ClassificationReason
| join kind=inner top10Incidents on Title // 关联总数量计算占比
| extend 
    Percentage = round(todouble(ClosureCount)/TotalCount*100, 2),
    Reason = iif(isnotempty(ClassificationReason), ClassificationReason, "Defender AutoClosed")
| project Title, Reason, Amount = ClosureCount, Percentage
// 渲染双面板柱状图
| render columnchart with (
    xcolumn=Title,
    ycolumns=Amount, Percentage,
    ysplit=panels,
    legend=visible,
    title="Top10安全事件关闭数量与占比分布"
)

关键优化点说明 #

• 预计算Top10事件：通过let语句一次性提取目标事件，避免重复扫描全表，确保后续数据仅聚焦于Top10范围
• 简化数据结构：移除冗余的make_set/mv-expand，生成扁平的Title-Reason-Amount-Percentage结构，让渲染引擎清晰识别数值列
• 明确渲染参数：指定xcolumn=Title统一X轴分组，ycolumns明确标注两个数值列，ysplit=panels即可正常生成上下双面板
• 过滤无效数据：用inner join剔除空值行，确保所有行都有合法的数量和占比数值

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