KQL Search

Query Details

Threat Intel Indicators Stopped Event Reception Threat Intel Indicators

Query

let query_frequency = 1h;
let query_period = 3d;
let _ExpectedFrequencies =
    _GetWatchlist("DataType-IngestedTables")
    | where Type == "ThreatIntelIndicators"
    | project Type, Critical, SourceSystem = Auxiliar, ExpectedIngestionFrequency = totimespan(Frequency)
;
ThreatIntelIndicators
| where TimeGenerated > ago(query_period)
| summarize IngestionTime = max(ingestion_time()) by Type, SourceSystem
| lookup kind=inner _ExpectedFrequencies on Type, SourceSystem
| where IngestionTime between (ago(ExpectedIngestionFrequency + query_frequency) .. ago(ExpectedIngestionFrequency))
| extend
    TimespanWithoutIngestion = now() - IngestionTime,
    AlertSeverity = case(
        Critical == "true", "High",
        "Informational"
        )
| project
    Type,
    SourceSystem,
    Critical,
    ExpectedIngestionFrequency,
    TimespanWithoutIngestion,
    AlertSeverity

Explanation

This KQL query is designed to monitor the ingestion of threat intelligence indicators and identify any delays in their expected ingestion frequency. Here's a simplified explanation of what the query does:

  1. Define Parameters:

    • query_frequency is set to 1 hour, which is used to define a buffer period for checking ingestion times.
    • query_period is set to 3 days, which specifies the time range for checking data.

  2. Retrieve Expected Frequencies:

    • The query retrieves a watchlist named "DataType-IngestedTables" to get expected ingestion frequencies for data types labeled as "ThreatIntelIndicators".
    • It filters the watchlist to only include entries where the Type is "ThreatIntelIndicators".
    • It projects relevant columns: Type, Critical, SourceSystem, and converts Frequency to a timespan for ExpectedIngestionFrequency.

  3. Check Ingestion Times:

    • It queries the ThreatIntelIndicators table for entries generated within the last 3 days.
    • It summarizes the latest ingestion time for each combination of Type and SourceSystem.

  4. Compare with Expected Frequencies:

    • It performs an inner join with the expected frequencies data to match each entry by Type and SourceSystem.
    • It filters the results to find entries where the latest ingestion time falls within a specific range, indicating a delay.

  5. Calculate Delay and Severity:

    • It calculates the timespan since the last ingestion (TimespanWithoutIngestion).
    • It assigns an alert severity based on whether the entry is marked as Critical.

  6. Output:

    • The query projects the final results, showing the Type, SourceSystem, Critical status, ExpectedIngestionFrequency, TimespanWithoutIngestion, and AlertSeverity.

In summary, this query helps identify threat intelligence indicators that are not being ingested as frequently as expected, highlighting potential issues with data ingestion processes.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: July 9, 2025

Tables

ThreatIntelIndicators

Keywords

ThreatIntelIndicators

Operators

let=|where>agosummarizebylookupkind=inneronbetween..extend-nowcase==project

Actions

GitHub