Forum Discussion

JMSHW0420's avatar
JMSHW0420
Iron Contributor
Feb 22, 2023

RE: How do you extract the UPN of a privileged user who has added a role via PIM?

The following query, to identify the action of a privileged user adding a member to a role, works apart from showing the UPN of that privileged user when they 'committed' the action via VIA PIM.

 

//Lookup the IdentityInfo table for any users holding a privileged role
let privilegedusers=
IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| distinct AccountUPN;
//Find actions taken by those users previously
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)))
| where isnotempty(UserPrincipalName)
| where UserPrincipalName in (privilegedusers)
| distinct TimeGenerated, UserPrincipalName, UserAdded, RoleAdded

 

So with the following:

 

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

 

the UserPrincipalName is NULL (in the AuditLogs table) whenever it is showing the Service Principal of MS-PIM instead of the direct user initiating the 'add' action. When the add action is performed outside of PIM, it shows the user (UPN).

 

How can I extract the ACTUAL user who committed the 'action' of adding a member, instead of the Service Principal? 

KQL
  • Varun_Ghildiyal's avatar
    Varun_Ghildiyal
    Brass Contributor
    Mar 10, 2023

    JMSHW0420 

     

    If the query is showing the Service Principal of MS-PIM instead of the direct user initiating the 'add' action, you can try using the "InitiatedBy.servicePrincipalName" property to retrieve the service principal name of the PIM system.

    //Lookup the IdentityInfo table for any users holding a privileged role
let privilegedusers=
IdentityInfo
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| distinct AccountUPN;
//Find actions taken by those users previously
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)))
| extend ServicePrincipalName = tostring(parse_json(tostring(InitiatedBy.servicePrincipalName)))
| where isnotempty(UserPrincipalName)
| where UserPrincipalName in (privilegedusers)
| distinct TimeGenerated, UserPrincipalName, UserAdded, RoleAdded, ServicePrincipalName

    With this modification, the query should return the actual user who committed the 'add' action whenever possible. However, in some cases where the action is performed via PIM, the Service Principal of MS-PIM may still be shown instead of the actual user

Resources