Query Details
Hourly Ingestion Heatmap
Query
let _AuxiliaryTables = dynamic([
"OfficeActivity", "AzureActivity", "Heartbeat",
"SentinelHealth", "SecurityAlert", "SecurityIncident", "Operation"
]);
Usage
| where TimeGenerated > ago(7d)
| where IsBillable == true
| where DataType !in (_AuxiliaryTables)
| extend
DayOfWeek = dayofweek(TimeGenerated) / 1d,
HourOfDay = hourofday(TimeGenerated)
| summarize HourlyMB = round(sum(Quantity), 1) by DayOfWeek = toint(DayOfWeek), HourOfDay
| extend
DayName = case(
DayOfWeek == 0, "Sun",
DayOfWeek == 1, "Mon",
DayOfWeek == 2, "Tue",
DayOfWeek == 3, "Wed",
DayOfWeek == 4, "Thu",
DayOfWeek == 5, "Fri",
"Sat"
),
HourlyGB = round(HourlyMB / 1024.0, 3)
| project DayName, DayOfWeek, HourOfDay, HourlyGB, HourlyMB
| order by DayOfWeek asc, HourOfDay asc
Explanation
This KQL (Kusto Query Language) query is analyzing usage data over the past week, focusing on billable data types that are not part of a specified list of auxiliary tables. Here's a simple breakdown of what the query does:
-
Define Auxiliary Tables: It sets up a list of auxiliary tables that are not of interest for this analysis.
-
Filter Data: The query filters the
Usagedata to include only entries from the last 7 days (TimeGenerated > ago(7d)) and ensures that only billable data (IsBillable == true) is considered. It excludes data types that are in the auxiliary tables list. -
Extract Time Information: For each entry, it calculates the day of the week and the hour of the day from the
TimeGeneratedtimestamp. -
Summarize Data: It aggregates the data to calculate the total usage in megabytes (MB) for each hour of each day of the week.
-
Convert and Label Data: The query converts the day of the week into a readable day name (e.g., "Sun" for Sunday) and converts the usage from megabytes to gigabytes (GB), rounding to three decimal places.
-
Select and Order Results: Finally, it selects specific columns to display (
DayName,DayOfWeek,HourOfDay,HourlyGB,HourlyMB) and orders the results by day of the week and hour of the day in ascending order.
In summary, this query provides a breakdown of billable data usage in gigabytes and megabytes for each hour of each day over the past week, excluding certain auxiliary data types.
Details
David Alonso
Released: April 8, 2026
Tables
Keywords
Operators