Welcome to Part 2 in the series about SQL Server Agent Job Failures. In this part you are going to learn how to build an HTML report with Failed Agent Job information and send the report via e-mail to the interested team(s).
Let’s get started putting the pieces together to build our report.
First step:
Picking up where we left off based on Part 1 of the series, the
$servers variable is populated with all the SQL Servers you will be checking for Failed Agent Jobs.
Below, you can see we are using the same filters from our original post.
# Now just Failed run outcomes
$failedJobsOnly = $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>Jobs with Failed Status</h2>"
# Show all jobs that did not have a "Successful" last run outcome
$failedJobsAll = $failedJobs | Where-Object LastRunOutcome -ne "Succeeded" | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Non-Successful Jobs</h2>"
# Now filter out those Replication jobs that were cancelled
$failedJobsNoRepl = $failedJobs | Where-Object { ( $_.LastRunOutcome -ne "Succeeded") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Failed Jobs with No Replication Jobs</h2>"
# How about any outcome that is not Failed or Succeeded
$failedJobsNotFailedSuccess = $failedJobs | Where-Object { ( $_.LastRunOutcome -ne "Succeeded" -AND $_.LastRunOutcome -ne "Failed") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Job Status Not Succeed or Fail</h2>"
# What if we want to see jobs that have never been executed
$failedJobsNeverExecuted = $failedJobs | Where-Object { ( $_.LastRunDate -eq "01/01/0001") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Jobs Never Executed</h2>"
# Know we can look for never executed and not enabled
$failedJobsNeverExecutedDisabled = $failedJobs | Where-Object { ( $_.LastRunDate -eq "01/01/0001" -AND $_.IsEnabled -eq $false) -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Jobs Never Executed and Disabled</h2>"
# Do failed jobs have a schedule
$failedJobsNeverExecutedNoSchedule = $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed" -AND $_.HasSchedule -eq $false) -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Jobs Never Executed and No Schedule</h2>"Second step:
At the end of all of the filter commands, you will add a reference to the ConvertTo-Html function. This function along with a couple parameters will convert the output into HTML code for use in the report.
As part of this step we will be adding a
<style> section to the script in order to provide some formatting and an
Out-File command to save output for our report.
This HTML coding is what will enable us to format our data into one final report.
#The command below will combine all the information gathered into a single HTML report $Report = ConvertTo-HTML -Body "$reportInfo $failedJobsOnly $failedJobsAll $failedJobsNoRepl $failedJobsNotFailedSuccess $failedJobsNeverExecuted $failedJobsNeverExecutedDisabled $failedJobsNeverExecutedNoSchedule" ` -Title "Failed SQL Agent Job Report" -Head $header -PostContent "<p id='CreationDate'>Creation Date: $today</p>" #The command below will generate the report to an HTML file $Report | Out-File "C:TempFailedJobsReport.html"
Third Step:
Here you see the HTML report taking shape with data from different filters and heading information.
Heavily redacted to protect the innocent…
Based on the filters we used from the First Step the output has been broken up into multiple sections for the report. This allows you to build multiple filters based on the output you are interested in generating for the report.
Final Step:
The final step is to send the report via e-mail to a list of end users that might be interested in the Failed Agent Jobs.
$body = Get-Content C:TempFailedJobsReport.html -Raw #Send email to DBA Team Send-MailMessage -To gbargsley@gmail.com -From ProdDBAs@gmail.com -Subject " Failed SQL Agent Job Report " -Body $body -BodyAsHtml -Priority High -SmtpServer mail.gmail.com
Conclusion:
Now you know how to get a list of all SQL Agent Jobs and populate a variable. Then you also know how to filter that output to help you identify Failed Agent Jobs. The filter examples also provide information to further identify Agent Jobs that are technical debt, no longer needed or could be candidates for removal.
Resources:
Part 1: Checking remote servers for failed jobs from a Central Console
Watch for Part 3 of our series where you will learn how to setup SQL Server Agent Alerts and Operators to notify you when jobs fail. The easiest way to be notified of new blog posts is by subscribing to this blog!
If you found this blog handy, please use the Subscribe section at the top of the screen to join my newly created mailing list. I hope to start delivering content via the mailing list soon. 
The post Failing SQL Agent Jobs – Part 2 appeared first on GarryBargsley.com.