Ingestion Delays

# Ingestion Delays ## Query Information #### Description This query can be used to calculate ingestion delays of the unified security platform. In this specific case the GraphAPIAuditEvents and MicrosoftGraphActivityLogs are compared, but these tablenames can be changed to any other table. For all EDR logs you can for example use *union withsource=TableName Device* * to filter on all tables starting with Device. #### Risk Ingestion delays should be taken into account when creating detections, these delays can cause gaps in your detections if not handled properly. #### References - https://kqlquery.com/posts/graphapiauditevents/ ## Defender XDR ```KQL union withsource=TableName GraphAPIAuditEvents, MicrosoftGraphActivityLogs | extend IngestionTime = ingestion_time() | extend IngestionDelay = datetime_diff('minute', IngestionTime, Timestamp) | summarize Average = round(avg(IngestionDelay), 1), percentiles(IngestionDelay, 50, 75, 90, 95, 97, 99) by TableName ``` ## Sentinel ```KQL union withsource=TableName GraphAPIAuditEvents, MicrosoftGraphActivityLogs | extend IngestionTime = ingestion_time() | extend IngestionDelay = datetime_diff('minute', IngestionTime, TimeGenerated) | summarize Average = round(avg(IngestionDelay), 1), percentiles(IngestionDelay, 50, 75, 90, 95, 97, 99) by TableName ```

Explanation

This query is designed to measure the delays in data ingestion for a unified security platform. It specifically compares two tables, GraphAPIAuditEvents and MicrosoftGraphActivityLogs, but can be adapted to other tables as needed.

Here's a simple breakdown of what the query does:

Combines Data: It combines data from the specified tables (GraphAPIAuditEvents and MicrosoftGraphActivityLogs) into a single dataset, while also tagging each entry with its source table name.
Calculates Ingestion Time: For each log entry, it calculates the time when the data was ingested into the system.
Computes Ingestion Delay: It calculates the delay in minutes between when the data was generated (or logged) and when it was ingested into the system.
Summarizes Delays: It then summarizes these delays by calculating the average delay and various percentiles (50th, 75th, 90th, 95th, 97th, and 99th) for each table. This helps in understanding the distribution and extent of ingestion delays.
Risk Consideration: The query highlights the importance of considering ingestion delays when creating detection rules, as delays can lead to gaps in detection if not properly managed.

This query is useful for monitoring and optimizing the performance of data ingestion processes in security platforms, ensuring timely and accurate threat detection.

KQL Search

Query

Explanation

Details

Actions