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

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

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-automation-logs-in-azure-monitor-logs

 

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!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

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