Tag Archives: Notifications

SQL Server Operators and Notifications with Powershell – Strange Enumerate issue fixed by @napalmgram

Posted on by

Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.

I created a SQL Server Object

2013-09-04_125056

One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object

$server | Get-Member

gave me the JobServer Property

$Server.JobServer|gm

includes the Operator Property

$Server.JobServer.Operators | gm
2013-09-04_125717

has the EnumJobNotifications and EnumNotifications methods

So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator

2013-09-04_130052

and create a simple report

However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.

So I ended up with two functions

2013-09-04_174005
2013-09-04_173953

and I thought I could do this

2013-09-04_174056

But that doesnt work

So I tried this

2013-09-04_174112

and that doesnt work either

Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.

2013-09-05_113931

Job Notifications are System.Object and Alert Notifications are System.Array

I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.

So the final script is as follows

2013-09-05_114601

and the script is

#############################################################################################
#
# NAME: Show-SQLServerOperators.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:03/09/2013
#
# COMMENTS: Load function for Enumerating Operators and Notifications
# ————————————————————————

Function Show-SQLServerOperators ($SQLServer) {
    Write-Output "############### $SQLServer ##########################"
    Write-Output "#####################################################`n"     

    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
        
        
    foreach ($Operator in $server.JobServer.Operators) {
        $Operator = New-Object ("$SMO.Agent.Operator") ($server.JobServer, $Operator)

        $OpName = $Operator.Name
        Write-Output "Operator $OpName"
        Write-Output "`n###### Job Notifications   ######"
        $Operator.EnumJobNotifications()| Select JobName | Format-Table
        Write-Output "#####################################################`n"  
        Write-Output "`n###### Alert Notifications  #######"
        $Operator.EnumNotifications() | Select AlertName | Format-Table
        Write-Output "#####################################################`n"  
                 
    }
 
}