LLI Wiki — My Digital Knowledge Base

KQL: ingestion and usage statistics in Log Analytics

KQL queries to measure data ingested into Log Analytics, notably Azure SQL audit logs.

This guide collects KQL queries to analyse how much data is ingested into a Log Analytics workspace, in particular Azure SQL audit logs sent to the AzureDiagnostics table. They help find what drives the bill.

Ingestion by database

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where database_name_s == "AppDb"
| summarize ingestedGB=sum(_BilledSize) / 1.E9, countLogs=count() by Type, bin(TimeGenerated,1d)
| render columnchart

Usage per solution and data type

The Usage table reports billable volumes. Quantity is expressed in MB, hence the division by 1024 to get GB.

Usage
| where TimeGenerated > startofday(ago(20d))
| where IsBillable == true
| summarize IngestedGB = sum(Quantity) / 1024 by Solution, bin(TimeGenerated, 1d)
| sort by IngestedGB desc
| render columnchart
Usage
| where TimeGenerated > startofday(ago(7d))
| where IsBillable == true and Solution == 'LogManagement'
| summarize IngestedGB = sum(Quantity) / 1024 by DataType
| sort by IngestedGB desc
| render piechart

Summary queries on SQL audit

Per category:

AzureDiagnostics
| where TimeGenerated > startofday(ago(5d))
//| where Category == 'SQLSecurityAuditEvent'
| summarize ingestedGb = sum(_BilledSize) / 1024 / 1024 / 1024, cnt = count() by Category, bin (TimeGenerated,1d)

Per resource group:

AzureDiagnostics
| where TimeGenerated > startofday(ago(5d))
| where Category == 'SQLSecurityAuditEvents'
| summarize ingestedGb = sum(_BilledSize) / 1024 / 1024 / 1024, cnt = count() by ResourceGroup, bin (TimeGenerated,1d)

Per logical server:

AzureDiagnostics
| where TimeGenerated > startofday(ago(5d))
| where Category == 'SQLSecurityAuditEvents' //and ResourceGroup == 'my-resource-group'
| summarize ingestedGb = sum(_BilledSize) / 1024 / 1024 / 1024, cnt = count() by LogicalServerName_s, bin (TimeGenerated,1d)

Per database of one server:

AzureDiagnostics
| where TimeGenerated > startofday(ago(5d))
| where Category == 'SQLSecurityAuditEvents' and LogicalServerName_s == 'sqlserver01'
| summarize ingestedGb = sum(_BilledSize) / 1024 / 1024 / 1024, cnt = count() by database_name_s, bin (TimeGenerated,1d)

Separating real queries from noise

Auditing also records the REVERT; and EXECUTE AS USER = ... statements issued by application connections. This query splits those events from real queries:

AzureDiagnostics
| where TimeGenerated > startofday(ago(5d))
| where Category == 'SQLSecurityAuditEvents' and LogicalServerName_s == 'sqlserver01'
| extend emptystatement = iif(statement_s == 'REVERT;' or statement_s has 'EXECUTE AS USER = ', strcat(action_name_s, ' not real query'), strcat(action_name_s,' real query'))
| summarize ingestedGb = sum(_BilledSize) / 1024 / 1024 / 1024 by emptystatement, bin (TimeGenerated,1d)

Notes

Mastodon LLIMastodon SocialFosstodon