A dashboard visually summarizes all the important metrics you have selected to track, to give you a quick and easy overview of where everything stands. A real-time dashboard is a live view of exactly how your application information is performing. This helps in better decision making and helps in taking proactive actions.
This blog explains how you can use Azure Log Analytics to create a custom dashboard for Azure Database for PostgreSQL.
This is a starter guide. You can use these ideas and customize as per your need.
Log in to the Azure portal. Select Dashboard to open your default dashboard.
Select New dashboard
Give the Name for your Dashboard and Select Done Customizing for now
For more details on how to create a Dashboard you can refer to:
When you create a dashboard, it is private by default, which means you are the only person who can see it. To make it visible to others, use the Share button that appears alongside the other dashboard commands. You are asked to choose a subscription and resource group for your dashboard to be published to.
Select the Subscription Name where you would like to share and select Publish
In this step, you configure logging on your Azure Database for PostgreSQL server.
Depending on what you would like to see in the Dashboard, configure the logging as needed.
Azure Database for PostgreSQL is integrated with Azure Monitor diagnostic settings. Through this service, Postgres logs and metrics can be emitted to Azure Monitor Logs, Event Hubs, or Azure Storage. For our scenario we will send to Azure Monitor Logs (also known as Log Analytics).
In the Azure portal, select your Azure Database for PostgreSQL server. In the server's menu, select Diagnostic settings
Click on Add diagnostic setting
Select the data you would like to upload to the Azure Monitor Logs.
(If you don’t have a Log Analytics Workspace, create one. You can refer to https://docs.microsoft.com/azure/azure-monitor/learn/quick-create-workspace).
Note: Audit logs are emitted as part of the PostgreSQLLogs category.
Go to Dashboard again from the Azure portal menu.
Select the custom dashboard you created initially and click on Edit. (In the picture below my custom dashboard is called Azure Database for Postgres).
Click on Add Metrics Chart
Click on Done Customizing. Once you are back on the dashboard page select Edit in Metrics on the chart.
Use Select a scope to choose your Postgres server. Select the Metric you would like to see and the Aggregation.
You can add multiple metrics for the same Postgres server in one chart. You could have multiple Postgres servers listed for the same metric.
In this chart we have the CPU percentage of one PostgreSQL server. You can add metrics of your choice and then select Save to Dashboard.
In the same way you can create more charts. For our example I have charts for Avg CPU, Avg Memory and Max Storage.
Now let's see how we have bring the log data we sent to log analytics into the dashboard.
First go to Monitor from the Azure portal menu, or using the search bar. Select Logs and click on Get started
Select the Scope. (Here for example I selected postgreSQL server )
For the restarts I used the below query.
AzureDiagnostics | where Category == "PostgreSQLLogs" | where LogicalServerName_s == "<ServerName>" | where Message contains "database system was shut down at" or Message contains "database system is ready to accept" | project LogicalServerName_s , Message, TimeGenerated
Run your query to view results. Use Pin to dashboard to make the results of this query a chart.
A couple other query samples:
AzureDiagnostics | where Category == "PostgreSQLLogs" | where LogicalServerName_s == "<Server Name>" | where errorLevel_s contains "error" | project LogicalServerName_s , errorLevel_s , Message, TimeGenerated
Top 10 time consuming queries
AzureDiagnostics | where Category == "QueryStoreRuntimeStatistics" | where db_id_s == "18153" | where LogicalServerName_s == "<Server Name>" | order by total_time_s desc | project query_id_s, total_time_s, rows_s, db_id_s , calls_s, LogicalServerName_s , user_id_s , runtime_stats_entry_id_s | limit 10
My final dashboard:
Hope this helps,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.