azure - 使用数据透视表在结果集后进行 KQL 过滤

Question

我正在尝试编写一个 KQL 查询，以从附加到 API 管理实例的 Application Insights 请求日志中返回行。当 api 名称满足以下两个条件时，我想返回该 api 名称的单行:

1. 至少两次失败。 (成功=“假”)
2. 至少 50% 的调用失败。

在下面的示例数据表中，我希望查询仅返回“/api/person”的一行，因为总共 6 次调用中有 5 次失败。不会出现“/api/banner”行，因为虽然总共有 2 次失败，但失败率低于 50%。

到目前为止，我已经能够使用数据透视来获取每个表的成功和失败总数。我更熟悉 TSQL，会创建一个嵌套子查询并过滤掉结果。我的理解是这种方法在 KQL 中不可用。

[![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)))]

当前结果集:

我怀疑有一种方法可以在 KQL 中通过不同的方法来实现这一点。任何提示或建议将不胜感激。预先感谢您!

Answer 1

I want to return a single row for an api name when that api name meets the following two criteria:

1. At least two failures. (success = "False")
2. At least 50% of total calls are failures.

In the follow example datatable I would want a query that would only return one row for "/api/person"

您可以使用 count() & countif() aggregation功能。

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),
]
| extend is_failure = success == false
| summarize total_failures = countif(is_failure),
            failure_percentage = 100.0 * countif(is_failure) / count() 
         by apiname
| where total_failures >= 2 and failure_percentage >= 50.0
| project apiname

<表类=“s-表”><标题>apiname <正文>/api/人