KQL Search

Query Details

Threat Intelligence Indicator Stopped Event Reception Threat Intelligence Indicator

Query

let query_frequency = 1h;
let query_period = 3d;
let _ExpectedFrequencies =
    _GetWatchlist("DataType-IngestedTables")
    | where Type == "ThreatIntelligenceIndicator"
    | project Type, Critical, SourceSystem = Auxiliar, ExpectedIngestionFrequency = totimespan(Frequency)
;
ThreatIntelligenceIndicator
| 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 simple breakdown of what the query does:

  1. Define Parameters:

    • query_frequency is set to 1 hour, which is used as a buffer for checking ingestion times.
    • query_period is set to 3 days, which is the time window for looking back at data.

  2. Retrieve Expected Frequencies:

    • The query fetches a watchlist named "DataType-IngestedTables" to get expected ingestion frequencies for different types of threat intelligence indicators.
    • It filters for entries where the type is "ThreatIntelligenceIndicator" and extracts relevant fields like Type, Critical, SourceSystem, and ExpectedIngestionFrequency.

  3. Analyze Ingestion Data:

    • It looks at the ThreatIntelligenceIndicator table for records generated within the last 3 days.
    • It summarizes the latest ingestion time for each type and source system.

  4. Compare with Expected Frequencies:

    • The query performs an inner join with the expected frequencies data to match types and source systems.
    • It checks if the latest ingestion time falls within a window that indicates a delay (between the expected frequency plus the query frequency and the expected frequency itself).

  5. Calculate and Classify Delays:

    • It calculates the timespan since the last ingestion.
    • It assigns an alert severity based on whether the indicator is marked as critical: "High" for critical indicators and "Informational" otherwise.

  6. Output Results:

    • The final output includes the type, source system, critical status, expected ingestion frequency, timespan without ingestion, and alert severity for each indicator that has a delayed ingestion.

In summary, this query helps identify threat intelligence indicators that are not being ingested as frequently as expected, potentially highlighting issues in data flow or system performance.