如何用KQL筛选满足故障条件的API Management请求日志？

你可以通过两种方式实现需求，一种是基于你已有的pivot查询进行扩展，另一种是用更简洁的summarize分组统计方式，后者更符合KQL的惯用写法：

方式一：基于现有pivot查询扩展 #

datatable(apiname:string, success:bool, timestamp:timespan)
[
   "/api/person", "True", time(00:00:00),
   "/api/status", "True", time(00:00:24),
   "/api/banner", "True", time(00:00:20),
   "/api/banner", "True", time(00:00:19),
   "/api/banner", "True", time(00:00:21),
   "/api/banner", "False", time(00:00:22),
   "/api/banner", "False", time(00:00:23),
   "/api/person", "False", time(00:00:00),
   "/api/person", "False", time(00:00:37),
   "/api/person", "False", time(00:00:47),
   "/api/person", "False", time(00:00:53),
   "/api/person", "False", time(00:00:55),
] 
| project apiname, success
| evaluate pivot(success, count(tobool(success)))
// 计算总调用数和失败率
| extend total_calls = False + True, failure_rate = todouble(False) / total_calls
// 过滤符合条件的API
| where False >= 2 and failure_rate >= 0.5
// 只返回API名称
| project apiname

方式二：用summarize分组统计（推荐） #

这种写法更简洁高效，直接通过分组统计得到需要的指标，避免pivot的列名依赖：

datatable(apiname:string, success:bool, timestamp:timespan)
[
   "/api/person", "True", time(00:00:00),
   "/api/status", "True", time(00:00:24),
   "/api/banner", "True", time(00:00:20),
   "/api/banner", "True", time(00:00:19),
   "/api/banner", "True", time(00:00:21),
   "/api/banner", "False", time(00:00:22),
   "/api/banner", "False", time(00:00:23),
   "/api/person", "False", time(00:00:00),
   "/api/person", "False", time(00:00:37),
   "/api/person", "False", time(00:00:47),
   "/api/person", "False", time(00:00:53),
   "/api/person", "False", time(00:00:55),
] 
// 按API分组，统计总调用数和失败调用数
| summarize 
    total_calls = count(),
    failed_calls = countif(success == false) 
    by apiname
// 计算失败率
| extend failure_rate = todouble(failed_calls) / total_calls
// 过滤条件：至少2次失败，且失败率≥50%
| where failed_calls >= 2 and failure_rate >= 0.5
// 返回目标字段
| project apiname

关键逻辑说明 #

1. summarize分组统计：替代TSQL的子查询分组，直接按apiname聚合，用count()计算总调用量，countif()精准统计失败次数。
2. 失败率计算：用todouble()强制转换为浮点数，避免整数除法导致的精度丢失问题。
3. 过滤条件：直接通过where子句筛选符合要求的API，无需嵌套查询结构。

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