Stuck at PIM Diagnostics via KQL

Question

I'd like to create an Azure Workbook to display all PIM activations within last x days and after going crazy and a lot of shed tears, now i'm stuck. I don't get how to combine the request event with the approval event. As far as I know (or rather, as far as I concluded from data in my Log Analytics Workspace) during the process of activating a Role within PIM there are 2 or 4 events logged:

1. Add member to role requested (PIM activation)
2. Add member to role approval requested (PIM activation)
3. Add member to role request approved (PIM activation)
4. Add member to role completed (PIM activation)

1 & 4 are logged during every activation and 2 + 3 are logged for approvals. So far, so easy.

But how do these events correlate with each other so that I can display them automatically with KQL within 1 Line?

I don't see any correlating ID (because, funfact, the "CorrelationID" changes between event 2 and 3). I've built a KQL query which is probably totally overengineered (because I had no clue of Kusto 3 days ago and my SQL Knowledge was used 11 years ago...)

A few words about the following code: I had the idea of creating the 2 temporary tables Requests and Approvals and join them together - preferred via an correlating ID, but I can't find any - via the UserObjectID from the requesting User in combination with the RoleID and the TimeGenerated (as close after the requesting event, as possible). But I have no clue how to do this. My Vision for the result is 1 activation per line and the events without any needed approval have an empty field in this column like this:

Requestor	Approver	Role	Justification
Bob		Helpdesk Admin	Ticket 1
Dave	Kate	Global Admin	Change 5

let TimeSpan = 35d
let Request = (
    AuditLogs
    | where TimeGenerated > ago(TimeSpan)
    | where OperationName == "Add member to role requested (PIM activation)"
    | mv-apply AdditionalDetails on(
        extend TicketNumber = iif(AdditionalDetails.key == "TicketNumber", 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), "")
        | extend IP = iif(AdditionalDetails.key== "ipaddr", tostring(AdditionalDetails.value), "")
        )
    | mv-apply tr = TargetResources on(
        extend TargetUPN = TargetResources.userPrincipalName
        | extend Permission = iff(tr.displayName == "Member", tostring(parse_json(TargetResources)[3].displayName), tostring(tr.displayName))
        | extend RequestedRoleId = parse_json(TargetResources)["id"]
    )
    | mv-apply InitiatedBy on (
        extend InitiatorUPN = InitiatedBy.user.userPrincipalName
        | extend InitiatorDisplayName = InitiatedBy.user.displayName
        | extend RequestorRoleId = InitiatedBy.user.id
    )
    | extend UserInternal = iff( InitiatorUPN contains "ext@","False","True") 
    | summarize take_any(TicketNumber)
        ,take_any(RequestorRoleId)
        ,take_any(Justification)
        ,take_any(StartTime)
        ,take_any(Expirationtime)
        ,take_any(IP)
        ,take_any(TargetUPN)
        ,take_any(InitiatorUPN)
        ,take_any(InitiatorDisplayName)
        ,take_any(UserInternal)
        ,take_any(Permission)
        ,take_any(RequestedRoleId) by TimeGenerated
);
let Approvals = (
    AuditLogs
    | where OperationName == "Add member to role request approved (PIM activation)"
    | where TimeGenerated > ago(TimeSpan)
    | mv-apply AdditionalDetails on(
        extend ApproverJustification = iif(AdditionalDetails.key=="Justification", tostring(AdditionalDetails.value), "")
        | extend RequestorUserID = iif(AdditionalDetails.key=="RequestId", tostring(AdditionalDetails.value), "") 
        )
    | mv-apply InitiatedBy on(
        extend ApproverDisplayName = parse_json(InitiatedBy)["user"]["displayName"]
        | extend ApproverUPN = parse_json(InitiatedBy)["user"]["userPrincipalName"]
    )
    | mv-apply TargetResources on(
        extend RequestedRoleId = parse_json(TargetResources)["id"]
    )
    | extend ApproverInternal = iff( InitiatorUPN contains "ext@","False","True") 
    | summarize take_any(RequestorUserID)
        ,take_any(RequestedRoleId)
        ,take_any(ApproverJustification)
        ,take_any(ApproverDisplayName)
        ,take_any(ApproverUPN)
        ,take_any(ApproverInternal)by TimeGenerated
);

Answer 1

I had a hard time creating a simplified view myself but came up with this:

let daterange = 30d;
AuditLogs
| where LoggedByService == "PIM"
| where OperationName == "Add member to role completed (PIM activation)"
| where TimeGenerated > ago(daterange)
| project 
    TimeGenerated, 
    Reason = ResultReason, 
    Result, 
    Requestor = Identity, 
    Category,
    Role = tostring(TargetResources[0].displayName), 
    ResourceName = tostring(TargetResources[3].displayName), 
    ResourceType = tostring(TargetResources[3].type), 
    Start = iif(Category == "GroupManagement", todatetime(AdditionalDetails[2].value), todatetime(AdditionalDetails[3].value)),
    End = iif(Category == "GroupManagement", todatetime(AdditionalDetails[3].value), todatetime(AdditionalDetails[4].value)), 
    CorrelationId
| join kind=leftouter  (
    AuditLogs
        | where TimeGenerated > ago(daterange)
        | where LoggedByService == "PIM"
        | where OperationName == "Add member to role request approved (PIM activation)"
        | project CorrelationId, Approval = ResultDescription, Approver = Identity, Approved = Result) 
    on CorrelationId
| distinct TimeGenerated, Requestor, Reason, Approver, Approval, Category, Role, ResourceName, ResourceType, Start, End

Request and approval are related through the CorrelationId column. This works if you use the Add member to role completed (PIM activation) event as source.

The only thing missing is the ticket number, we don't use it, but it should be easy to add that one as well.