KQL Search

Query Details

HomeAI ToolsDevice Query

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 desc

Explanation

This query is analyzing usage data over the past 30 days to provide insights into data consumption and costs. Here's a simple breakdown:

  1. Filter Data: It looks at records from the last 30 days that are billable and have a data type ending with "_CL".

  2. 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.

  3. 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.

  4. 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 profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

Usagewhere>ago==endswithsummarizeroundsumavgdcountbinmaxbyextend/*iff<projectorder bydesc

Actions