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
- The
AvgSessionsaliases in the original queries actually held a maximum; they are renamedMaxSessionshere. - Incomplete fragments and test variants were removed.