KQL Search

Query Details

Example Activity Counts Metrics

Query

let query_frequency = 15m;
let query_period = 30m;
let threshold = 5;
...
| where TimeGenerated > ago(query_period)
...
| as _Events
| join kind=leftsemi (
    _Events
    // query_period should be 2 * query_frequency
    // if IdColumn is Type, and Type has only 1 possible value (only 1 table is used), activity_counts_metrics will only be useful to check if a threshold has been surpassed
    | evaluate activity_counts_metrics(Type, TimeGenerated, ago(query_period), now(), query_frequency, ColumnToSummarizeBy1, ColumnToSummarizeBy2, ColumnToSummarizeBy3)
    | summarize
        arg_min(PreviousTimeGenerated = TimeGenerated, PreviousCount = ["count"]),
        arg_max(CurrentTimeGenerated = TimeGenerated, CurrentCount = ["count"])
        by ColumnToSummarizeBy1, ColumnToSummarizeBy2, ColumnToSummarizeBy3
    | where CurrentTimeGenerated > ago(query_period)
    | extend PreviousCount = iff(PreviousTimeGenerated == CurrentTimeGenerated, 0, PreviousCount)
    | where (not(PreviousCount > threshold) and CurrentCount > threshold)
        or ((CurrentCount - PreviousCount) > threshold)
    ) on ColumnToSummarizeBy1, ColumnToSummarizeBy2, ColumnToSummarizeBy3
...

Explanation

This query is designed to monitor and identify significant changes in activity over a specified time period. Here's a simplified breakdown:

  1. Time Parameters:

    • query_frequency is set to 15 minutes, which is the interval at which activity is checked.
    • query_period is set to 30 minutes, which is the total time window being analyzed.
    • threshold is set to 5, which is the minimum change in activity count that is considered significant.

  2. Data Filtering:

    • The query filters events to include only those generated within the last 30 minutes (query_period).

  3. Activity Analysis:

    • The query uses the activity_counts_metrics function to calculate activity counts over time, grouped by three columns (ColumnToSummarizeBy1, ColumnToSummarizeBy2, ColumnToSummarizeBy3).
    • It captures the minimum and maximum counts within the time window to identify changes in activity.

  4. Change Detection:

    • It checks for two conditions:
      • If the previous count was not above the threshold but the current count is.
      • If the difference between the current and previous counts exceeds the threshold.

  5. Result Filtering:

    • The query uses a leftsemi join to filter the original set of events, keeping only those that meet the significant change criteria based on the conditions above.

In essence, this query is used to detect and highlight events where there has been a notable increase in activity within a 30-minute window, using a 15-minute frequency for checking changes.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: July 3, 2024

Tables

_Events

Keywords

TimeGeneratedTypeColumnToSummarizeBy1ColumnToSummarizeBy2ColumnToSummarizeBy3

Operators

letagoasjoinevaluatesummarizearg_minarg_maxbywhereextendiffnoton

Actions

GitHub