求助：构建KQL查询展示URL点击后的用户登录（结果异常）

现有查询的问题点 #

• 多对多Join导致结果不稳定：两次join都用默认的inner join，当一个邮件对应多次点击、一个用户对应多次登录时，会产生笛卡尔积，每次结果的匹配组合可能因为数据新增或排序变化导致结果不一致。
• 时间条件逻辑矛盾：注释写的是“邮件接收后30分钟内的登录”，但实际代码判断的是UserLogons - TimeOfClick在0-15分钟，和需求不符；且未明确确保登录时间晚于点击时间，存在边界逻辑漏洞。
• 未实现“点击后接下来10次登录”的需求：现有查询没有对登录记录按时间排序并取前10，会返回所有符合时间范围的登录，而非指定数量。
• 账号匹配逻辑不严谨：EmailEvents中用split(RecipientEmailAddress, "@")[0]取账号前缀，而UrlClickEvents用AccountUpn contains "bob.smith"，若存在同名不同域的用户，可能出现错误匹配；同时join时用前缀匹配AccountName，若IdentityLogonEvents中的AccountName是完整UPN，会直接匹配失败。
• 布尔值判断错误：IsClickedThrough是布尔类型字段，不能和字符串"0"比较，应该用IsClickedThrough == true。

修正后的查询代码 #

EmailEvents
| where SenderFromAddress == "example@example.com"  // 用精确匹配代替contains，避免误匹配相似邮箱
| where RecipientEmailAddress contains "bob.smith"
| project TimeEmailReceived = Timestamp, Subject, SenderFromAddress, 
          AccountUpn = RecipientEmailAddress,  // 直接用完整收件人邮箱作为UPN，确保匹配一致
          NetworkMessageId
// 用innerunique join减少重复匹配，只保留每个NetworkMessageId的唯一匹配
| join kind=innerunique ( 
    UrlClickEvents 
    | where AccountUpn contains "bob.smith"
    | where Workload == "Email"
    | where ActionType == "ClickAllowed" or IsClickedThrough == true  // 修正布尔值判断逻辑
    | project Url, UrlChain, ActionType, NetworkMessageId, TimeOfClick = Timestamp 
) on NetworkMessageId
// 先对登录记录按时间排序，取每个用户的前10次登录，再关联点击事件
| join kind=leftouter (
    IdentityLogonEvents
    | where AccountUpn contains "bob.smith"  // 用UPN匹配，避免前缀同名问题
    | project UserLogons = Timestamp, AccountUpn, DeviceName, IPAddress, Location, LogonType
    | order by UserLogons asc
    | partition by AccountUpn (top 10 by UserLogons asc)
) on AccountUpn
// 过滤点击之后15分钟内的登录（可根据需求调整时间范围）
| where UserLogons > TimeOfClick and (UserLogons - TimeOfClick) <= 15m
// 按点击时间、登录时间排序，确保结果稳定
| order by TimeOfClick asc, UserLogons asc

关键优化说明 #

• 指定Join类型：用kind=innerunique避免同一邮件多次点击导致的重复匹配，kind=leftouter保证点击事件即使无对应登录也能展示。
• 统一账号匹配字段：用完整的AccountUpn（收件人邮箱）进行匹配，彻底避免前缀同名的错误。
• 修正字段类型判断：将IsClickedThrough != "0"改为IsClickedThrough == true，符合KQL字段类型规范。
• 实现取前10次登录：通过partition by AccountUpn (top 10 by UserLogons asc)先获取用户的前10次登录，再结合点击时间过滤，精准满足需求。
• 明确时间逻辑：添加UserLogons > TimeOfClick确保登录在点击之后，消除时间计算的边界歧义。

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