KQL Search

Query Details

HomeAI ToolsDevice Query

Top10 Noisiest Tables Trend

Query

let _AuxiliaryTables = dynamic([
    "OfficeActivity", "AzureActivity", "Heartbeat",
    "SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _Top10 = 
    Usage
    | where TimeGenerated > ago(30d)
    | where IsBillable == true
    | where DataType !in (_AuxiliaryTables)
    | summarize TotalMB = sum(Quantity) by DataType
    | top 10 by TotalMB desc
    | project DataType;
Usage
| where TimeGenerated > ago(30d)
| where IsBillable == true
| where DataType in (_Top10)
| summarize DailyGB = round(sum(Quantity) / 1024.0, 3) by Day = bin(TimeGenerated, 1d), DataType
| order by Day asc, DailyGB desc

Explanation

This KQL (Kusto Query Language) query is designed to analyze data usage over the past 30 days. Here's a simple breakdown of what it does:

  1. Define Auxiliary Tables: It starts by defining a list of auxiliary table names that are not of interest for the main analysis. These tables include "OfficeActivity," "AzureActivity," "Heartbeat," "SentinelHealth," "SecurityAlert," "SecurityIncident," and "Operation."

  2. Identify Top 10 Data Types:

    • It queries the Usage table to find records from the last 30 days (TimeGenerated > ago(30d)) that are billable (IsBillable == true).
    • It excludes data types that are in the auxiliary tables list.
    • It calculates the total megabytes (MB) used for each data type.
    • It selects the top 10 data types with the highest total MB usage.

  3. Calculate Daily Usage for Top Data Types:

    • It again queries the Usage table for the last 30 days of billable data.
    • This time, it focuses only on the top 10 data types identified earlier.
    • It calculates the daily usage in gigabytes (GB) for each of these data types by summing up the quantities and converting them from MB to GB.
    • The results are rounded to three decimal places.
    • Finally, it orders the results by day (ascending) and by daily usage (descending).

In summary, this query identifies the top 10 data types with the highest usage over the past 30 days and then calculates and orders their daily usage in gigabytes.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

letdynamicinagowheresummarizesumbytopdescprojectround/binorder byasc

Actions