Query Details
Per Solution Breakdown
Query
let _AuxiliaryTables = dynamic([
"OfficeActivity", "AzureActivity", "Heartbeat",
"SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
Usage
| where TimeGenerated > ago(30d)
| where IsBillable == true
| where DataType !in (_AuxiliaryTables)
| summarize
TotalGB = round(sum(Quantity) / 1024.0, 2),
TableCount = dcount(DataType),
DailyAvgGB = round(avg(Quantity) / 1024.0, 4)
by Solution
| extend EstMonthlyCostUSD = round(DailyAvgGB * 30 * 2.76, 2)
| order by TotalGB desc
Explanation
This KQL query is designed to analyze data usage over the past 30 days, focusing on billable data types that are not part of a predefined list of auxiliary tables. Here's a breakdown of what the query does:
-
Define Auxiliary Tables: A list of table names (
OfficeActivity,AzureActivity,Heartbeat,SentinelHealth,SecurityAlert,SecurityIncident,Operation) is defined as auxiliary tables, which are not the focus of this analysis. -
Filter Data: The query filters the
Usagetable to include only records from the last 30 days (TimeGenerated > ago(30d)) and ensures that only billable data (IsBillable == true) is considered. It also excludes any data types that are in the auxiliary tables list. -
Summarize Data: For each solution, the query calculates:
TotalGB: The total gigabytes of data used, rounded to two decimal places.TableCount: The distinct count of different data types.DailyAvgGB: The average daily data usage in gigabytes, rounded to four decimal places.
-
Estimate Monthly Cost: It estimates the monthly cost in USD by multiplying the daily average gigabytes by 30 days and a cost rate of $2.76 per gigabyte, rounding the result to two decimal places.
-
Order Results: Finally, the results are ordered by
TotalGBin descending order, showing the solutions with the highest data usage first.
In summary, this query provides insights into the data usage and estimated costs for different solutions, excluding certain auxiliary data types, over the past month.
Details
David Alonso
Released: April 8, 2026
Tables
Keywords
Operators