KQL Search

Query Details

Multiple Delayed Event Ingestion

Query

let query_frequency = 1h;
let query_period = 1d;
let percentile_threshold = 90;
let _ExpectedDelays =
    _GetWatchlist("DataType-IngestedTables")
    | project Type, Critical, ExpectedIngestionDelay = totimespan(IngestionDelay), DelayType, Notes
;
union withsource=_Type
    //This is a comment
    * // withsource= is just used to bypass the Analytics rule wizard
| where TimeGenerated > ago(query_period)
| where ingestion_time() between (ago(query_frequency) .. now())
| summarize IngestionDelay = percentile(ingestion_time() - TimeGenerated, percentile_threshold) by Type
| lookup kind=leftouter _ExpectedDelays on Type
| where not(DelayType == "offline" and Notes has "[LowVolume]")
| where IngestionDelay > ExpectedIngestionDelay * iff(DelayType == "offline", 2, 1)
| extend
    AlertSeverity = case(
    Critical == "true", "High",
    "Informational"
    )
| project Type, Critical, IngestionDelay, ExpectedIngestionDelay, AlertSeverity

Explanation

This KQL query is designed to monitor data ingestion delays for different data types and generate alerts based on certain conditions. Here's a simplified explanation of what the query does:

  1. Setup Parameters:

    • query_frequency is set to 1 hour, meaning the query looks at data ingested in the last hour.
    • query_period is set to 1 day, meaning the query considers data generated in the last day.
    • percentile_threshold is set to 90, which is used to calculate the 90th percentile of ingestion delays.

  2. Retrieve Expected Delays:

    • It fetches a watchlist named "DataType-IngestedTables" to get expected ingestion delays for different data types, along with their criticality and notes.

  3. Data Union:

    • It combines data from all tables and filters it to only include records generated in the last day and ingested in the last hour.

  4. Calculate Ingestion Delay:

    • It calculates the 90th percentile of the ingestion delay (time difference between when data was ingested and when it was generated) for each data type.

  5. Join with Expected Delays:

    • It performs a left outer join with the expected delays data to compare actual delays with expected ones.

  6. Filter Conditions:

    • It excludes data types marked as "offline" with low volume.
    • It checks if the actual ingestion delay exceeds the expected delay. If the data type is "offline," it allows for double the expected delay.

  7. Determine Alert Severity:

    • It assigns an alert severity of "High" if the data type is marked as critical; otherwise, it assigns "Informational."

  8. Output:

    • Finally, it outputs the data type, its criticality, the actual and expected ingestion delays, and the alert severity.

In summary, this query identifies data types with significant ingestion delays compared to expected values and categorizes them based on their criticality to generate alerts.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: October 31, 2024

Tables

_GetWatchlist

Keywords

DataTypeIngestedTablesTimeGeneratedIngestionDelayAlertSeverity

Operators

letunionwithsource*|where>agobetween..nowsummarizepercentilebylookupkindonnot==andhasiffextendcaseproject

Actions

GitHub