将连续的非零行标记为不同的分区，这次使用 kusto(Azure AppInsights 的查询语言)

Question

>我们正在使用 Azure DevOps Server 2019(on-prem(。因此，构建分析几乎不存在。因此，我们设置了一个过程，将完成的生成指标上传到 Azure AppInsights，这使我们能够编写各种有趣的仪表板。

我的经理想知道三件事：

1. 构建失败的频率如何？
2. 构建需要多长时间？
3. 构建会损坏多长时间？

前两个非常简单，但最后一个非常困难。

首先，我将问题转换为 Sql 并在 Tag 连续的非零行到不同的分区的帮助下解决了它？

收到 SQL 的答案后，我能够将其转换为 Kql(AppInsights 使用的 Kusto 查询语言(，如下所示：

customEvents
| where customDimensions.kind == "build"
and name == 'vNext_master_ci_r_git'
| project
startTime = todatetime(customDimensions.startTime),
outcome = tostring(customDimensions.buildResult)
| where outcome in ('succeeded', 'failed')
| order by startTime asc
| extend 
nextStartTime = next(startTime, 1)
| extend 
duration = case(outcome == 'failed', nextStartTime - startTime, totimespan(0)),
rn = row_number(),
rn2 = row_number(1, prev(outcome) != outcome)
| extend 
grp = rn - rn2
| where outcome == 'failed'
| summarize startTime = min(startTime), duration = toreal(sum(duration) / time(1h)) by grp
| order by startTime asc
| project startTime, duration

如您所见，这是相应 SQL 的 1-1 翻译。但是，我想知道是否有更简洁的方法可以在 Kql 中实现这一目标。我的理由是 Kql 旨在支持数据分析，因此也许它可以提供更优雅的解决方案。

Answer 1

我不确定以下内容是否是您寻找的，但它绝对更简洁。此外，我没有数据样本，所以我尝试从您的查询中构建其中的一部分。因此，让我们假设我们有：

let customEvents = datatable (customDimensions: dynamic, name: string)
[
dynamic({ "kind": "build", "startTime": "2021-01-14T10:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git", 
dynamic({ "kind": "build", "startTime": "2021-01-14T11:00:00Z", "buildResult": "failed" }), "vNext_master_ci_r_git", 
dynamic({ "kind": "build", "startTime": "2021-01-14T12:00:00Z", "buildResult": "failed" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T13:00:00Z", "buildResult": "failed" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T14:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T15:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T16:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T17:00:00Z", "buildResult": "failed" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T18:00:00Z", "buildResult": "failed" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T19:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T20:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git",
dynamic({ "kind": "build", "startTime": "2021-01-14T21:00:00Z", "buildResult": "succeeded" }), "vNext_master_ci_r_git"
];

然后我们可以做：

customEvents
| where customDimensions.kind == "build"
and name == 'vNext_master_ci_r_git'
| project
startTime = todatetime(customDimensions.startTime),
outcome = tostring(customDimensions.buildResult)
| serialize | extend statusChangedTime = row_window_session(startTime, 24h, 24h, outcome != prev(outcome))
| summarize by outcome, statusChangedTime
| serialize | extend prevStatusChangedTime = prev(statusChangedTime)
| where outcome == "succeeded" and isnotnull(prevStatusChangedTime)
| project startTime = prevStatusChangedTime, duration = (statusChangedTime - prevStatusChangedTime) / 1h

由于您已经在使用next和prev，我假设您知道窗口函数概述。row_window_session正是您正在寻找的方法。它可以让您根据行之间的检查按时开窗。因此，具有给定数据的查询输出为：

2021-01-14T11:00:00Z    3
2021-01-14T17:00:00Z    2

与查询中相同。