Account Elevated to New Role

Requête originale, sans modification.

let auditList =
AuditLogs
| where TimeGenerated >= ago(14d)
| where OperationName =~ "Add member to role completed (PIM activation)"
| where Result =~ "success"
| extend TargetUserPrincipalName = tostring(TargetResources[2].userPrincipalName)
| extend displayName = tostring(TargetResources[0].displayName)
| extend displayName2 = tostring(TargetResources[3].displayName)
| extend ElevatedRole = iif(displayName =~ "Member", displayName2, displayName)
;
let lookbackList = auditList
| where TimeGenerated between(ago(14d)..ago(1d))
;
let recentList = auditList
| where TimeGenerated > ago(1d)
;
let newlyElevated = recentList
| join kind = leftanti lookbackList on ElevatedRole, TargetUserPrincipalName
;
newlyElevated | project Id, AdditionalDetails
| mv-expand bagexpansion=array AdditionalDetails
| evaluate bag_unpack(AdditionalDetails)
| extend key = column_ifexists("key", ''), value = column_ifexists("value", '')
| evaluate pivot(key, make_set(value))
| extend ipaddr = todynamic(column_ifexists("ipaddr", ""))
| mv-expand ipaddr
| project Id, InitiatingIPAddress = tostring(ipaddr)
| join kind=rightouter newlyElevated on Id
| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
| extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
| extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
| extend InitiatingIPAddress = iff(isnotempty(tostring(InitiatedBy.user.ipAddress)), tostring(InitiatedBy.user.ipAddress), InitiatingIPAddress)
| extend ElevatedBy = iff(isnotempty(InitiatingUserPrincipalName), InitiatingUserPrincipalName, InitiatingAppName)
| extend ElevatedUser = TargetUserPrincipalName
| extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])
| extend TargetAccountName = tostring(split(TargetUserPrincipalName, "@")[0]), TargetAccountUPNSuffix = tostring(split(TargetUserPrincipalName, "@")[1])
| project-reorder ElevatedUser, ElevatedRole, ResultReason, ElevatedBy, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIPAddress, TargetUserPrincipalName