LLI Wiki — My Digital Knowledge Base

KQL queries: Azure SQL sessions (AzureMetrics)

KQL queries to track the sessions_count metric of Azure SQL Database in the AzureMetrics table of Log Analytics.

The number of sessions on an Azure SQL database is exposed by the platform metric sessions_count. Once platform metrics are exported to a Log Analytics workspace, they are available in the AzureMetrics table. The queries below help track this metric over time.

Hourly maximum per resource

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where MetricName == "sessions_count"
| summarize max(Maximum) by Resource,bin(TimeGenerated,1h)
| project TimeGenerated,Resource,max_Maximum

Hourly time chart across all databases

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where MetricName == "sessions_count"
| summarize MaxSessions = max(Maximum) by bin(TimeGenerated, 1h)
| order by TimeGenerated asc
| render timechart

Separate server and database

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL"
| where MetricName == "sessions_count"
| parse Resource with * "/servers/" ServerName "/databases/" DatabaseName
| summarize maxSessions = max(Maximum) by bin(TimeGenerated,1h), ServerName, DatabaseName
| order by TimeGenerated asc
| render timechart

Maximum per 30-minute bucket over 7 days

AzureMetrics
| where TimeGenerated >= ago(7d)
| where ResourceProvider == "MICROSOFT.SQL"
| where MetricName == "sessions_count"
| summarize MaxPer30m = max(Maximum) by bin(TimeGenerated, 30m), Resource
| order by TimeGenerated asc

Compare each hour with the resource maximum

let hourlyData =
    AzureMetrics
    | where ResourceProvider == "MICROSOFT.SQL"
    | where MetricName == "sessions_count"
    | summarize MaxPerHour = max(Maximum) by bin(TimeGenerated, 1h), Resource;
let globalMaxByResource =
    hourlyData
    | summarize MaxOfMaxPerHour = max(MaxPerHour) by Resource;
hourlyData
| join kind=leftouter globalMaxByResource on Resource
| project TimeGenerated, Resource, MaxPerHour, MaxOfMaxPerHour
| order by Resource asc, TimeGenerated asc

A single database

The Resource field holds the full Azure resource ID (ARM), so an exact match with == needs the complete ID, as in this example (fictitious IDs).

AzureMetrics
| where TimeGenerated >= ago(7d)
| where ResourceProvider == "MICROSOFT.SQL"
| where Resource == "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/rg-example/providers/Microsoft.Sql/servers/sqlserver01/databases/AppDb"
| where MetricName == "sessions_count"
| summarize max(Maximum) by bin(TimeGenerated, 1h), Resource

Remarks

Mastodon LLIMastodon SocialFosstodon