Multiple Ingestion Delays
Query
let query_period = 7d;
union *
| where TimeGenerated > ago(query_period)
| distinct Type, bin(IngestionTime = ingestion_time(), 30m)
| sort by Type asc, IngestionTime asc
| extend Difference = iff(Type == prev(Type), IngestionTime - prev(IngestionTime), 0s)
| summarize Frequency = max(Difference) by Type
| extend Frequency = iff(Frequency == 0s, query_period, Frequency)
| lookup kind=leftouter (
union *
| where TimeGenerated > ago(query_period)
| summarize percentiles(IngestionDelay = ingestion_time() - TimeGenerated, 50, 80, 95, 99) by Type
) on TypeExplanation
This query is retrieving data from a specified time period and performing various calculations on it. It is then grouping the data by Type and calculating the maximum difference in IngestionTime for each Type. If the maximum difference is 0 seconds, it is replaced with the query period. Finally, it is performing a left outer join with another dataset to retrieve percentiles of IngestionDelay for each Type.
Details
Jose Sebastián Canós
Released: September 27, 2022
Tables
union
Keywords
DevicesIntuneUser
Operators
unionwheredistinctbinsort byextendiffprevsummarizemaxlookupkindleftouter