RE: In KQL, IF a new role is added by user through AD PIM, how do you extract their details...

[JMSHW0420]

Hello,

I posted previously, a similar query about HOW do you extract details about the user who initiated the action to ADD a new privileged role to ANOTHER user?

I have simplified my BASE query to this...

AuditLogs
| where TimeGenerated between (ago(90d) ..now())
| where OperationName == "Add member to role"
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend UserAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].userPrincipalName)))))
| extend RoleAdded = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))

The query WORKS but:

| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

...sometimes returns BLANK when ADDING a new privileged role has been completed through AD PIM. It tries to return the Service Principal and return a NULL value.

Now as the initiating user has completed the ACTION through AD PIM, how can I extract their details?

Now when the ADD action is done outside AD PIM, it returns the UPN (User Principle Name).

[#2]

Sorry to reach out like this @Clive_Watson

You have been extremely helpful before, but do you have any suggestions for the above please?

Cheers

[#3]

Not sure where the null info would be, but you could always at least populate a value in that field, maybe add a final line of:

| extend UserPrincipalName = iif(isempty(UserPrincipalName),Identity,UserPrincipalName)

This should add "MS-PIM" to the gaps

[#4 · Accepted solution]

Hi @Clive_Watson,

Yes, that did exactly that but still did not return the actual user committing the add action.

The following query does though when the add action is performed through PIM.

AuditLogs
| where TimeGenerated > ago (30d)
| where OperationName in ("Add eligible member to role in PIM completed (permanent)", "Add eligible member to role in PIM completed (timebound)", "Add member to role in PIM completed (permanent)", "Add member to role in PIM completed (timebound)")
| extend ['Azure AD Role Name'] = tostring(TargetResources[0].displayName)
| extend Target = tostring(TargetResources[2].userPrincipalName)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| project TimeGenerated, OperationName, Actor, Target, ['Azure AD Role Name']

@Clive_Watson thank you for your input. It really did help.