KQL Search

Query Details

HomeAI ToolsDevice Query

Table Growth Week Over Week

Query

let _AuxiliaryTables = dynamic([
    "OfficeActivity", "AzureActivity", "Heartbeat",
    "SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _ThisWeek =
    Usage
    | where TimeGenerated > ago(7d)
    | where IsBillable == true
    | where DataType !in (_AuxiliaryTables)
    | summarize ThisWeekMB = sum(Quantity) by DataType;
let _LastWeek =
    Usage
    | where TimeGenerated between (ago(14d) .. ago(7d))
    | where IsBillable == true
    | where DataType !in (_AuxiliaryTables)
    | summarize LastWeekMB = sum(Quantity) by DataType;
_ThisWeek
| join kind=leftouter _LastWeek on DataType
| extend 
    LastWeekMB = coalesce(LastWeekMB, 0.0),
    GrowthPct = iff(LastWeekMB > 0, round((ThisWeekMB - LastWeekMB) / LastWeekMB * 100, 1), 100.0),
    DeltaMB = round(ThisWeekMB - coalesce(LastWeekMB, 0.0), 1),
    DeltaGB = round((ThisWeekMB - coalesce(LastWeekMB, 0.0)) / 1024.0, 3),
    GrowthCategory = case(
        LastWeekMB == 0, "NEW",
        (ThisWeekMB - LastWeekMB) / LastWeekMB > 0.5, "RAPID GROWTH (>50%)",
        (ThisWeekMB - LastWeekMB) / LastWeekMB > 0.2, "GROWING (>20%)",
        (ThisWeekMB - LastWeekMB) / LastWeekMB < -0.5, "MAJOR DROP (>50%)",
        (ThisWeekMB - LastWeekMB) / LastWeekMB < -0.2, "DECLINING (>20%)",
        "STABLE"
    )
| project 
    DataType, 
    ThisWeekGB = round(ThisWeekMB / 1024.0, 2),
    LastWeekGB = round(LastWeekMB / 1024.0, 2),
    DeltaGB, GrowthPct, GrowthCategory
| order by GrowthPct desc

Explanation

This query analyzes data usage over the past two weeks, excluding certain auxiliary tables. Here's a simple breakdown:

  1. Define Auxiliary Tables: A list of tables (OfficeActivity, AzureActivity, etc.) that should be excluded from the analysis.

  2. Calculate This Week's Usage:

    • Look at data from the last 7 days.
    • Only consider billable data.
    • Exclude data from the auxiliary tables.
    • Sum the data usage (in megabytes) for each data type.

  3. Calculate Last Week's Usage:

    • Look at data from 7 to 14 days ago.
    • Apply the same filters as for this week's data.
    • Sum the data usage (in megabytes) for each data type.

  4. Join and Compare:

    • Combine this week's and last week's data by data type.
    • Calculate the growth percentage, absolute change in megabytes and gigabytes, and categorize the growth:
      • "NEW" if there was no usage last week.
      • "RAPID GROWTH (>50%)" if usage increased by more than 50%.
      • "GROWING (>20%)" if usage increased by more than 20%.
      • "MAJOR DROP (>50%)" if usage decreased by more than 50%.
      • "DECLINING (>20%)" if usage decreased by more than 20%.
      • "STABLE" for other cases.

  5. Output:

    • Display the data type, usage in gigabytes for this and last week, change in gigabytes, growth percentage, and growth category.
    • Sort the results by growth percentage in descending order.

This helps identify trends in data usage, highlighting significant increases or decreases.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

letdynamicagobetweensummarizesumjoinkind=leftouteronextendcoalesceiffroundcaseprojectorder by

Actions