如何基于另一字段值聚合字段？KQL实现登录日志统计查询

日志数据示例：

本次需要实现的统计需求：

• 统计登录成功的唯一用户数（对应Identifier字段）、登录失败的唯一用户数
• 统计用户成功登录之前产生的失败登录尝试次数，确认KQL是否支持该类统计

原有查询的核心问题：countif仅能统计符合条件的事件总条数，无法对Identifier字段做去重计数，不符合唯一用户数的统计要求。

1. 成功/失败登录唯一用户数统计 #

KQL提供了dcountif函数，专门用于实现带条件的去重计数，直接替换原有countif逻辑即可，参考查询如下：

customEvents
| extend
    LoginIdentifier = tostring(customDimensions["Identifier"]),
    IsLoginSuccess = name startswith "Success"
| summarize
    // 总参与登录流程的去重用户数
    TotalLoginUserCount = dcount(LoginIdentifier),
    // 有成功登录记录的去重用户数
    SuccessUniqueUserCount = dcountif(LoginIdentifier, IsLoginSuccess),
    // 有失败登录记录的去重用户数
    FailureUniqueUserCount = dcountif(LoginIdentifier, IsLoginSuccess == false)

说明：部分用户可能同时存在成功、失败登录记录，因此成功用户数+失败用户数可能大于总登录用户数，属于正常情况。

2. 用户成功登录前失败尝试次数统计 #

KQL完全支持这类时序维度的关联统计，核心逻辑是按用户分组找到首次成功登录的时间点，再统计该时间点之前产生的失败登录事件数即可，参考查询如下：

customEvents
| extend
    LoginIdentifier = tostring(customDimensions["Identifier"]),
    IsLoginSuccess = name startswith "Success"
// 过滤无有效用户标识的日志
| where isnotempty(LoginIdentifier)
// 按用户分组，计算首次成功登录时间，同时打包该用户所有登录事件
| summarize
    FirstSuccessTimestamp = minif(timestamp, IsLoginSuccess),
    UserLoginEvents = pack_all()
    by LoginIdentifier
// 展开打包的事件集合
| mv-expand UserLoginEvents
| extend
    EventTime = todatetime(UserLoginEvents.timestamp),
    IsEventFailure = tobool(UserLoginEvents.IsLoginSuccess) == false
// 筛选首次成功前的失败事件：如需排除从未成功登录的用户，删除isnull(FirstSuccessTimestamp)判断即可
| where IsEventFailure and (isnull(FirstSuccessTimestamp) or EventTime < FirstSuccessTimestamp)
// 统计单个用户的首次成功前失败次数
| summarize FailCountBeforeFirstSuccess = count() by LoginIdentifier
// 如需查看失败次数的用户分布，可替换为以下汇总逻辑：
// | summarize UserCount = count() by FailCountBeforeFirstSuccess
// | order by FailCountBeforeFirstSuccess asc

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