Skip to content

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.
    undefined
    undefined
    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”.
    undefined

  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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: