Sunday 15 March 2015

Powershell - Failed Jobs Email

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 <administrator@rogue.local>"
$from = "DBMail <dbmail@administrator.local>"
$subject = "Failed Jobs as of $currdate"
Send-MailMessage -SmtpServer $smtp -To $to -from $from  -Subject $subject -Body "$($content)" -BodyAsHtml

No comments:

Post a Comment