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
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
includes the Operator Property
$Server.JobServer.Operators | gm
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
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
and I thought I could do this
But that doesnt work
So I tried this
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.
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
and can be found here Show-SQLServerOperators