Visualisation of the users with the most HardDelete actions performed

Visualization Hard Deletions By User

Query

CloudAppEvents
| where ActionType == 'HardDelete'
| extend
     Workload = parse_json(RawEventData).Workload,
     UserId = parse_json(RawEventData).UserId,
     ResultStatus = parse_json(RawEventData).ResultStatus,
     AffectedItemsJson = parse_json(RawEventData).AffectedItems
| extend ParentFolderPath = extract('"Path":"([^"]*)"', 1, tostring(AffectedItemsJson))
| where ResultStatus == 'Succeeded'
| where not(ParentFolderPath has_any ("Calendar", 'Agenda')) // Remove personal deletions of Calendar items
| summarize count() by tostring(UserId)
| top 50 by count_
| render columnchart with (title='HardDeletions by User')

About this query

Visualisation of the users with the most HardDelete actions performed

Defender XDR

Sentinel

CloudAppEvents
| where ActionType == 'HardDelete'
| extend
     Workload = parse_json(RawEventData).Workload,
     UserId = parse_json(RawEventData).UserId,
     ResultStatus = parse_json(RawEventData).ResultStatus,
     AffectedItemsJson = parse_json(RawEventData).AffectedItems
| extend ParentFolderPath = extract('"Path":"([^"]*)"', 1, tostring(AffectedItemsJson))
| where ResultStatus == 'Succeeded'
| where not(ParentFolderPath has_any ("Calendar", 'Agenda')) // Remove personal deletions of Calendar items
| summarize count() by tostring(UserId)
| top 50 by count_
| render columnchart with (title='HardDeletions by User')

Explanation

This query is designed to visualize the users who have performed the most "HardDelete" actions within a cloud application. Here's a simple breakdown of what the query does:

Data Source: It starts by looking at events from the CloudAppEvents table where the action type is 'HardDelete'.
Extract Information: It extracts specific details from the raw event data, such as the workload, user ID, result status, and affected items.
Filter Successful Actions: It filters the results to include only those actions where the result status is 'Succeeded', meaning the deletion was successful.
Exclude Specific Folders: It excludes deletions that occurred in personal folders like "Calendar" or "Agenda" to focus on more relevant deletions.
Count Deletions by User: It counts the number of successful hard deletions performed by each user.
Top Users: It selects the top 50 users who have performed the most hard deletions.
Visualization: Finally, it creates a column chart to visually represent the number of hard deletions performed by each of these top users, with the chart titled 'HardDeletions by User'.

This query is duplicated for both Defender XDR and Sentinel, indicating that the same analysis is being performed in both security platforms.

Details

Bert-Jan Pals

Released: December 1, 2024

Tables

CloudAppEvents

Keywords

CloudAppEventsActionTypeWorkloadUserIdResultStatusAffectedItemsJsonParentFolderPathCalendarAgenda

Operators

whereextendparse_jsonextracttostringhas_anysummarizecountbytoprenderwith

Actions

GitHub

KQL Search