KQL Search

Query Details

HomeAI ToolsDevice Query

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 profile picture

Matt Zorich

Released: June 17, 2022

Tables

CloudAppEvents

Keywords

CloudAppEvents,IsAdminOperation,AccountType,RawEventData,UserId,ActionType,UserPrincipalName

Operators

where|=="true"AccountType"Regular"extendtostring()evaluatepivotcount()UserPrincipalName

Actions