Sentinel Usage - Defender for Endpoint Log Ingestion

MDE Usage Latest

Query

let xagotime = 32d;
let xstarttime = 31d;
// File Events
let xDeviceFileEvents  = Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceFileEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceFileEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
  | extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// Process Events
let xDeviceProcessEvents = Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceProcessEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceProcessEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// logon events
let xDeviceLogonEvents =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceLogonEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceLogonEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
 // Registry Events
let xDeviceRegistryEvents =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceRegistryEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceRegistryEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
  // Network Events
 let xDeviceNetworkEvents =   Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceNetworkEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceNetworkEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// Device Network Info
let xDeviceNetworkInfo =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceNetworkInfo"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceNetworkInfo
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// Device Info 
let xDeviceInfo =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceInfo"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceInfo
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// Image load events
let xDeviceImageLoadEvents =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceImageLoadEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceImageLoadEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// Device Events
let xDeviceevents =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceEvents"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceEvents
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// File Cert Info
let xDeviceCertInfo =  Usage 
| where TimeGenerated > ago(xagotime)
| where Solution contains "SecurityInsights"
| where DataType == "DeviceFileCertificateInfo"
| where StartTime >= startofday(ago(xstarttime)) and EndTime < startofday(now())
| summarize BillableDataGB = sum(Quantity) / 1000. by bin(StartTime, 1d), DataType 
| join (DeviceFileCertificateInfo
| where TimeGenerated > ago(xagotime)
| summarize TotalDevices = dcount(DeviceId) by bin(TimeGenerated,1d))
 on $left.StartTime == $right.TimeGenerated 
| extend AverageMBClient = BillableDataGB / TotalDevices * 1000;
// bring all together
union xDeviceCertInfo, xDeviceevents, xDeviceImageLoadEvents, xDeviceInfo, xDeviceLogonEvents, xDeviceNetworkEvents, xDeviceNetworkInfo, xDeviceProcessEvents, xDeviceRegistryEvents, xDeviceFileEvents
// calculate avg per device per data type per day
| summarize AvgMBClient = sum(AverageMBClient) by bin(TimeGenerated,1d) , DataType
| render columnchart

About this query

Sentinel Usage - Defender for Endpoint Log Ingestion

Query Information

Description

Ever wondered how much MB clients ingest on average per day?

The below query attempts to get the avg Size in MB per client that is send from Microosoft Defender for Endpoint to Azure Sentinel when using the Microsoft Defender XDR connector The calculation is done as following:

Collect the Usage data for the specified table from the Usage table, for example DeviceFileEvents.
Collect the total # of devices that submitted information into the specified table, for example 'DeviceFileEvents"
Divide the total BillableDataGB per DataType by the total number of devices that send data to get the avg MB send by client
finally 'uniion' all tables

References

Explanation

This query is designed to calculate the average amount of data, in megabytes (MB), that each client sends to Azure Sentinel from Microsoft Defender for Endpoint on a daily basis. Here's a simplified breakdown of what the query does:

Data Collection: It gathers usage data from various event tables (like DeviceFileEvents, DeviceProcessEvents, etc.) over the past 32 days.
Data Filtering: It filters the data to include only those entries related to the "SecurityInsights" solution and within a specific time range (from 31 days ago to today).
Data Calculation:
- For each type of event data (e.g., file events, process events), it calculates the total billable data in gigabytes (GB) and converts it to MB.
- It counts the number of unique devices that have sent data for each event type.
Average Calculation: It computes the average data size sent by each device by dividing the total data size by the number of devices and multiplying by 1000 to convert GB to MB.
Data Aggregation: It combines the results from all event types into a single dataset.
Final Output: It summarizes the average MB per client per data type per day and displays the results in a column chart for easy visualization.

In essence, this query helps you understand the data ingestion patterns of different event types from Microsoft Defender for Endpoint into Azure Sentinel, providing insights into how much data each client contributes on average.

Details

Alex Verboon

Released: August 22, 2025

Tables

UsageDeviceFileEventsDeviceProcessEventsDeviceLogonEventsDeviceRegistryEventsDeviceNetworkEventsDeviceNetworkInfoDeviceInfoDeviceImageLoadEventsDeviceEventsDeviceFileCertificateInfo

Keywords

SentinelDefenderEndpointDevicesUsageSecurityInsightsDataTypeDeviceFileEventsDeviceProcessEventsDeviceLogonEventsDeviceRegistryEventsDeviceNetworkEventsDeviceNetworkInfoDeviceInfoDeviceImageLoadEventsDeviceEventsDeviceFileCertificateInfo

Operators

letwherecontainsagostartofdaynowsummarizesumbybinjoinonextenduniondcountrender

Actions

GitHub

KQL Search