Consultas para a tabela PGSQLServerLogs

Para obter informações sobre como usar essas consultas no portal do Azure, consulte o tutorial do Log Analytics. Para a API REST, consulte Consulta.

Mensagens de erro

Exiba todas as mensagens de erro do log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where ErrorLevel =~ "ERROR"
| order by TimeGenerated desc 
| take 100

Mensagens fatais

Exiba todas as mensagens fatais do log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where ErrorLevel =~ "FATAL"
| order by TimeGenerated desc 
| take 100

Detectar deadlocks

Pesquise eventos de deadlock no log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "deadlock detected"
| order by TimeGenerated desc 
| take 100

Reinicializações do servidor

Pesquise por eventos prontos para servidor e desligamento do servidor no log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "database system was shut down" or Message has "database system is ready to accept"
| order by TimeGenerated asc
| take 100

Conexões recebidas

Pesquise mensagens de conexões recebidas no log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "connection received"
| extend allmatches=extract_all(@'host=(.+)port=(.+)', Message)
| project TimeGenerated, host=allmatches[0][0], port=allmatches[0][1], Message,ProcessId,ErrorLevel, SqlErrorCode
| order by TimeGenerated desc 
| take 100

Conexões autorizadas

Pesquise mensagens de conexões autorizadas no log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "connection authorized"
| extend allmatches=extract_all(@'user=(.+)database=(.\S+)', Message)
| project TimeGenerated, user=trim(@" ",tostring(allmatches[0][0])), database=allmatches[0][1], Message,ProcessId,ErrorLevel, SqlErrorCode
| where  ['user'] !='azuresu' //exclude Azure managed superuser
| order by TimeGenerated desc 
| take 100

Falhas de conexões

Pesquise conexões não autorizadas (com falha) no log do PostgreSQL.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "authentication failed" or SqlErrorCode in ('28000', '28P01', '3D000', '53300', '42501','08P01')
| where ErrorLevel =~'FATAL'
| order by TimeGenerated desc 
| take 100

Contenção de bloqueio

Pesquise contenção de bloqueio no log do PostgreSQL. Requer parâmetro log_lock_waits=ON.

// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "still waiting for"
| order by TimeGenerated desc 
| take 100

Eventos de vácuo automático

Pesquise eventos de vácuo automático no log do PostgreSQL. Ele requer o parâmetro 'log_autovacuum_min_duration' habilitado.

PGSQLServerLogs
| where Message has "vacuum of table"
| order by TimeGenerated desc 
| take 100

Logs de auditoria

Pesquise todos os eventos de auditoria no log do PostgreSQL. Ele requer que os logs de auditoria sejam habilitados [https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-audit].

PGSQLServerLogs
| where Message contains "AUDIT:"
| order by TimeGenerated desc 
| take 100