KQL查询疑问：敏感度标签报表关联及静态身份表咨询

我正尝试基于部门、用户及指定操作生成敏感度标签使用情况报表，需关联两张表：

• MicrosoftPurviewInformationProtection：包含敏感度标签、操作、Workload、UserId信息（存在重复行，但有唯一ID区分，需保留所有数据）
• IdentityInfo：包含用户AccountUPN、邮箱地址、部门名称信息（非静态表，同一用户存在多条历史记录）

遇到的问题：MicrosoftPurviewInformationProtection的UserId字段有时为邮箱（如abc@xyz.com），有时为UPN（如123@xyz.com）。我尝试先将其与IdentityInfo的AccountUPN左连接，再与MailAddress左连接，最后用union合并，查询语句如下：

MicrosoftPurviewInformationProtection
| join kind=leftouter  IdentityInfo on ($left.UserId==$right.AccountUPN)
| union (MicrosoftPurviewInformationProtection | join kind = leftouter  IdentityInfo on ($left.UserId==$right.MailAddress))
| where Operation in~ ("SensitivityLabelApplied",
    "FileSensitivityLabelApplied",
    "FileSensitivityLabelChanged",
    "SensitivityLabelUpdated",
    "SiteSensitivityLabelApplied",
    "SiteSensitivityLabelChanged")
| extend ProcessName= tostring(Common.ProcessName)
| extend Apps = strcat(Application,ProcessName)
| summarize count() by Operation, SensitivityLabelId, Department,Apps,Workload,AccountUPN,MailAddress

运行后虽能看到所需字段，但有两点疑虑：

1. 首次使用union与join组合，不确定是否达成预期效果
2. 大量行的AccountUPN/邮箱地址字段为空

同时咨询：

1. 当前查询逻辑是否正确？若正确，为何会出现空白字段？
2. 是否存在符合需求的静态用户身份信息表（仅在用户创建时更新，包含用户UPN、邮箱、部门）？

问题1：当前查询逻辑的问题及空白字段原因 #

当前查询逻辑不正确，会导致数据重复和空白字段问题：

• 两次left outer join后用union合并，会把同一条MicrosoftPurviewInformationProtection的记录重复输出（如果UserId同时匹配到AccountUPN和MailAddress的话），而且会引入大量未匹配成功的空值行。
• 空白字段的核心原因：
  1. 部分UserId既不匹配IdentityInfo中的AccountUPN，也不匹配MailAddress，连接后字段自然为空
  2. union操作保留了两次连接中未匹配成功的行，这些行的身份字段必然为空
  3. IdentityInfo存在多条历史记录，连接时可能匹配到无效的历史条目，或者部分用户的信息在IdentityInfo中本身就缺失

优化后的查询语句 #

改用分阶段匹配+合并字段的逻辑，同时先处理IdentityInfo的历史记录，避免无效数据干扰：

// 先从IdentityInfo中提取用户的有效记录（这里按时间取最新，若有创建时间字段可替换为arg_min）
let valid_identity = IdentityInfo
| summarize arg_max(TimeGenerated, *) by AccountUPN;
MicrosoftPurviewInformationProtection
| where Operation in~ ("SensitivityLabelApplied",
    "FileSensitivityLabelApplied",
    "FileSensitivityLabelChanged",
    "SensitivityLabelUpdated",
    "SiteSensitivityLabelApplied",
    "SiteSensitivityLabelChanged")
| extend ProcessName= tostring(Common.ProcessName)
| extend Apps = strcat(Application, ProcessName)
// 第一步：用UserId匹配AccountUPN
| lookup valid_identity on $left.UserId == $right.AccountUPN
// 第二步：对未匹配到的记录，用UserId匹配MailAddress
| lookup valid_identity on $left.UserId == $right.MailAddress and isempty(Department)
// 合并两次匹配的有效字段，覆盖空值
| extend 
    AccountUPN = coalesce(AccountUPN, AccountUPN1),
    MailAddress = coalesce(MailAddress, MailAddress1),
    Department = coalesce(Department, Department1)
// 移除重复的临时字段
| project-away AccountUPN1, MailAddress1, Department1
| summarize count() by Operation, SensitivityLabelId, Department, Apps, Workload, AccountUPN, MailAddress

问题2：静态用户身份信息表的可能性 #

不存在完全静态（仅创建时更新）的内置用户身份表，但可以通过以下方式实现类似效果：

• Azure AD内置表：部分环境中存在AADUsers表，存储用户基础身份信息，仅在用户属性变更时更新，更新频率低；若需仅保留创建时的信息，可通过筛选用户创建时间的初始记录生成静态视图。
• 自定义静态视图：基于IdentityInfo表，用summarize arg_min(TimeGenerated, *) by AccountUPN筛选用户创建时的初始记录，将结果保存为新的静态表或视图，后续报表直接调用该视图即可。
• Purview身份同步：若Purview开启了身份目录同步，MicrosoftPurviewInformationProtection表的UserId可能直接关联到同步后的身份数据，无需额外连接IdentityInfo表即可获取部门等信息。

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