Dca Pivottableadminactions

KQL detection rule by reprise99 · MIT

Create a pivot table of all actions in Defender for Cloud Apps by your privileged users over the last 7 days Lookup the IdentityInfo table for any users holding a privileged role

//Create a pivot table of all actions in Defender for Cloud Apps by your privileged users over the last 7 days
//Lookup the IdentityInfo table for any users holding a privileged role

//Data connector required for this query - M365 Defender - CloudAppEvents

//Microsoft Sentinel query
let privusers=
    IdentityInfo
    | where TimeGenerated > ago(21d)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    //Add any roles that you are interested in auditing
    | where AssignedRoles has_any ("Global Administrator", "Security Administrator", "SharePoint Administrator")
    | distinct AccountUPN;
CloudAppEvents
| where TimeGenerated > ago(7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
| where UserId in~ (privusers)
//Create pivot table of all actions by each user
| evaluate pivot(Activity, count(), UserId)

//Advanced hunting query

//Data connector required for this query - Advanced Hunting license

CloudAppEvents
| where Timestamp > ago(7d)
| extend Operation = tostring(RawEventData.Operation)
| extend UserId = tostring(RawEventData.UserId)
| extend Workload = tostring(RawEventData.Workload)
//Advanced hunting doesn't retain role information about users, but you can add a list of users in manually to create a table
| where UserId in~ ("admin1@domain.com", "admin2@domain.com")
//Create a new column that adds workload and operation together to make the events more readable
| extend Activity = strcat(Workload, " - ", Operation)
//Create pivot table of all actions by each user
| evaluate pivot(Activity, count(), UserId)

[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

98d3fe45-dc97-49e6-9354-90ace5f862c8

Propose edits, view related rules, and engage with the community through comments.

Executable code, not a declarative rule

This submission is arbitrary [[ currentRule.format ? currentRule.format.toUpperCase() : '' ]] code, not a declarative detection rule — review it as you would any script before running it, and note that it cannot be safely aggregated or auto-executed alongside YARA/Sigma/Suricata/CRS rules.

Rejected pattern present

[[ reason ]]

What does this mean?

Format & documentation suggestions

Not a security issue — this rule is valid and was accepted as-is. These are optional fields/conventions its own format recommends, mainly affecting its quality score and how well it documents itself for other reviewers.

[[ reason ]]

What do these messages mean?

[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

[[ currentRule.title ]]

Community Discussion