LLI Wiki — My Digital Knowledge Base

Requêtes KQL pour superviser Azure SQL Database

Requêtes Log Analytics (AzureMetrics, AzureDiagnostics, AzureActivity) pour suivre les métriques, deadlocks, connexions échouées et opérations longues d'Azure SQL.

Collection de requêtes KQL pour Log Analytics afin de superviser Azure SQL Database : métriques de ressources, deadlocks, connexions échouées, authentifications échouées et opérations du plan de contrôle longues ou limitées. Elles supposent que les paramètres de diagnostic Azure SQL (métriques, journaux d'audit) et le journal d'activité sont envoyés vers un espace de travail Log Analytics. Voir Monitor Azure SQL Database with Azure Monitor.

Découvrir les données disponibles

Fournisseurs de ressources qui envoient des métriques :

AzureMetrics
| summarize count() by ResourceProvider

Ressources SQL et noms de métriques présents :

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group'
| summarize count() by ResourceId
AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group'
| summarize count() by MetricName

CPU, écriture du journal et lecture des données par heure et par base

AzureMetrics
//| where TimeGenerated between ( datetime('$MetricDate') .. endofday(datetime('$MetricDate')))
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName in ('cpu_percent','log_write_percent','physical_data_read_percent')
| where ResourceId has 'SQLSRV-PRD-01' and Resource matches regex 'SQLDB-PRD-APP-DB[0-9][0-9][0-9][0-9][0-9]$'
| summarize MetricMax = max(Maximum), MetricAvg = avg(Average) by Resource, MetricName, bin(TimeGenerated, 1h)
| extend MetricDate = format_datetime(TimeGenerated, 'yyyy-MM-dd'), MetricTime = format_datetime(TimeGenerated, 'HH:mm')
| where MetricMax > 0 and MetricAvg > 0

Le filtre has sélectionne le serveur logique et l'expression régulière sélectionne les bases selon un modèle de nommage ; à adapter à votre convention.

Deadlocks

Événements bruts :

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'

Par jour :

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
| summarize EventCount = count() by bin(TimeGenerated, 1d)
| render timechart

Par jour et par base :

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| render timechart

Connexions échouées (erreurs utilisateur)

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'connection_failed_user_error'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| render timechart

Uniquement les jours et bases au-dessus d'un seuil :

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'connection_failed_user_error'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| where EventCount > 10
| order by EventCount desc
| render timechart

Authentifications échouées par IP cliente

Lister les ressources qui produisent des données d'audit :

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| summarize count() by Resource

Compter les authentifications échouées par IP cliente pour une base donnée (et master) :

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Resource contains "MYDATABASE" or Resource contains "master"
| where action_name_s == "DATABASE AUTHENTICATION FAILED"
| summarize count() by client_ip_s
// | order by TimeGenerated desc

Journal d'activité : opérations du plan de contrôle

Opérations sur une base :

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where tostring(Properties) contains "sqldb-prd-app-db00001"
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc

Idem, limité à une journée :

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where TimeGenerated >= datetime(2026-02-20)
| where TimeGenerated < datetime(2026-02-21)
| where tostring(Properties) contains "sqldb-prd-app-db00001"
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc

Opérations de plus d'une heure (regroupées par identifiant de corrélation) :

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| summarize min(TimeGenerated), max(TimeGenerated) by OperationNameValue, CorrelationId
| extend Duration = max_TimeGenerated - min_TimeGenerated
| where Duration > 1h
| order by Duration desc

Tous les événements de ces opérations longues :

let LongOps =
AzureActivity
| where ResourceProvider == "Microsoft SQL"
| summarize
    MinTime = min(TimeGenerated),
    MaxTime = max(TimeGenerated)
    by OperationNameValue, CorrelationId
| extend Duration = MaxTime - MinTime
| where Duration > 1h;

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| join kind=inner LongOps on CorrelationId
| project TimeGenerated,
          OperationNameValue,
          ActivityStatusValue,
          CorrelationId,
          Duration,
          Properties
| order by Duration desc, TimeGenerated asc

Limitations (throttling) et quotas

AzureActivity
| where tostring(Properties) has_any ("throttle","limit","quota","TooManyRequests")
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc

Opérations en échec uniquement :

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where ActivityStatusValue == "Failed"
| where tostring(Properties) has_any ("quota","limit","TooManyRequests","429")
| project TimeGenerated, OperationNameValue, Properties
| order by TimeGenerated desc
Mastodon LLIMastodon SocialFosstodon