Query Details
Audit Pivot Tableof Privileged User Actions
Query
//Create a pivot table showing all the actions taken by your privileged users
//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA
//Lookup the IdentityInfo table for any users holding a privileged role
let privusers=
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| distinct AccountUPN;
//Search for all actions taken by those users in the last 7 days
AuditLogs
| where TimeGenerated > ago(7d)
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where Actor in (privusers)
//Create a pivot table counting each action for each user
| evaluate pivot(OperationName, count(), Actor)
| order by Actor asc Explanation
This query creates a pivot table that shows all the actions taken by privileged users. It uses data connectors for Azure Active Directory - Audit Logs and Microsoft Sentinel UEBA.
First, it looks up the IdentityInfo table to find users holding a privileged role within the last 21 days. Then, it searches for all actions taken by those users in the last 7 days in the AuditLogs.
Finally, it creates a pivot table that counts each action for each user and orders the results by the user's name in ascending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
IdentityInfoAuditLogs
Keywords
IdentityInfo,AuditLogs
Operators
letwheresummarizebyisnotemptywheredistinctextendtostringparse_jsoninevaluatepivotcount()order by