KQL Search

Query Details

HomeAI ToolsDevice Query

Visualization Hard Deletions By User

Query

# Visualisation of the users with the most HardDelete actions performed

### Defender For Endpoint

```
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')
```
### 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

The query retrieves and visualizes the users who have performed the most "HardDelete" actions in Defender for Endpoint and Sentinel. It filters the CloudAppEvents data based on the ActionType being "HardDelete" and extracts relevant information such as Workload, UserId, ResultStatus, and AffectedItems. It then removes personal deletions of Calendar items and summarizes the count of HardDelete actions performed by each user. The top 50 users with the highest count are displayed in a column chart titled "HardDeletions by User".

Details

Bert-Jan Pals profile picture

Bert-Jan Pals

Released: February 14, 2023

Tables

CloudAppEvents

Keywords

Devices,Intune,User

Operators

CloudAppEventswhere==extendparse_jsonRawEventDataWorkloadUserIdResultStatusAffectedItemsJsonextract1tostringParentFolderPathhas_anywherenotsummarizecount()bytoprendercolumnchartwithtitle

Actions