KQL Search

Query Details

HomeAI ToolsDevice Query

Ingestion Drop Silent Table

Query

let _AuxiliaryTables = dynamic([
    "OfficeActivity", "AzureActivity", "Heartbeat",
    "SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
let _MinDailyMB = 1;
let _ActiveTables =
    Usage
    | where TimeGenerated between (ago(7d) .. ago(1d))
    | where IsBillable == true
    | where DataType !in (_AuxiliaryTables)
    | summarize DailyMB = sum(Quantity) by Day = bin(TimeGenerated, 1d), DataType
    | summarize AvgDailyMB = avg(DailyMB), ActiveDays = count() by DataType
    | where AvgDailyMB > _MinDailyMB and ActiveDays >= 4;
let _TodayTables =
    Usage
    | where TimeGenerated > ago(1d)
    | where IsBillable == true
    | summarize TodayMB = sum(Quantity) by DataType
    | where TodayMB > 0
    | project DataType;
_ActiveTables
| where DataType !in (_TodayTables)
| extend 
    AlertSeverity = iff(AvgDailyMB > 100, "High", iff(AvgDailyMB > 10, "Medium", "Low")),
    EstDailyCostLostUSD = round((AvgDailyMB / 1024.0) * 2.76, 2)
| project 
    TimeGenerated = now(),
    DataType,
    Status = "SILENT - No ingestion in 24h",
    AvgDailyMB = round(AvgDailyMB, 1),
    PreviousActiveDays = ActiveDays,
    AlertSeverity, EstDailyCostLostUSD
| order by AvgDailyMB desc

Explanation

This KQL query is designed to identify data types that were actively ingesting data over the past week but have not ingested any data in the last 24 hours. Here's a simplified breakdown of what the query does:

  1. Define Auxiliary Tables: It starts by defining a list of auxiliary tables that are not considered for this analysis.

  2. Set Minimum Daily Data Threshold: It sets a minimum threshold of 1 MB for average daily data ingestion to consider a data type as active.

  3. Identify Active Tables:

    • It looks at data ingestion over the past week (from 7 days ago to 1 day ago).
    • Filters out non-billable data and auxiliary tables.
    • Calculates the daily data ingestion in MB for each data type.
    • Determines the average daily ingestion and counts the number of active days for each data type.
    • Filters to include only those data types with an average daily ingestion greater than 1 MB and active on at least 4 days.

  4. Identify Today's Active Tables:

    • It checks which data types have ingested data in the last 24 hours and are billable.

  5. Find Silent Data Types:

    • Compares the list of active tables from the past week with today's active tables to find data types that were active but are now silent (no ingestion in the last 24 hours).

  6. Generate Alerts:

    • For each silent data type, it assigns an alert severity based on the average daily ingestion: "High" for over 100 MB, "Medium" for over 10 MB, and "Low" otherwise.
    • Estimates the potential daily cost lost due to the lack of data ingestion, assuming a cost rate of $2.76 per GB.
    • Projects the results with details such as the current time, data type, status, average daily MB, previous active days, alert severity, and estimated daily cost lost.

  7. Order Results:

    • Finally, it orders the results by average daily MB in descending order, highlighting the most significant silent data types first.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

letdynamicbetweenagoinsummarizesumbybinavgcountwhereprojectextendiffroundnoworder bydesc

Actions