KQL Search

Query Details

HomeAI ToolsDevice Query

Cost Projection

Query

let _AuxiliaryTables = dynamic([
    "OfficeActivity", "AzureActivity", "Heartbeat",
    "SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _AnalyticsIngestionRate = 2.76;
let _AnalyticsRetentionRate = 0.12;
let _AuxiliaryIngestionRate = 0.76;
let _AuxiliaryRetentionRate = 0.02;
let _CurrentRetentionDays = 90;
let _ExtendedRetentionMonths = 0;
Usage
| where TimeGenerated > ago(30d)
| summarize 
    MonthlyMB = round(sum(Quantity), 2),
    IsBillable = max(IsBillable)
    by DataType
| extend 
    MonthlyGB = round(MonthlyMB / 1024.0, 2),
    TablePlan = iff(DataType in (_AuxiliaryTables), "Auxiliary", "Analytics")
| extend
    MonthlyIngestionCost = round(
        iff(TablePlan == "Analytics",
            MonthlyGB * _AnalyticsIngestionRate,
            MonthlyGB * _AuxiliaryIngestionRate
        ), 2),
    MonthlyRetentionCost = round(
        iff(_ExtendedRetentionMonths > 0,
            iff(TablePlan == "Analytics",
                MonthlyGB * _CurrentRetentionDays / 30.0 * _AnalyticsRetentionRate * _ExtendedRetentionMonths,
                MonthlyGB * _CurrentRetentionDays / 30.0 * _AuxiliaryRetentionRate * _ExtendedRetentionMonths
            ),
            0.0
        ), 2),
    StoredGB = round(MonthlyGB * _CurrentRetentionDays / 30.0, 2)
| extend TotalMonthlyCost = MonthlyIngestionCost + MonthlyRetentionCost
| summarize 
    TotalIngestionGB = round(sum(MonthlyGB), 2),
    TotalStoredGB = round(sum(StoredGB), 2),
    TotalIngestionCost = round(sum(MonthlyIngestionCost), 2),
    TotalRetentionCost = round(sum(MonthlyRetentionCost), 2),
    TotalMonthlyCost = round(sum(TotalMonthlyCost), 2),
    AnalyticsTables = dcountif(DataType, TablePlan == "Analytics"),
    AuxiliaryTables = dcountif(DataType, TablePlan == "Auxiliary")
    by TablePlan
| extend AnnualProjection = round(TotalMonthlyCost * 12, 2)
| project 
    TablePlan, 
    Tables = iff(TablePlan == "Analytics", AnalyticsTables, AuxiliaryTables),
    TotalIngestionGB, TotalStoredGB,
    TotalIngestionCost, TotalRetentionCost, TotalMonthlyCost,
    AnnualProjection

Explanation

This query is analyzing data usage and costs over the past 30 days, focusing on two types of data tables: "Analytics" and "Auxiliary." Here's a simple breakdown of what the query does:

  1. Define Variables and Constants:

    • Lists specific tables as "Auxiliary."
    • Sets ingestion and retention rates for both "Analytics" and "Auxiliary" data.
    • Specifies the current retention period and any extended retention months.

  2. Filter and Summarize Data:

    • Filters data from the last 30 days.
    • Calculates the total data usage in megabytes (MB) and gigabytes (GB) for each data type.
    • Determines whether the data is billable.

  3. Calculate Costs:

    • Classifies each data type as either "Analytics" or "Auxiliary."
    • Computes the monthly ingestion cost based on the data type and its respective rate.
    • Calculates the monthly retention cost, considering any extended retention period.
    • Computes the total monthly cost by adding ingestion and retention costs.

  4. Summarize Results by Table Plan:

    • Aggregates total ingestion and stored data in GB.
    • Sums up the total costs for ingestion and retention.
    • Counts the number of unique data types for each table plan.
    • Projects the annual cost based on the monthly total.

  5. Output:

    • Displays the results, including the type of table plan, number of tables, total data usage, costs, and projected annual costs.

In essence, the query provides a detailed analysis of data usage and associated costs, distinguishing between "Analytics" and "Auxiliary" data, and offers a projection of annual expenses based on current usage patterns.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

letdynamicwhereagosummarizeroundsummaxbyextendiffinprojectdcountif

Actions