The example below explains how we can setup alerts for DDL commands on Azure Postgresql.
Note that on the example below, if you setup the alert on one server, if you want to include a new server, you only need to follow steps 1, 2 and 3 - if the the two servers share the same log analytics workspace.
Also, on the example below, I`m setting up the alert for CREATE TABLE. This can easily be adapted for other DDL commands
Basically we are going to send the PostgreSql logs to log analytics and build the alerts on top of a log analytics query, like so first of all you need to create a log analytics workspace: https://docs.microsoft.com/en-us/azure/azure-monitor/learn/quick-create-workspace
1 – make sure that on the server parameters you are logging DDL statements
2 – on the server diagnostic settings, add a new diagnostic setting
3 – send the PostgreSQLLogs to the log analytics workspace and click on Save:
4 – Navigate to your Log Analytics workspace. On Logs section. A table named AzureDiagnostics should be visible (it should take some minutes to be available after the first setup):
5 – Copy and paste the query below into the query window and Run (even if doesn`t return any results). Then click on “New alert rule”
AzureDiagnostics | where Message contains "CREATE TABLE" | where Message !contains "CREATE TABLE msftpgbackupprobe" | where Category == "PostgreSQLLogs" | where TimeGenerated > ago(1h) | project TimeGenerated,LogicalServerName_s, Message
Note – if you notice, I`m not specifying any server name. I`m only looking for create tables executed by users in the last hour, inside postgresql logs. I will explain this below.
6 – on the Alert configuration, click on the Condition name:
7 – set the condition logic (specify according with your needs) – I`m setting to trigger an alert every time I have a row for the query output. Then click on “Done”
8 – create an Action group (if you don`t have one):
I`m creating one based on emails
9 – configure the new alert details:
10 – check the rule created. Type rules on the search box:
Click on Manage alert rules:
The alert appears:
Note that the alert doesn`t use postgresql specifically. We are pointing to a Log analytics workspace.
Now, like mentioned previously, if I want to add the same alert for another PostgreSql server I just need to steps 1, 2 and 3, if I use the same log analytics workspace – in other words, the alert is already setup on the log analytics workspace.
An example of an alert email triggered after creating one table on two different servers, sharing the same alert:
Now, with the alert setup, you can always correlate with the server logs (inside the Postgresql server, on the Server Logs blade):
The text file will contain the exact statement (for example, if on the alert email the query text is truncated):
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.