Query Details
Full Ingestion Dashboard
Query
let _AuxiliaryTables = dynamic([
"OfficeActivity", "AzureActivity", "Heartbeat",
"SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _TotalBillableGB = toscalar(
Usage
| where TimeGenerated > ago(30d)
| where IsBillable == true
| where DataType !in (_AuxiliaryTables)
| summarize round(sum(Quantity) / 1024.0, 2)
);
Usage
| where TimeGenerated > ago(30d)
| where IsBillable == true
| where DataType !in (_AuxiliaryTables)
| summarize
DailyAvgMB = round(avg(Quantity), 2),
TotalMB = round(sum(Quantity), 2),
MaxDayMB = round(max(Quantity), 2),
MinDayMB = round(min(Quantity), 2),
DaysActive = dcount(bin(TimeGenerated, 1d))
by DataType, Solution
| extend
TotalGB = round(TotalMB / 1024.0, 2),
DailyAvgGB = round(DailyAvgMB / 1024.0, 4),
SpikeRatio = iff(DailyAvgMB > 0, round(MaxDayMB / DailyAvgMB, 1), 0.0),
EstMonthlyCost = round((DailyAvgMB / 1024.0) * 30 * 2.76, 2),
PctOfTotal = round(TotalMB / 1024.0 / _TotalBillableGB * 100, 1)
| project
DataType, Solution, TotalGB, DailyAvgGB,
MaxDayMB, SpikeRatio, DaysActive,
EstMonthlyCost, PctOfTotal
| order by TotalGB desc
Explanation
This KQL (Kusto Query Language) query is designed to analyze and summarize the usage data from a database, focusing on billable data types over the past 30 days. Here's a simplified breakdown of what the query does:
-
Define Auxiliary Tables: It starts by defining a list of auxiliary tables (
_AuxiliaryTables) that should be excluded from the analysis. -
Calculate Total Billable Gigabytes: It calculates the total amount of billable data (in gigabytes) by summing up the data quantities from the
Usagetable, excluding the auxiliary tables, and considering only the data generated in the last 30 days. -
Filter and Summarize Usage Data: The query filters the
Usagetable to include only billable data from the last 30 days, excluding the auxiliary tables. It then summarizes this data by:- Calculating the daily average, total, maximum, and minimum data usage (in megabytes) for each data type and solution.
- Counting the number of active days for each data type and solution.
-
Extend Calculations: It extends the summarized data with additional calculations:
- Converts total and daily average usage from megabytes to gigabytes.
- Calculates a "Spike Ratio" to show how much the maximum daily usage exceeds the average daily usage.
- Estimates the monthly cost based on the daily average usage.
- Computes the percentage of total billable data that each data type represents.
-
Project and Order Results: Finally, it selects specific columns to display (such as
DataType,Solution,TotalGB, etc.) and orders the results by total gigabytes in descending order.
In essence, this query provides a detailed breakdown of data usage, highlighting the most significant data types and solutions in terms of volume and cost, while excluding non-billable and auxiliary data.
Details
David Alonso
Released: April 8, 2026
Tables
Keywords
Operators