Agent Jobs Reporting

This month we’ll continue playing around with SQL Server Agent creating a script to generate HTML reports regarding Jobs execution and Agent information in general.

SQL Server Agent jobs can be monitored interactively using the “Job Activity Monitor” GUI in SQL Server Management Studio. This monitoring tool gives you a high level overview of jobs currently handled by the Agent along with information such as whether a job is enabled, is running, the last time that it was executed, execution outcome, and so on, and so forth. Following is an example of what the monitor shows:

The interface also offers a way to filter jobs by several properties, as shown in the following picture:

By right-clicking on a job, you can open the “Log File Viewer” GUI that lets you drill down into the job’s execution history as shown in the following picture:

These GUIs are very handy when you want to interactively monitor or analyze job execution history. However, imagine having hundreds of jobs that you want to analyze in several different ways and imagine you want to do it regularly. You’ll have to manually change the filter every time and possibly navigate back and forth from the interfaces. Imagine also having to do all of this for more than one instance. Clearly, an interactive approach would not be effective, if possible at all. You are probably better off creating a reporting solution to help you with these daily activities.

Agent Reporting

There are several ways to build a reporting solution. For example, you can use Reporting Services to build reports that query the SQL Server Agent system catalog.

However, you need to know how to build reports with Reporting Services, how to publish them and/or how to integrate them in SSMS and have a good understanding of SQL Server Agent system tables and/or stored procedures.

An easier way to build such a reporting solution is to leverage PowerShell scripting and SQL Server Management Objects to build static HTML reports.

Simple reporting

Using the function Get-SQLInstance defined to initialize and login to a SQL Server instance that we introduced in previous articles, we can use the connection to retrieve the Jobs collection and output the desired information by selecting properties as in the following example:

$Jobs = $SQLInstance.JobServer.Jobs;
$Jobs ' 
| select Name, Category, IsEnabled, OwnerLoginName,` LastRunOutcome, LastRunDate, NextRunDate`
| sort Name;

If you want to filter some elements of a collection, it’s quite easy. For example, filtering out all jobs that belong to job categories whose names start with the keyword “Util” can be simply done as follows:

$Jobs = $SQLInstance.JobServer.Jobs | Whe re { $_.Category -notmatch “^Util*”};
$Jobs `
  | select Name, Category, IsEnabled,`
                LastRunOutcome, LastRunDate, NextRunDate `
  | sort Name;

It’s very easy to format the output into a more readable format using the Format-Table cmdlet and we could write the results to a text file or we could export it in CSV format to be displayed in Excel.

However, we want to build a solution that creates HTML reports for convenience. This can be done by piping the results into the Convert-To-Html cmdlet first and into the Out-File after as shown in the following script fragment:

| ConvertTo-HTML `
| Out-File “jobs.htm”;

 By opening the jobs.htm file in a browser, you can see the rendered results in a table which, by default, is not very readable:

Furthermore, the page title by default is “HTML TABLE”. The title can be changed simply by specifying the –Title parameter for the ConvertTo-HTML cmdlet.
However, you probably want to change the graphical layout of the table. This can be done by passing to the ConvertTo-HTML cmdlet the URI of a cascading style sheet (CSS) using the –CssUri parameter or by directly embedding style sheet information into the content of the tag using the –Head parameter. The title can also be changed by inserting the “@ $Jobs ` | select Name, Category, IsEnabled, ` LastRunOutcome, LastRunDate, NextRunDate ` | sort Name ` | ConvertTo-HTML -Head $Head ` | Out-File “jobs.htm”;

Combining HTML fragments

The technique we’ve seen in the previous section is fine, but has some limitations. For example, what about combining several results into a single HTML page?

Let’s say you want to include SQL Server Agent configuration properties in the report. You can easily do this by calling the get_Properties() method of the JobServer class as follows:

$SQLInstance.JobServer.get_Properties() `
  | Select Name, Value `
  | Sort Name;

 However, this typically returns more properties than we are interested in. We can introduce a filter in a similar way as we did to filter jobs, but in this case instead of matching names, we are going to match against a collection of properties we’re interested in, per the following code:

$AgentProperties = “ErrorLogFile”, “JobServerType”, “ServiceAccount”;
$SQLInstance.JobServer.get_Properties()`
  | where {$AgentProperties -contains $_.Name } `
  | Select Name, Value `
  | Sort Name `
  | ConvertTo-HTML -Head $Head `
  | Out-File “jobs.htm”;

 The job.htm contains these newly extracted results:

The problem trying to combine more result sets is that the ConvertTo-HTML cmdlet generates a full HTML file ready to be parsed and rendered. This means we cannot simply concatenate the results output by the cmdlet but, luckily, it has a parameter named –Fragment that generates only enough HTML code to be assembled in the final HTML page.

We still have to provide the concatenation of the fragments and, eventually, more information like headings, per the following code:

The result can be seen in this captured image:

Adding more

One good thing about this approach, is that you can quickly add more information by simply iterating the various collections, producing an HTML fragment and concatenating it to the rest of the report.
For example, let’s say you want to display the following information:

• jobs execution history for jobs that have run in the last 24 hours;
• failed job step history for jobs that have run in the last 24 hours
• jobs that have not been run in the last 24 hours.

You can start by adding some code to the script to calculate the threshold date and time and populate the Job History collection as follows:

$Hours = 24;
$DateFrom = (Get-Date).AddHours(($Hours * -1));
$JobsHistory = $SQLInstance.JobServer.EnumJobHistory();

 Then, you create the various script fragments that pull the data from the collections, pass each of them to the ConvertTo-HTML CmdLet and finally concatenate the results as the following code shows:

Again, you can see the result in the following image:

Conclusion

In this article we have seen how to build simple, yet informative, HTML reports by leveraging PowerShell ConvertTo-HTML cmdlet and SMO.

We’d love to hear from you—especially about which topics you’d like us to cover next.

Post co-authored by Gianluca Hotz and Herbert Albert.