We can monitor and be alerted on failed jobs as well. This is the basic snippet that gets this information:
$server.JobServer.Jobs | Where-Object LastRunOutcome -eq "Failed"
In the following sample, we are listing all failed jobs and sending an e-mail report out:
Import-Module SQLPS -DisableNameChecking #current server name $servername = "ROGUE" $server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername #get a list of jobs that failed, and convert to HTML $content = ($server.JobServer.Jobs | Where-Object LastRunOutcome -eq "Failed" | Select-Object Name, LastRunDate | ConvertTo-Html) #email settings $currdate = Get-Date -Format "yyyy-MM-dd hmmtt" $smtp = "mail.rogue.local" $to = "DBA <[email protected]>" $from = "DBMail <[email protected]>" $subject = "Failed Jobs as of $currdate"
Send-MailMessage -SmtpServer $smtp -To $to -from $from -Subject $subject -Body "$($content)" -BodyAsHtml