DCA Pivot Table Admin Operations
Query
//Defender for Cloud Apps tracks administrative actions under the 'isAdminOperation' flag. This query will build a pivot table of all admin operations completed by your users
//Works in both Sentinel and Advanced Hunting
//Data connector required for this query - Advanced Hunting license or M365 Defender - CloudAppEvents for Sentinel
CloudAppEvents
| where IsAdminOperation == "true"
| where AccountType == "Regular"
| extend UserPrincipalName = tostring(RawEventData.UserId)
| evaluate pivot(ActionType, count(), UserPrincipalName)Explanation
This query uses the Defender for Cloud Apps data to track administrative actions performed by users. It creates a pivot table that shows the count of each administrative action type performed by each user. The query requires a data connector such as Advanced Hunting or M365 Defender - CloudAppEvents for Sentinel.
Details
Matt Zorich
Released: June 17, 2022
Tables
CloudAppEvents
Keywords
CloudAppEventsIsAdminOperationAccountTypeRawEventDataUserIdActionTypeUserPrincipalName
Operators
where|=="true"AccountType"Regular"extendtostring()evaluatepivotcount()UserPrincipalName