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

Detetar bloqueios

Procure 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ção do servidor

Procure eventos de desligamento do servidor e pronto para o 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

Ligaçõ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

Ligações autorizadas

Procure 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

Procure 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

Procure por contenção de bloqueio no log do PostgreSQL. Requer o 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. Requer o parâmetro 'log_autovacuum_min_duration' ativado.

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

Registos de auditoria

Pesquise todos os eventos de auditoria no log do PostgreSQL. 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