Query Details
Daily Budget Breach
Query
let _AuxiliaryTables = dynamic([
"OfficeActivity", "AzureActivity", "Heartbeat",
"SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _DailyBudgetGB = 15.0;
let _CostPerGB = 2.76;
Usage
| where TimeGenerated > ago(1d)
| where IsBillable == true
| where DataType !in (_AuxiliaryTables)
| summarize TotalMB = sum(Quantity) by DataType
| extend TotalGB = TotalMB / 1024.0
| summarize
DailyTotalGB = round(sum(TotalGB), 2),
TopTable = arg_max(TotalGB, DataType),
TableCount = dcount(DataType)
| where DailyTotalGB > _DailyBudgetGB
| extend
OverageGB = round(DailyTotalGB - _DailyBudgetGB, 2),
OverageCostUSD = round((DailyTotalGB - _DailyBudgetGB) * _CostPerGB, 2),
DailyCostUSD = round(DailyTotalGB * _CostPerGB, 2),
BudgetUtilizationPct = round(DailyTotalGB / _DailyBudgetGB * 100, 1)
| project
TimeGenerated = now(),
DailyTotalGB, DailyCostUSD,
BudgetGB = _DailyBudgetGB,
OverageGB, OverageCostUSD, BudgetUtilizationPct,
TopContributor = DataType,
ActiveTables = TableCount
Explanation
This KQL query is designed to analyze data usage over the past day and calculate costs associated with that usage. Here's a simplified breakdown of what it does:
-
Define Auxiliary Tables: It starts by defining a list of auxiliary tables that should be excluded from the analysis.
-
Set Budget and Cost Parameters: It sets a daily data usage budget of 15 GB and a cost of $2.76 per GB.
-
Filter Usage Data: The query filters the data to include only records from the last day (
ago(1d)) that are billable and not part of the auxiliary tables. -
Calculate Total Usage: It calculates the total data usage in megabytes for each data type and converts it to gigabytes.
-
Summarize Usage: The query summarizes the total daily usage in gigabytes, identifies the data type with the highest usage, and counts the number of different data types used.
-
Check Against Budget: It checks if the total daily usage exceeds the budget of 15 GB.
-
Calculate Costs and Utilization: If the budget is exceeded, it calculates the overage in gigabytes, the cost of the overage, the total daily cost, and the percentage of the budget used.
-
Project Results: Finally, it outputs the current time, total daily usage, daily cost, budget, overage details, budget utilization percentage, the top data type contributing to usage, and the count of active tables.
In essence, this query helps monitor and manage data usage costs by comparing actual usage against a predefined budget and identifying the main contributors to data usage.
Details
David Alonso
Released: April 8, 2026
Tables
Keywords
Operators