Kusto无匹配数据时返回timespan(0)的问题排查与解决

当DataMetadata["category"] == "Warning"过滤后没有匹配的行时，查询后续的summarize、top等算子都没有输入数据，最终的summarize无法生成任何结果，因此返回空表，而不是你期望的00:00:00:00格式值。

通过添加兜底的默认行，确保即使没有匹配数据时也能返回目标格式值，以下是两种可行实现：

方式一：用union合并默认行 #

先执行原查询，再将结果与包含默认值的虚拟表做union，最后取有效结果（原查询有结果则用原结果，否则用默认值）：

let original_query = 
    Telemetry
    | where DataMetadata["category"] == "Warning"
    | summarize 
            Duration = sum(case(Name == "Event", totimespan(Value), totimespan(0))),
            Text = min(case(Name == "Information", tostring(Value), "N/A")),
            DeviceID = min(case(Name == "Ident", tostring(Value), "N/A"))
            by Timestamp
    | summarize TotalDuration = sum(Duration) by Text,DeviceID
    | top 2 by TotalDuration
    | summarize Duration = max(case(isnotnull(TotalDuration) or isnotempty(TotalDuration), strcat("Duration: ",format_timespan(TotalDuration, 'dd:hh:mm:ss'), "[sec] ",DeviceID," - ",Text), tostring(timespan(0))));
original_query
| union (datatable(Duration:string)["Duration: 00:00:00:00[sec] N/A - N/A"])
| summarize Duration = max(Duration)

方式二：用materialize判断结果是否为空 #

先将原查询结果物化，再判断结果行数，为空则返回默认行：

let result = materialize(
    Telemetry
    | where DataMetadata["category"] == "Warning"
    | summarize 
            Duration = sum(case(Name == "Event", totimespan(Value), totimespan(0))),
            Text = min(case(Name == "Information", tostring(Value), "N/A")),
            DeviceID = min(case(Name == "Ident", tostring(Value), "N/A"))
            by Timestamp
    | summarize TotalDuration = sum(Duration) by Text,DeviceID
    | top 2 by TotalDuration
    | summarize Duration = max(case(isnotnull(TotalDuration) or isnotempty(TotalDuration), strcat("Duration: ",format_timespan(TotalDuration, 'dd:hh:mm:ss'), "[sec] ",DeviceID," - ",Text), tostring(timespan(0))))
);
result
| union (datatable(Duration:string)["Duration: 00:00:00:00[sec] N/A - N/A"] | where result | count == 0)

补充说明 #

你可以根据业务需求调整默认行的文本内容，比如把N/A替换为更贴合场景的描述。

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