How to generate alerts for DDL statements on Azure PostgreSQL using Log Analytics

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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

Ricardo_NS_Marques_0-1590140217356.png

 

2 – on the server diagnostic settings, add a new diagnostic setting

Ricardo_NS_Marques_1-1590140217365.png

 

3 – send the PostgreSQLLogs to the log analytics workspace and click on Save:

Ricardo_NS_Marques_2-1590140217374.png

 

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):

Ricardo_NS_Marques_3-1590140217384.png

 

 

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.

 

Ricardo_NS_Marques_4-1590140217391.png

 

 

 

6 – on the Alert configuration, click on the Condition name:

 

Ricardo_NS_Marques_5-1590140217396.png

 

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”

 

Ricardo_NS_Marques_6-1590140217402.png

 

8 – create an Action group (if you don`t have one):

Ricardo_NS_Marques_7-1590140217409.png

 

I`m creating one based on emails

Ricardo_NS_Marques_8-1590140217415.png

 

9 – configure the new alert details:

Ricardo_NS_Marques_9-1590140217420.png

 

10 – check the rule created. Type rules on the search box: 

Ricardo_NS_Marques_10-1590140217422.png

 

Click on Manage alert rules:

Ricardo_NS_Marques_11-1590140217424.png

 

The alert appears:

 

Ricardo_NS_Marques_12-1590140217426.png

 

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:

 

Ricardo_NS_Marques_13-1590140217444.png

 

Now, with the alert setup, you can always correlate with the server logs (inside the Postgresql server, on the Server Logs blade):

 

Ricardo_NS_Marques_14-1590140217449.png

 

The text file will contain the exact statement (for example, if on the alert email the query text is truncated):

Ricardo_NS_Marques_15-1590140217464.png

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.