Ingestion Delay
Query
//Identifying ingestion delay between tables
union SigninLogs, AuditLogs
| extend E2EIngestionLatencyMin = todouble(datetime_diff("Second",ingestion_time(),TimeGenerated))/60
| summarize avg(E2EIngestionLatencyMin), min(E2EIngestionLatencyMin), max(E2EIngestionLatencyMin) by bin(TimeGenerated,1h),ingestion_time(), Type
| summarize lessthanOneMin = countif(avg_E2EIngestionLatencyMin < 60), gtrthanOneMin=countif(avg_E2EIngestionLatencyMin > 60) by TypeExplanation
This query combines the SigninLogs and AuditLogs tables and calculates the ingestion delay between them. It calculates the minimum, maximum, and average ingestion latency in minutes. It then groups the results by hour, ingestion time, and type. Finally, it summarizes the number of records with ingestion latency less than one minute and greater than one minute, grouped by type.
Details
Rod Trent
Released: October 13, 2022
Tables
SigninLogsAuditLogs
Keywords
DevicesIntuneUser
Operators
unionextendtodoubledatetime_diffsummarizeavgminmaxbybincountif