Checking Alerts on remote server using PowerShell and SMO

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:

cls
[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
Write-Host `
}

Thanks for reading.

Advertisements
This entry was posted in SQL Server and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s