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 ascExplanation
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