KQL查询新增Result列后出现第6行结果的问题排查

我在做数据可视化时，为了让图例更清晰，新增了Result列，把错误码和部分描述拼接在一起。但加了这个列后，输出出现了6种Result组合，而不是预期的5种。我已经用top 5 by AvgCount筛选过，原以为project语句不会影响结果数量，实际却出了问题，找不到原因。

以下查询返回6种Result类型：

let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber); 
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0); 
let dateBegin = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateBegin);
let top5ResultTypes = SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0
| summarize CountPerDay=count() by ResultType, bin(TimeGenerated, 24h)
| summarize AvgCount=avg(CountPerDay) by ResultType
| top 5 by AvgCount
| project ResultType;
SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0 and ResultType in (top5ResultTypes)
| summarize Count=count() by ResultType, ResultDescription, bin(TimeGenerated, 24h)
| project Count, Result=strcat(ResultType, " : ", substring(ResultDescription, 0, 30)), TimeGenerated
| render timechart
    with (
    title="Top 5 Login Failure Types Per Day",
    xtitle="Date",
    ytitle="Count",
    ysplit=panels 
    )

如果保留ResultType列不变，不新增Result列，查询会返回预期的5种结果：

let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber); 
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0); 
let dateBegin = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateBegin);
let top5ResultTypes = SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0
| summarize CountPerDay=count() by ResultType, bin(TimeGenerated, 24h)
| summarize AvgCount=avg(CountPerDay) by ResultType
| top 5 by AvgCount
| project ResultType;
SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType in (top5ResultTypes)
| summarize Count=count() by ResultType, bin(TimeGenerated, 24h)
| render timechart
    with (
    title="Top 5 Login Failure Types Per Day",
    xtitle="Date",
    ytitle="Count",
    ysplit=panels 
    )

原因分析 #

问题核心在于分组条件的变化：有问题的查询中，summarize时把ResultDescription也加入了分组维度。同一个ResultType可能对应多个不同的ResultDescription，哪怕只截取前30个字符，仍可能产生不同的截断结果，最终拼接出的Result列会出现额外的唯一值，导致结果从5种变成6种。

举个例子：某个错误码ResultType=50126下，存在两个不同的ResultDescription，截断前30字符后内容仍有差异，就会生成两个不同的Result值，反映在时间图里就是6个系列。

解决方案 #

要解决这个问题，需要确保每个ResultType对应唯一的描述文本，再进行拼接。可以在获取Top5结果时顺便关联描述，或者后续单独聚合每个错误码的统一描述。

修改后的查询示例1（在Top5阶段关联描述） #

let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber); 
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0); 
let dateBegin = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateBegin);
// 获取Top5 ResultType时，同时取每个类型对应的一个典型描述（比如任意一条记录的描述）
let top5ResultTypes = SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0
| summarize CountPerDay=count(), Desc=any(ResultDescription) by ResultType, bin(TimeGenerated, 24h)
| summarize AvgCount=avg(CountPerDay), ResultDescription=any(Desc) by ResultType
| top 5 by AvgCount;
SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0 and ResultType in (top5ResultTypes.ResultType)
| summarize Count=count() by ResultType, bin(TimeGenerated, 24h)
// 关联之前获取的统一描述，确保每个ResultType对应唯一的Result文本
| join kind=inner (top5ResultTypes) on ResultType
| project Count, Result=strcat(ResultType, " : ", substring(ResultDescription, 0, 30)), TimeGenerated
| render timechart
    with (
    title="Top 5 Login Failure Types Per Day",
    xtitle="Date",
    ytitle="Count",
    ysplit=panels 
    )

修改后的查询示例2（单独聚合描述） #

let lastmonth = iff(lastmonthNumber == 0, 12, lastmonthNumber); 
let year = getyear(datetime(now)) - iff(lastmonth == 12, 1, 0); 
let dateBegin = make_datetime(year, lastmonth, 01);
let dateEnd = endofmonth(dateBegin);
let top5ResultTypes = SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0
| summarize CountPerDay=count() by ResultType, bin(TimeGenerated, 24h)
| summarize AvgCount=avg(CountPerDay) by ResultType
| top 5 by AvgCount;
// 单独为每个Top5 ResultType生成唯一描述
let resultDescMap = SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType in (top5ResultTypes.ResultType)
| summarize ResultDescription=any(ResultDescription) by ResultType;
SigninLogs
| where TimeGenerated between (dateBegin .. dateEnd)
| where ResultType != 0 and ResultType in (top5ResultTypes.ResultType)
| summarize Count=count() by ResultType, bin(TimeGenerated, 24h)
| join kind=inner (resultDescMap) on ResultType
| project Count, Result=strcat(ResultType, " : ", substring(ResultDescription, 0, 30)), TimeGenerated
| render timechart
    with (
    title="Top 5 Login Failure Types Per Day",
    xtitle="Date",
    ytitle="Count",
    ysplit=panels 
    )

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