In the dynamic landscape of cybersecurity, staying ahead of potential threats requires a keen understanding of the data generated by security tools. Microsoft Defender for Endpoint stands as a stalwart guardian, offering robust protection against evolving risks. To harness the full potential of Defender for Endpoint, we turn our attention to the power of the Kusto Query Language (KQL). In this blog post, we embark on a journey through a comprehensive KQL script meticulously crafted for hunting and analyzing data within Defender for Endpoint. This script serves as a beacon, illuminating the path to unlocking valuable insights into daily activities and data sizes across diverse tables. Join us as we dissect each section of this script, revealing the intricacies that make it an indispensable asset in the arsenal of cybersecurity professionals.

Complete KQL Script

union withsource = TableName

DeviceEvents, DeviceFileEvents, DeviceFileCertificateInfo, DeviceImageLoadEvents, DeviceInfo, DeviceLogonEvents, DeviceNetworkEvents,

DeviceNetworkInfo, DeviceProcessEvents, DeviceRegistryEvents, EmailAttachmentInfo, EmailEvents, EmailPostDeliveryEvents, EmailUrlInfo, UrlClickEvents, AlertEvidence, AlertInfo,

IdentityLogonEvents, IdentityQueryEvents, IdentityDirectoryEvents, EmailEvents, EmailAttachmentInfo, EmailPostDeliveryEvents



| where Timestamp >= ago(1d)


| summarize total = count(), DailyBytes = sum(estimate_data_size(*)) by TableName, bin(Timestamp, 1d)


| summarize DailyAvg = avg(DailyBytes), TotalTableEntries = sum(total), TotalBytes = sum(DailyBytes) by TableName


| extend TableSizeInGB = format_bytes(TotalBytes,4, "GB"),


  DailyAvgInGB = format_bytes(DailyAvg, 4, "GB")


//    TableSizeInMB = format_bytes(TotalBytes, 2, "MB"),


//    DailyAvgInMB = format_bytes(DailyAvg, 2, "MB")


| sort by TotalBytes desc


| project-away TotalBytes, DailyAvg

• Union Operation:
  • In this section, we use the union operator to combine data from multiple tables. The withsource option adds a column named TableName to identify the source table for each record.
• Filtering by Timestamp:
  • This line filters the records based on the Timestamp field, selecting only those that occurred within the last 1 day (ago(1d)).
• Summarization:
  • The summarize statement calculates the total count and daily sum of estimated data size for each table. The results are aggregated by TableName and binned Timestamp into 1-day intervals.
• Further Summarization:
  • Another summarize statement further aggregates the data, calculating the average daily bytes (DailyAvg), total table entries (TotalTableEntries), and total bytes (TotalBytes) for each table.
• Extension with Size Formatting:
  • The extend statement adds new columns to the result, formatting the total bytes and daily average bytes into gigabytes.
• Sorting and Projecting:
  • The sort statement arranges the results in descending order based on total bytes (TotalBytes). The project-away statement removes unnecessary columns (TotalBytes and DailyAvg) from the final output.

Conclusion:

By delving into the intricacies of this KQL script, we’ve uncovered a powerful approach to extracting valuable insights from Defender for Endpoint data. This script not only aggregates and summarizes data but also formats it for easy interpretation. Incorporate this script into your security analysis toolkit to gain a comprehensive understanding of daily activities and data sizes, empowering you to make informed decisions to enhance your organization’s cybersecurity posture.