Failing SQL Agent Jobs – Part 24 min read

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.

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.

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.

Show me the Code!

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. 👆 

Leave a Reply

Your email address will not be published. Required fields are marked *