KQL Search

Query Details

HomeAI ToolsDevice Query

New Table Detected

Query

let _KnownTables =
    Usage
    | where TimeGenerated between (ago(31d) .. ago(1d))
    | distinct DataType;
Usage
| where TimeGenerated > ago(1d)
| where DataType !in (_KnownTables)
| summarize 
    FirstSeen = min(TimeGenerated),
    TodayMB = round(sum(Quantity), 2),
    IsBillable = max(IsBillable)
    by DataType
| extend 
    TablePlan = iff(IsBillable == true, "Analytics (Billable)", "Auxiliary/Free"),
    EstDailyCostUSD = iff(IsBillable == true, round((TodayMB / 1024.0) * 2.76, 2), 0.0)
| project TimeGenerated = now(), DataType, FirstSeen, TodayMB, TablePlan, EstDailyCostUSD

Explanation

This query is designed to analyze data usage over the past day and identify any new data types that have appeared in the last 24 hours, which were not present in the previous 30 days. Here's a simplified breakdown of what the query does:

  1. Identify Known Data Types:

    • It first creates a list of distinct data types (DataType) from the Usage table that were recorded between 31 days ago and 1 day ago.

  2. Filter Recent Data:

    • It then looks at the Usage table for the last 24 hours (TimeGenerated > ago(1d)) and filters out any data types that are already in the list of known data types.

  3. Summarize New Data Types:

    • For these new data types, it calculates:
      • FirstSeen: The earliest time this data type was recorded in the last 24 hours.
      • TodayMB: The total quantity of data for this type, rounded to two decimal places.
      • IsBillable: Whether the data type is billable or not.

  4. Classify and Estimate Cost:

    • It classifies each data type into a plan:
      • "Analytics (Billable)" if it's billable.
      • "Auxiliary/Free" if it's not billable.
    • It estimates the daily cost in USD for billable data types, using a conversion factor to calculate the cost based on the total megabytes used.

  5. Project Results:

    • Finally, it outputs the current time, data type, first seen time, total megabytes used today, the plan type, and the estimated daily cost in USD.

In essence, this query helps identify and analyze new data types that have started appearing in the last day, providing insights into their usage and potential costs.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

letbetweenagodistinctwhere!insummarizeminroundsummaxbyextendiff==/*projectnow

Actions