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
_BilledSizeis in bytes;Usage.Quantityis in MB.- Database, server and resource group names above are examples to replace.