It is common practice to configure a set of alerts across all of your servers. These alerts will cover standard Events (severity 017 – 025) and SQL Server conditions (Log space used etc. ). You can manage these alerts through SSMS and scripting, but PowerShell has made this job a lot easier with some very handy SMO classes (server, database, Agent).
For example, if you wanted to check what alerts are configured on all of your servers you can iterate through a table of your instances, or a text file with all of your servers listed, and pull back a list of all of the alerts configured on each instance. You can even expand this to find out how many times each alert has been fired, and when.
The script below shows you how to easily pull this information from one server, and can easily be adapted to manage all of your servers:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$server = "SERVER1"
$Instance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $server
$Alerts = $Instance.JobServer.Alerts
foreach ($Alert in $Alerts)
write-host "Name: " $Alert.Name
write-host "Occurrences: " $Alert.OccurrenceCount
write-host "Type: " $Alert.AlertType
write-host "Count Reset Date: " $Alert.CountResetDate
write-host "Last Occurrence: " $Alert.LastOccurrenceDate
Thanks for reading.