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.
Prerequisites:
- Create a Log Analytics workspace in the Azure portal: https://docs.microsoft.com/en-us/azure/azure-monitor/logs/quick-create-workspace
- Enable diagnostic logs on the Automation account to be sent to the log analytics workspace: https://docs.microsoft.com/en-us/azure/automation/automation-manage-send-joblogs-log-analytics#confi...
- 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:
- By querying AzureDiagnostics logs, you can find information about the runbooks runs you have:
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.
- Fill the condition that the alert should evaluate, you can specify the runbook name, and the threshold for the query results:
- Create an action group to send a mail:
Thank you!