How to setup email alerts to monitor automation jobs for Azure SQL DB maintenance

Published May 16 2022 03:00 AM 2,071 Views
Microsoft

When we automate Azure SQL DB index and statistics maintenance using Azure Automation, we can monitor the automation job status by forwarding runbook job data to a Log Analytics workspace and then create alert based on Kusto queries to notify you via email.

Reference: https://docs.microsoft.com/en-us/azure/automation/automation-manage-send-joblogs-log-analytics#view-...

 

Prerequisites:

        automation1.png

        automation2.png

  • When you hit save, and run any automation job within the account, the logs will be sent to log analytics workspace.

 

Query the logs and create alerts on the log analytics workspace side:

 

  • Navigate to your log analytics workspace using Azure Portal:

      automation3.png

 

  • By querying AzureDiagnostics logs, you can find information about the runbooks runs you have:

        automation4.png

 

Sample queries that can be used:

1. Jobs that have completed:

AzureDiagnostics

| where Category == 'JobStreams'

| extend jsonResourceDescription = parse_json(ResultDescription)

 

2. Jobs that have completed with errors only:

AzureDiagnostics | where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobStreams" and StreamType_s == "Error" | summarize AggregatedValue = count() by JobId_g

 

3. Jobs that have failed:

AzureDiagnostics | where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobLogs" and (ResultType == "Failed" or ResultType == "Suspended")

 

4. Jobs that have succeeded:

AzureDiagnostics

| where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobLogs" and ResultType == "Completed"

| project TimeGenerated, RunbookName_s, ResultType, _ResourceId, JobId_g

 

Now you can create an alert rule based on any custom query you would like to use:

  • For example creating an alert rule for Jobs that have completed with errors:

Add the query that you would like to use, and then create a new alert rule.

automation5.png

  • Fill the condition that the alert should evaluate, you can specify the runbook name, and the threshold for the query results:

automation6.png

  • Create an action group to send a mail:

automation7.png

 

Thank you!

2 Comments
Co-Authors
Version history
Last update:
‎May 16 2022 02:58 AM
Updated by: