Azure Workbook中PIM激活事件KQL关联查询求助

问题背景 #

需要创建Azure Workbook展示指定时间范围内的所有PIM角色激活记录，核心需求是将激活请求事件与对应的审批事件合并为一行展示。

PIM角色激活过程会生成以下日志事件：

• Add member to role requested (PIM activation)：每次激活必生成
• Add member to role approval requested (PIM activation)：仅需审批的场景生成
• Add member to role request approved (PIM activation)：仅需审批的场景生成
• Add member to role completed (PIM activation)：每次激活必生成

当前遇到的问题是：审批事件与请求事件的CorrelationID不统一，无法直接通过该字段关联；尝试拆分Requests和Approvals临时表后，不知如何实现基于请求用户UserObjectID、角色ID及时间戳的关联逻辑。

期望输出 #

每行对应一次激活记录，无需审批的记录审批人字段留空：

优化后的KQL查询 #

let TimeSpan = 35d;
// 提取PIM激活请求事件的核心字段，包含RequestId（若存在）
let Requests = AuditLogs
| where TimeGenerated > ago(TimeSpan)
| where OperationName == "Add member to role requested (PIM activation)"
| mv-apply AdditionalDetails on (
    extend RequestId = iif(AdditionalDetails.key == "RequestId", tostring(AdditionalDetails.value), "")
    | extend Justification = iif(AdditionalDetails.key == "Justification", tostring(AdditionalDetails.value), "")
    | extend StartTime = iif(AdditionalDetails.key == "StartTime", tostring(AdditionalDetails.value), "")
    | extend ExpirationTime = iif(AdditionalDetails.key == "ExpirationTime", tostring(AdditionalDetails.value), "")
)
| mv-apply TargetResources on (
    extend RoleName = tostring(TargetResources.displayName)
    | extend RoleId = tostring(TargetResources.id)
)
| mv-apply InitiatedBy on (
    extend RequestorName = tostring(InitiatedBy.user.displayName)
    | extend RequestorObjectId = tostring(InitiatedBy.user.id)
)
// 按请求事件的唯一标识聚合（用TimeGenerated+RequestorObjectId+RoleId确保唯一性）
| summarize 
    take_any(RequestId),
    take_any(Justification),
    take_any(StartTime),
    take_any(ExpirationTime),
    take_any(RoleName)
    by RequestorName, RequestorObjectId, RoleId, TimeGenerated;

// 提取PIM审批通过事件的核心字段
let Approvals = AuditLogs
| where TimeGenerated > ago(TimeSpan)
| where OperationName == "Add member to role request approved (PIM activation)"
| mv-apply AdditionalDetails on (
    extend RequestId = iif(AdditionalDetails.key == "RequestId", tostring(AdditionalDetails.value), "")
    | extend RequestorObjectId = iif(AdditionalDetails.key == "RequestorId", tostring(AdditionalDetails.value), "")
)
| mv-apply TargetResources on (
    extend RoleId = tostring(TargetResources.id)
)
| mv-apply InitiatedBy on (
    extend ApproverName = tostring(InitiatedBy.user.displayName)
)
// 按审批事件的关联字段聚合
| summarize 
    take_any(ApproverName)
    by RequestId, RequestorObjectId, RoleId, TimeGenerated;

// 关联请求与审批事件：优先用RequestId，其次用请求人ID+角色ID+时间窗口（1小时内）
Requests
| left join kind=leftouter Approvals
    on Requests.RequestId == Approvals.RequestId
    or (
        Requests.RequestorObjectId == Approvals.RequestorObjectId
        and Requests.RoleId == Approvals.RoleId
        and Approvals.TimeGenerated between (Requests.TimeGenerated .. Requests.TimeGenerated + 1h)
    )
// 去重并整理最终输出字段
| summarize
    Requestor = take_any(Requests.RequestorName),
    Approver = anyif(take_any(Approvals.ApproverName), isnotempty(Approvals.ApproverName)),
    Role = take_any(Requests.RoleName),
    Justification = take_any(Requests.Justification)
    by Requests.TimeGenerated, Requests.RequestorObjectId, Requests.RoleId
| sort by TimeGenerated desc
| project Requestor, Approver, Role, Justification

关键逻辑说明 #

1. 字段提取优化：在请求和审批事件中统一提取RequestId（若日志存在该字段）、请求用户ID、角色ID等核心关联字段，为后续关联做准备。
2. 双重关联策略：
   • 优先使用RequestId进行精确匹配，这是最可靠的关联方式；
   • 若RequestId不存在或匹配失败，通过请求用户ID+角色ID+1小时时间窗口进行模糊匹配，覆盖日志中RequestId缺失的场景。
3. 结果整理：通过summarize去重，确保每行对应一次激活记录，无审批的记录自动留空Approver字段。

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