Welcome to Day 23 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Today, we’re going to look into the JobServer collections. The JobServer collections manage all aspects of SQL Agent – jobs, job schedules, alerts, operators, etc. We can see all of the collections that the JobServer manages with:

Checking for Alerts

One of the best practices to have in place is to have alerts on system errors 823, 824, 825 and 829. A quick PowerShell script checks see if these are in place:

What!??? You don’t have these in place? Okay, let’s make them.

Creating Operators and Alerts by Error Number

We will create an operator, and set its NetSendAddress to localhost. Next we will create an alert for each of these conditions, and finally add the operator to the alert.

As you can see, as long as you are treating each event the same, you can include all of the MessageIds in the ForEach loop to process them all. One item to point out: you can only have one alert per MessageId, so this script is searching for an existing alert based upon the MessageId to be created. If one already exists, it just skips adding that alert.

Creating Operators and Alerts by Severity

As you might imagine, creating alerts by a severity is just as easy… just change the MessageId to Severity:

Again, you can only have one alert per Severity, so the code checks for existing alerts by the severity before adding a new one.