Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
For information on using these queries in the Azure portal, see Log Analytics tutorial. For the REST API, see Query.
Error messages
Display all error messages from the PostgreSQL log.
// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where ErrorLevel =~ "ERROR"
| order by TimeGenerated desc
| take 100
Fatal messages
Display all fatal messages from the PostgreSQL log.
// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where ErrorLevel =~ "FATAL"
| order by TimeGenerated desc
| take 100
Detect deadlocks
Search for deadlock events in the PostgreSQL log.
// To create an alert for this query, click '+ New alert rule'
PGSQLServerLogs
| where Message has "deadlock detected"
| order by TimeGenerated desc
| take 100
Server restarts
Search for server shut down and server ready events in the PostgreSQL log.
// 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
Connections received
Search for received connections messages in the PostgreSQL log.
// 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
Connections authorized
Search for authorized connections messages in the PostgreSQL log.
// 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
Connections failures
Search for unauthorized (failed) connections in the PostgreSQL log.
// 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
Lock contention
Search for lock contention in PostgreSQL log. It requires parameter 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
Autovacuum events
Search for autovacuum events in PostgreSQL log. It requires parameter 'log_autovacuum_min_duration' enabled.
PGSQLServerLogs
| where Message has "vacuum of table"
| order by TimeGenerated desc
| take 100
Audit logs
Search for all audit events in PostgreSQL log. It requires audit logs to be enabled [https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-audit].
PGSQLServerLogs
| where Message contains "AUDIT:"
| order by TimeGenerated desc
| take 100