In a previous post, I showed how to set up statistics maintenance for your Azure databases using Azure Automation. However, what I didn’t show was how to generate an alert when things go south. Let’s do that now.

An Error Needing an Alert

First, I need to generate an error. I’m going to modify the code just slightly from the previous example so that it will fail:

$Cmd=new-object system.Data.SqlClient.SqlCommand("UPDATE STATISTICS dbo.TableNotInDB", $Conn)

If I modify my Runbook with the code above and then run it, I will get an error:

This is from the test pane. I strongly recommend you use tests on your PowerShell scripts when writing your own automation. It’ll save you a lot of pain trying to troubleshoot things later.

Alert on Error

Believe it or not, there’s not an immediately obvious “Oh, you had an error in your Automation script, here’s how you alert someone” setting in the Azure portal. Now, you could simply put error handling in your PowerShell script. In fact, it’s probably not at all a bad idea to do that as well. However, what you would not get setting things up that way is a mechanism for managing the alerts, history, additional possible responses (like firing off another Runbook, although there is way to do that from the PowerShell too). Instead, what I want is way to manage alerts through the Azure fabric.

If you do a search, there is an Azure Alert service. However, it didn’t seem to be really what I was looking for. Further, I found it extremely difficult (OK, I couldn’t make it work) to connect the alerts directly to the Jobs related to my Runbooks. Instead, after quite a bit of research, what I found is a combination of Azure Log Analytics with the Operations Management Suite (OMS) will do exactly what I’m looking for.

On a personal note, I kind of can’t believe I’m working with the Operations Manager again. I was writing articles about that at the very start of my time as an MVP ten years ago. Wild how things come around again.

Getting Started With Alerts

First things first, you have to already have set up your Azure Automation to put this all together. Then, you need to create a Log Analytics workspace. When you have those done, and, assuming you’ve got your PowerShell all up to date (that was the single longest part of making all this work), you can follow the directions in this nice Microsoft blog post to connect them together.

CAUTION: There are a bunch of posts out there purporting to show you how to connect these together using PowerShell, but they’re mostly over a year old, see my blog post from yesterday.

The key command is Set-AzureRmDiagnosticSetting. Pass in the correct values from your Automation and Log Analytics and the rest of this more or less falls into place.

At this point, I stepped out of the code and went over to Operations Manager. You should be automating everything you do, and following along with the Microsoft blog post to set up and manage your system using PowerShell is the correct approach. However, let’s look at pretty pictures:

That’s the default page for the OMS.

To set up an alert is pretty simple. First, let’s do a search of the logs to see if we successfully have everything connected. For my purposes, I have my automation script (which is failing) running a few times an hour so that I don’t have to wait to see results. The basic log search looks like this:

You can type in a * wild card search or just click on the link that will show all data collected (only do that when you’re getting started, not when you have lots of systems under management). Scrolling through the results, I can find where my Automation job failed and generated an error:

With this, I can create a new filter, one based on the data I have. Instead of *, I’ll use this:

RunbookName_s=UpdateStatistics StreamType_s=Error

It’s almost like a T-SQL query except no requirements for AND or quotation marks for the string values. That will filter the results. All I have to do now is click the alert button you’ll see on the screen and I’m setting up my alert:

The rest is pretty self-explanatory (click on the image to make it larger & more visible). Go through and fill out the fields and save this alert. You’ll be getting emails in no time.

Conclusion

The beauty of Azure, and the pain of Azure, is that there is usually more than one way to get something done. My intention with this approach was to stick to services rather than resort to code-based solutions. Yes, code to manage those solutions, but not simply code to run things. This way you get all the features that the service offers, not simply what you’ve coded.

The post Alerts From Azure Automation appeared first on Grant Fritchey.