This post is based on the request to get failed SQL jobs in last 24 hours and output should be displayed in a HTML format.
The verification of SQL job steps code is taken from the below blog.
http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/01/29/find-failed-sql-jobs-with-powershell/
The only difference is that the output, it’s formatted HTML output.
#let's get our list of servers. For this, create a .txt files with all the server names you want to check. $sqlservers = Get-Content "f:\powersql\server.txt"; $FailedJobFileName= "f:\PowerSQL\FailedSQLJob.htm" New-Item -ItemType file $FailedJobFileName -Force #we'll get the long date and toss that in a variable $datefull = Get-Date #and shorten it $today = $datefull.ToShortDateString() #let's set up the email stuff $from="pjayaram@appvion.com" $to="pjayaram@appvion.com" $smtp_address = "valid SMTP Address" #Formatting a message body Function writeHtmlHeader { param($fileName) $date = ( get-date ).ToString('yyyy/MM/dd') Add-Content $fileName "<html>" Add-Content $fileName "<head>" Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>" Add-Content $fileName '<title>SQL Job Failure Report </title>' add-content $fileName '<STYLE TYPE="text/css">' add-content $fileName "<!--" add-content $fileName "td {" add-content $fileName "font-family: Tahoma;" add-content $fileName "font-size: 11px;" add-content $fileName "border-top: 1px solid #999999;" add-content $fileName "border-right: 1px solid #999999;" add-content $fileName "border-bottom: 1px solid #999999;" add-content $fileName "border-left: 1px solid #999999;" add-content $fileName "padding-top: 0px;" add-content $fileName "padding-right: 0px;" add-content $fileName "padding-bottom: 0px;" add-content $fileName "padding-left: 0px;" add-content $fileName "}" add-content $fileName "body {" add-content $fileName "margin-left: 5px;" add-content $fileName "margin-top: 5px;" add-content $fileName "margin-right: 0px;" add-content $fileName "margin-bottom: 10px;" add-content $fileName "" add-content $fileName "table {" add-content $fileName "border: thin solid #000000;" add-content $fileName "}" add-content $fileName "-->" add-content $fileName "</style>" Add-Content $fileName "</head>" Add-Content $fileName "<body>" add-content $fileName "<table width='100%'>" add-content $fileName "<tr bgcolor='#CCCCCC'>" add-content $fileName "<td colspan='4' height='25' align='center'>" add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>SQL Job Stauts Report - $date</strong></font>" add-content $fileName "</td>" add-content $fileName "</tr>" add-content $fileName "</table>" } # Function to write the HTML Header to the file Function writeTableHeader { param($fileName) Add-Content $fileName "<tr bgcolor=#CCCCCC>" Add-Content $fileName "<td width='10%' align='LEFT'>ServerName</td>" Add-Content $fileName "<td width='50%' align='left'>JobName</td>" Add-Content $fileName "<td width='10%' align='left'>JobLastRun Outcome</td>" Add-Content $fileName "<td width='10%' align='left'>JobLastRunStatus</td>" Add-Content $fileName "</tr>" } Function writeHtmlFooter { param($fileName) Add-Content $fileName "</body>" Add-Content $fileName "</html>" } Function writeInfo { param($filename,$Servername,$name,$outcome,$Status) Add-Content $fileName "<tr>" Add-Content $fileName "<td align=left ><b>$servername</td>" Add-Content $fileName "<td align=left ><b>$name</td>" Add-Content $fileName "<td align=left ><b>$Outcome</td>" Add-Content $fileName "<td align=left ><b>$Status</td>" Add-Content $fileName "</tr>" } writeHtmlHeader $FailedJobFileName Add-Content $FailedJobFileName "<table width='100%'><tbody>" Add-Content $FailedJobFileName "<tr bgcolor='#CCCCCC'>" Add-Content $FailedJobFileName "<td width='100%' align='center' colSpan=4><font face='tahoma' color='#003399' size='2'><strong> SQL Job Details</strong></font></td>" Add-Content $FailedJobFileName "</tr>" writeTableHeader $FailedJobFileName #here, we will begin with a foreach loop. We'll be checking all servers in the .txt referenced above. foreach($sqlserver in $sqlservers) { #here we need to set which server we are going to check in this loop $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver; #now let’s loop through all the jobs foreach ($job in $srv.Jobserver.Jobs) { #now we are going to set up some variables. #These values come from the information in $srv.Jobserver.Jobs $jobName = $job.Name; $jobEnabled = $job.IsEnabled; $jobLastRunOutcome = $job.LastRunOutcome; $jobLastRun = $job.LastRunDate; #we are only concerned about jos that are enabled and have run before. #POSH is weird with nulls so you check by just calling the var #if we wanted to check isnull() we would use !$jobLastRun if($jobEnabled = “true” -and $jobLastRun) { # we need to find out how many days ago that job ran $datediff = New-TimeSpan $jobLastRun $today #now we need to take the value of days in $datediff $days = $datediff.days #gotta check to make sure the job ran in the last 24 hours if($days -lt 1 ) { #and make sure the job failed IF($jobLastRunOutcome -eq “Failed”) { #now we add the job info to our email body. use `n for a new line Write-Host $sqlserver $jobName $jobLastRunOutcome $JobLastRun writeInfo $FailedJobFileName $sqlserver $jobName $jobLastRunOutcome $JobLastRun } } } } } Add-Content $FailedJobFileName "</table>" writeHtmlFooter $FailedJobFileName $date = ( get-date ).ToString('yyyy/MM/dd') $body = Get-Content $FailedJobFileName $body = New-Object System.Net.Mail.MailMessage $from,$to, "Job Status - $Date", $body $body.isBodyhtml = $true $smtp = new-object Net.Mail.SmtpClient($smtp_address) $smtp.Send($body)
Outptut
