Welcome to Day 23 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://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:
Get-ChildItem SQLSERVER:SQL\localhost\SQL2012\JobServerChecking 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:
Get-ChildItem SQLSERVER:SQL\localhost\SQL2012\JobServer\Alerts | `
Select-Object Name, MessageID | Where-Object MessageID -in 823,824,825,829 | `
Format-Table –AutosizeWhat!??? 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.
#Assign variables
$Instance = "localhost\SQL2012"
#Assign the SMO class to a variable
$SMO = "Microsoft.SqlServer.Management.Smo"
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
# Get the Job Server
$JobServer = $Server.JobServer
# First, ensure that a local operator exists.
$Operator = $JobServer.Operators["LocalHost"]
IF (!($Operator)) #Check if the operator exists
{
# Operator does not exist, so create it
$Operator = New-Object ("$SMO.Agent.Operator") ($JobServer, "LocalHost")
$Operator.NetSendAddress = "LocalHost"
$Operator.Create()
}
ForEach ($MessageID in (823,824,825,829))
{
$AlertName = "Alert: Message Id: $MessageID"
# Get the alert if it already exists
# You could search for the alert by name, however you can only have one alert per messageid.
# So, search by the message id.
$Alert = $JobServer.Alerts | Where-Object Severity -EQ $Severity
IF (!($Alert)) #Check to see if the alert already exists
{
# The alert does not exist, so add it
$Alert = New-Object ("$SMO.Agent.Alert") ($JobServer, $AlertName)
$Alert.MessageID = $MessageID
$Alert.Create()
# Add the operator to the alert
$Alert.AddNotification("LocalHost", [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NetSend)
}
}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:
#Assign variables
$Instance = "localhost\SQL2012"
#Assign the SMO class to a variable
$SMO = "Microsoft.SqlServer.Management.Smo"
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
# Get the Job Server
$JobServer = $Server.JobServer
#First, ensure that a local operator exists.
$Operator = $JobServer.Operators["LocalHost"]
IF (!($Operator)) #Check if the operator exists
{
#Operator does not exist, so create it
$Operator = New-Object ("$SMO.Agent.Operator") ($JobServer, "LocalHost")
$Operator.NetSendAddress = "LocalHost"
$Operator.Create()
}
ForEach ($Severity in (22,23))
{
$AlertName = "Alert: Severity: $Severity"
# Get the alert if it already exists
# You could search for the alert by name, however you can only have one alert per Severity.
# So, search by the Severity.
$Alert = $JobServer.Alerts | Where-Object Severity -EQ $Severity
IF (!($Alert)) #Check to see if the alert already exists
{
# The alert does not exist, so add it
$Alert = New-Object ("$SMO.Agent.Alert") ($JobServer, $AlertName)
$Alert.Severity = $Severity
$Alert.Create()
#Add the operator to the alert
$Alert.AddNotification("LocalHost", [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NetSend)
}
}Again, you can only have one alert per Severity, so the code checks for existing alerts by the severity before adding a new one.
