Custom Table Cost Analysis
Query
Usage
| where TimeGenerated > ago(30d)
| where IsBillable == true
| where DataType endswith "_CL"
| summarize
TotalMB = round(sum(Quantity), 2),
DailyAvgMB = round(avg(Quantity), 2),
DaysActive = dcount(bin(TimeGenerated, 1d)),
LastIngestion = max(TimeGenerated)
by DataType
| extend
TotalGB = round(TotalMB / 1024.0, 2),
EstMonthlyCostUSD = round((DailyAvgMB / 1024.0) * 30 * 2.76, 2),
IsStale = iff(LastIngestion < ago(7d), true, false)
| project DataType, TotalGB, DailyAvgMB, EstMonthlyCostUSD, DaysActive, LastIngestion, IsStale
| order by TotalGB descExplanation
This query is analyzing usage data over the past 30 days to provide insights into data consumption and costs. Here's a simple breakdown:
-
Filter Data: It looks at records from the last 30 days that are billable and have a data type ending with "_CL".
-
Summarize Data:
- TotalMB: Calculates the total amount of data used, in megabytes, rounded to two decimal places.
- DailyAvgMB: Computes the average daily data usage, also in megabytes and rounded.
- DaysActive: Counts the number of days with activity.
- LastIngestion: Finds the most recent date data was ingested.
-
Extend Data:
- TotalGB: Converts the total data usage from megabytes to gigabytes.
- EstMonthlyCostUSD: Estimates the monthly cost in USD based on the average daily usage, assuming a rate of $2.76 per GB.
- IsStale: Flags data as stale if it hasn't been ingested in the last 7 days.
-
Project and Order:
- Selects specific columns to display: DataType, TotalGB, DailyAvgMB, EstMonthlyCostUSD, DaysActive, LastIngestion, and IsStale.
- Orders the results by TotalGB in descending order, showing the data types with the highest usage first.
Details
David Alonso
Released: April 8, 2026
Tables
Usage
Keywords
Usage
Operators
Usagewhere>ago==endswithsummarizeroundsumavgdcountbinmaxbyextend/*iff<projectorder bydesc