Track SQL Data with Azure Alerts and Dashboard Charts

I’m trying already for quite some time to track the result of some SQL query in my Azure Dashboard and even better: trigger Azure Alerts when some condition is reached. See my previous blog posts on Query SQL database using a Azure LogicApp and Show results of SQL query on Azure Dashboard. Thanks to to some ideas from my college we found a better approach to this requirements.

Goal

We have a SQL query which returns us a count. We like to monitor this count in a Azure Dashboard chart and trigger Azure Alerts when they reach a certain value.

Solution

Here is the short version how we finally got in working:

1. Create a Azure Logic App with a “Schedule” trigger.
2. Add action after the trigger to query the database (see here).
   Tip: To know the structure of the SQL query result for the the next steps, run the Logic App and check the SQL query’s output.

3. Use “Settings” on the query action and add a “Tracked Property” which logs the value from the query result to the diagnostic log.
   
   
   Expression in Tracked Properties value I used:
   "@int(body('Query_Event_Count')?['resultsets']['Table1'][0]['count'])"

4. Add a diagnostic setting to log “WorkflowProcess” and “Send to Log Analytics”.
   

5. Run the Logic App and check everything is working.
6. Wait about 5 minutes and check Log Analytics “Azure Diagnostics” for the query action and see if the “Tracked Property” got recorded correctly.
7. Create a Log Analytics query which query the these tracked values.
   AzureDiagnostics
| where TimeGenerated > ago(24h) and trackedProperties_countEvents_s != ""
| order by TimeGenerated desc nulls first
| project CountEvents=trackedProperties_countEvents_s
8. Use this query to define an Azure Alert Rule with source set to Log Analytics.
9. Use the same query in a Azure Monitor “Workbook” to query and render a chart or whatever you like to visualize.
10. Pin this workbook or only its chart to the Azure Dashboard.