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.
Query the logs and create alerts on the log analytics workspace side:
Sample queries that can be used:
1. Jobs that have completed:
| 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:
| 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:
Add the query that you would like to use, and then create a new alert rule.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.