Query Details
XDR MITRE ATTCK Pivot
Query
name : MITRE ATTCK pivot table
description : Providing a pivot table for MITRE ATT&CK across Microsoft 365 Defender
- Case1 - Pivot table for MITRE ATT&CK across Microsoft 365 Defender
- Case2 - M365D MITRE ATT&CK list with AlertID
table :
- AlertInfo
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-alertinfo-table?view=o365-worldwide
query: |
//Case1 - Pivot table for MITRE ATT&CK across Microsoft 365 Defender
AlertInfo
| where Timestamp > ago(30d)
| where ServiceSource in ("AAD Identity Protection", "Microsoft Defender for Endpoint", "Microsoft Defender for Office 365", "Microsoft Defender for Identity", "Microsoft Cloud App Security", "Microsoft 365 Defender")
| where isnotempty(AttackTechniques)
| mv-expand DetailedAttackTechniques= parse_json(AttackTechniques)
| project ServiceSource, DetailedAttackTechniques
| evaluate pivot(ServiceSource)
//Case2 - M365D MITRE ATT&CK list with AlertID
AlertInfo
| where Timestamp > ago(30d)
| where ServiceSource in ("AAD Identity Protection", "Microsoft Defender for Endpoint", "Microsoft Defender for Office 365", "Microsoft Defender for Identity", "Microsoft Cloud App Security", "Microsoft 365 Defender")
| where isnotempty(AttackTechniques)
| mv-expand DetailedAttackTechniques= parse_json(AttackTechniques)
| summarize AAD_AlertID = make_set_if(AlertId, ServiceSource == "AAD Identity Protection"),
MDE_AlertID = make_set_if(AlertId, ServiceSource == "Microsoft Defender for Endpoint"),
MDO_AlertID = make_set_if(AlertId, ServiceSource == "Microsoft Defender for Office 365"),
MDI_AlertID = make_set_if(AlertId, ServiceSource == "Microsoft Defender for Identity"),
MDA_AlertID = make_set_if(AlertId, ServiceSource == "Microsoft Cloud App Security"),
M365D_AlertID = make_set_if(AlertId, ServiceSource == "Microsoft Cloud App Security")
by tostring(DetailedAttackTechniques)
| extend AAD_case = array_length(AAD_AlertID)
| extend MDE_case = array_length(MDE_AlertID)
| extend MDO_case = array_length(MDO_AlertID)
| extend MDI_case = array_length(MDI_AlertID)
| extend MDA_case = array_length(MDA_AlertID)
| extend M365D_case = array_length(M365D_AlertID)
| project DetailedAttackTechniques, AAD_case, AAD_AlertID, MDE_case, MDE_AlertID, MDO_case, MDO_AlertID, MDI_case, MDI_AlertID, MDA_case, MDA_AlertID, M365D_case, M365D_AlertID
Explanation
The query is creating a pivot table that shows the MITRE ATT&CK techniques across different Microsoft 365 Defender services.
In Case 1, the query filters the AlertInfo table based on a specific time range and a list of Microsoft 365 Defender services. It then expands the AttackTechniques column, which contains JSON data, and projects the ServiceSource and DetailedAttackTechniques columns. Finally, it evaluates the pivot of the ServiceSource column.
In Case 2, the query performs the same filtering and expansion steps as Case 1. However, it also summarizes the AlertId values based on the ServiceSource column using the make_set_if function. It then extends the results with additional columns that count the number of AlertIds for each service. Finally, it projects the DetailedAttackTechniques and AlertId columns for each service.
Details
Kijo Girardi
Released: June 8, 2023
Tables
Keywords
Operators