KQL Search

Query Details

Ingestion Spike 3x

Query

let _AuxiliaryTables = dynamic([
    "OfficeActivity", "AzureActivity", "Heartbeat",
    "SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _SpikeMultiplier = 3.0;
let _MinDailyMB = 10;
let _Baseline =
    Usage
    | where TimeGenerated between (ago(15d) .. ago(1d))
    | where IsBillable == true
    | where DataType !in (_AuxiliaryTables)
    | summarize DailyMB = sum(Quantity) by Day = bin(TimeGenerated, 1d), DataType
    | summarize AvgDailyMB = avg(DailyMB) by DataType
    | where AvgDailyMB > _MinDailyMB;
Usage
| where TimeGenerated > ago(1d)
| where IsBillable == true
| where DataType !in (_AuxiliaryTables)
| summarize TodayMB = sum(Quantity) by DataType
| join kind=inner _Baseline on DataType
| where TodayMB > (AvgDailyMB * _SpikeMultiplier)
| extend 
    SpikeMultiplier = round(TodayMB / AvgDailyMB, 1),
    ExcessMB = round(TodayMB - AvgDailyMB, 1),
    ExcessGB = round((TodayMB - AvgDailyMB) / 1024.0, 3),
    EstExcessCostUSD = round(((TodayMB - AvgDailyMB) / 1024.0) * 2.76, 2)
| project 
    TimeGenerated = now(),
    DataType,
    TodayMB = round(TodayMB, 1),
    AvgDailyMB = round(AvgDailyMB, 1),
    SpikeMultiplier, ExcessMB, ExcessGB, EstExcessCostUSD
| order by SpikeMultiplier desc

Explanation

This KQL query is designed to identify unusual spikes in data usage for specific data types over the past day, excluding certain auxiliary tables. Here's a simplified breakdown of what the query does:

  1. Setup and Definitions:

    • It defines a list of auxiliary tables (_AuxiliaryTables) that should be excluded from the analysis.
    • It sets a spike multiplier (_SpikeMultiplier) of 3.0 to identify significant increases in data usage.
    • It sets a minimum daily data usage threshold (_MinDailyMB) of 10 MB to filter out data types with very low usage.

  2. Baseline Calculation:

    • The query calculates a baseline average daily data usage (AvgDailyMB) for each data type over the past 14 days (from 15 days ago to 1 day ago), excluding the auxiliary tables and only considering billable data.
    • It only includes data types with an average daily usage greater than 10 MB.

  3. Current Day Analysis:

    • It calculates the total data usage (TodayMB) for each data type in the last day, again excluding auxiliary tables and only considering billable data.
    • It joins this current day data with the baseline data to compare today's usage against the historical average.

  4. Spike Detection:

    • It identifies data types where today's usage exceeds three times the average daily usage (AvgDailyMB * _SpikeMultiplier).
    • For these data types, it calculates:
      • The spike multiplier (how many times today's usage exceeds the average).
      • The excess data usage in MB and GB.
      • An estimated excess cost in USD, assuming a cost rate of $2.76 per GB.

  5. Output:

    • It projects the results, including the current time, data type, today's usage, average daily usage, spike multiplier, excess usage in MB and GB, and estimated excess cost.
    • The results are ordered by the spike multiplier in descending order, highlighting the most significant spikes.

In summary, this query helps to detect and quantify significant increases in data usage for specific data types over the past day, potentially indicating unusual or unexpected activity.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

letdynamicbetweenagoinsummarizebinavgwhere!insumjoinkind=inneronextendroundprojectnoworder bydesc

Actions

GitHub