KQL Search

Query Details

Common Security Log Stopped Event Reception Common Security Log Device Product

Query

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

Explanation

This query is designed to monitor data ingestion for security logs and identify any potential issues with the frequency of data ingestion. Here's a simplified breakdown of what the query does:

  1. Setup Parameters:

    • query_frequency is set to 1 hour, which is used as a buffer time for checking ingestion.
    • query_period is set to 3 days, which defines the time range for the logs being analyzed.

  2. Retrieve Expected Frequencies:

    • It fetches a watchlist named "DataType-IngestedTables" to get expected ingestion frequencies for different types of security logs.
    • The watchlist is filtered to include only entries with the type "CommonSecurityLog".
    • It expands entries with multiple device products and extracts relevant fields: Type, DeviceProduct, Critical, and ExpectedIngestionFrequency.

  3. Analyze Ingestion Times:

    • It queries the CommonSecurityLog table for logs generated within the last 3 days.
    • For each log type and device product, it calculates the most recent ingestion time.

  4. Compare with Expected Frequencies:

    • It performs an inner join with the expected frequencies data to match logs with their expected ingestion frequencies.
    • It checks if the most recent ingestion time falls within a specific time window (between the expected frequency plus an hour and the expected frequency).

  5. Calculate and Classify Alerts:

    • It calculates how long it has been since the last ingestion (TimespanWithoutIngestion).
    • It assigns an alert severity based on whether the log type is marked as critical: "High" for critical logs and "Informational" otherwise.

  6. Output:

    • The query outputs a list of log types and device products, along with their critical status, expected ingestion frequency, time since last ingestion, and alert severity.

In summary, this query helps identify security logs that are not being ingested as frequently as expected, categorizes them based on their criticality, and provides insights into potential data ingestion issues.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: July 9, 2025

Tables

CommonSecurityLog

Keywords

QueryFrequencyPeriodExpectedFrequenciesWatchlistDataTypeIngestedTablesCommonSecurityLogDeviceProductCriticalIngestionTimeGeneratedAlertSeverity

Operators

letmv-expandsplittotypeofprojectwhereagosummarizemaxingestion_timelookupkind=inneronbetweenextendnowcase

Actions

GitHub