Microsoft Defender for Endpoint - Aggregated reporting
MDE Aggregated Reporting
Query
union DeviceFileEvents, DeviceLogonEvents, DeviceNetworkEvents, DeviceProcessEvents
| where ActionType contains "Aggregate"
| summarize count() by ActionType
union DeviceFileEvents, DeviceLogonEvents, DeviceNetworkEvents, DeviceProcessEvents
| where ActionType contains "Aggregate"
| summarize count() by ActionType
DeviceFileEvents
| where ActionType == @"FileCreatedAggregatedReport"
//| distinct FolderPath
| where ActionType == @"FileRenamedAggregatedReport"
| where ActionType == @"FileModifiedAggregatedReport"
DeviceLogonEvents
| where ActionType == @"LogonSuccessAggregatedReport"
DeviceProcessEvents
| where ActionType == @"ProcessCreatedAggregatedReport"
DeviceNetworkEvents
| where ActionType == @"ConnectionFailedAggregatedReport"
| where ActionType == @"ConnectionSuccessAggregatedReport"About this query
Explanation
This KQL query is designed to analyze and report on aggregated events from Microsoft Defender for Endpoint over the past 90 days. Here's a simplified breakdown of what the query does:
-
Data Collection: It combines data from four different event types: DeviceFileEvents, DeviceLogonEvents, DeviceNetworkEvents, and DeviceProcessEvents.
-
Filter for Aggregated Events: It specifically looks for events where the
ActionTypecontains the word "Aggregate" to focus on aggregated reports. -
Count Aggregated Events: It counts the number of each type of aggregated event.
-
Calculate Data Volume:
- Aggregated Events Volume: It calculates the total data volume (in gigabytes) of aggregated events that are billable, grouped by device and day.
- Non-Aggregated Events Volume: Similarly, it calculates the total data volume of non-aggregated events that are billable.
-
Join and Compare: It joins the results of aggregated and non-aggregated event volumes by device name to compare them.
-
Percentage Calculation: It calculates the percentage of data volume that comes from aggregated events compared to the total data volume for each device.
-
Output: The final output provides a list of devices with their respective volumes of aggregated and non-aggregated events, as well as the percentage of data that is aggregated.
This query helps in understanding the distribution and impact of aggregated event data on billing and storage, providing insights into how much of the data is being aggregated versus non-aggregated for each device.
Details
Alex Verboon
Released: September 17, 2025
Tables
Keywords
Operators