Forum Discussion

JMSHW0420's avatar
JMSHW0420
Iron Contributor
Feb 23, 2023

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

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).

  • JMSHW0420's avatar
    JMSHW0420
    Mar 01, 2023
    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.
  • JMSHW0420's avatar
    JMSHW0420
    Iron Contributor
    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
    • Clive_Watson's avatar
      Clive_Watson
      Bronze Contributor
      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
      • JMSHW0420's avatar
        JMSHW0420
        Iron Contributor
        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.

Resources