SSRS Reports to Monitor SQL Server Agent Jobs, Status and Details

Problem

How do you create a SQL Server Reporting Service (SSRS) report to show details of SQL Server Agent jobs along with their execution history and the steps and tasks they execute?

Solution

Recently, I was working on a project to show the details of SQL Server Agent jobs. Many third-party tools are available with features that provide insights into jobs, but the client did not want to invest money in monitoring tools. So, we created a report using SSRS.

In this article, we will learn how to create an SSRS report that lists the SQL Server Agent jobs, their execution history, and the commands they execute.

The metadata of SQL Server Agent jobs is stored in the MSDB database of SQL Server. Read this article to learn about the MSDB database. We will use the following sets of DMVs to populate the information of the SQL Server Agent jobs:

1. sysjobs
2. sysjobhistory
3. sysjobsteps
4. sysschedules

Note: All the above DMVs are in the MSDB database of SQL Server. Now, let’s see how to create SSRS reports.

Create an SSRS Report to View SQL Agent Jobs

We will create a parameterized report to view the SQL Server Agent job. For this demonstration, I have created a reporting services project using Visual Studio 2017 with three parameterized reports:

1. SQLJobMain: Lists all SQL Server Agent jobs and the tasks to be performed by the job.
2. SQLJobHistory: Contains the execution history of the selected Agent jobs. The report uses the @JobName parameter to fetch the history of the specified job.
3. SQLJobCommand: Contains the query/tasks to be performed by the SQL job. The report uses the @JobName parameter to fetch the history of the specified job.

This tip does not cover a detailed explanation of how the SSRS report is created. You can refer to this tutorial to learn the step-by-step process of creating an SSRS report.

First, we must create a data source to connect to the SQL Server instance. Open the Report Data pan, right-click on Data Sources, and select Add Data Source.

The image below shows the dialog box, Data Source Properties, that will open. Specify the required parameters and click OK.

The next step is to create a dataset. To create a new dataset, right-click on Data Set and select Add new dataset. We will create a stored procedure named sp_get_sql_jobs_all, which is used to populate the details of the SQL jobs.

The code for the stored procedure is as follows:

CREATE PROCEDURE sp_get_SQL_Jobs_All  
AS  
BEGIN  
  SELECT   
     @@servername AS ServerName
    ,allsqljobs.name AS Name 
    ,categories.NAME AS CategoryName  
    ,SUSER_SNAME(allsqljobs.owner_sid) AS OwnerID  
    ,CASE WHEN allsqljobs.enabled = 1 THEN 'Yes' ELSE 'No' END AS JobEnabled  
    ,CASE WHEN sysschedules.enabled = 1 THEN 'Yes' ELSE 'No' END AS JobScheduled  
    ,msdb.dbo.agent_datetime(schedules.next_run_date, schedules.next_run_time) AS NextRunDate  
  FROM msdb.dbo.sysjobs AS allsqljobs  
     LEFT JOIN msdb.dbo.sysjobschedules AS schedules ON allsqljobs.job_id = schedules.job_id  
     LEFT JOIN msdb.dbo.sysschedules AS sysschedules ON schedules.schedule_id = sysschedules.schedule_id  
    INNER JOIN msdb.dbo.syscategories AS categories ON allsqljobs.category_id = categories.category_id  
END  

Click OK to create a dataset.

Now, we need to create a tabular report.

Drag and drop the table tool from the SSRS Toolbox to the Report Designer pan. Drag and drop the report fields from the dataset to the SSRS table.

Once the fields are added, the report looks like the following image.

Now, let’s create a report to view SQL job history.

Create an SSRS Report to View the SQL Job Execution History

Now, let’s create another report named SQLJobHistory. The report will show the history of SQL jobs. The report uses an input parameter name @SQLJobName. I have created a dataset called Ds_SQLJob_History, which contains a stored procedure named sp_get_SQL_Jobs_history.

The code of the stored procedure is as follows:

CREATE PROCEDURE sp_get_SQL_Jobs_history @JobName varchar(max)  
AS 
BEGIN  
  SELECT 
     jv.NAME AS Job
    ,jh.step_name AS Step
    ,msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS RunDateTime
    ,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(jh.run_duration AS VARCHAR(8)), 8), 3, 0,':'), 6, 0,':'), 9, 0, ':') AS RunDuration  
    ,jh.message AS [Job outcome message]
    ,CASE  
        WHEN jh.run_status = 0 THEN 'Failed'  
        ELSE 'Success'  
        END AS [Job execution status]  
  FROM msdb.dbo.sysjobs_view AS jv  
    INNER JOIN msdb.dbo.sysjobhistory AS jh ON jv.job_id = jh.job_id  
  WHERE jv.name = jv.name  
  ORDER BY 
     job
    ,rundatetime  
END

Below is a screenshot of the data set.

Like the SQL Job main report, add report fields in the Report Designer. The final report looks like the following image:

Now, let’s create a report to see the SQL Job steps.

Create an SSRS Report to View the SQL Job Steps

Now, let’s create another report named SQLJobCommand. The report will show the history of SQL jobs. The report uses an input parameter name @SQLJobName. I have created a dataset called Ds_SQLJob_Step_details, which contains the stored procedure named sp_get_SQL_job_steps. The code of the stored procedure is as follows:

CREATE PROCEDURE sp_get_SQL_job_steps @JobName varchar(max)  
AS 
BEGIN 
  SELECT
     js.database_name AS 'DatabaseName'
    ,j.name AS 'JobName'
    ,js.step_name AS 'StepName'
    ,js.subsystem AS 'Subsystem'
    ,js.command AS 'Command'
  FROM msdb..sysjobs j
    INNER JOIN msdb..sysjobsteps js ON j.job_id = js.job_id
  WHERE j.name = @JobName
  ORDER BY
     j.name
    ,js.step_id
END

Now, configure the data source and data set as explained above. Here is the screenshot of the data set.

Like the SQLJobMain report, add the report fields in the report designer. The final report is shown in the following image:

Configure Action on the Main Report

Now, we aim to show the history of the SQL job in one report and the details of tasks and queries in a separate report.

For example:

1. Suppose a user wants to see the execution history of the sp_purge_job job. When the user clicks on sp_purge_job, the user must be redirected to another report which shows the execution history of the selected SQL Job.
2. Suppose the user wants to see the list of tasks performed by the sp_purge_job job. When a user clicks on sp_purge_job, the user must be redirected to another report which shows the details of tasks per job.

The following screenshot provides more clarity on how the report should work.

Let’s configure the action to view the SQL job execution history.

Configure Action to View SQL Job History

Right-click on the Job Name textbox and select the text box property.

The Text Box Properties dialog box will open. In the dialog box, select Action. Here, you can configure various actions. For this demo, we want to open the SQLJobHistory report and configure the action below.

1. Enable as an action: Select Go to report.
2. Specify a report: The list of reports will be available in a drop-down box. Select SQLJobHistory from the list.
3. Use these parameters to run the report: Here, you can define the variables used to run the report. SQLJobHistory is a parameterized report, and it uses the Jobname parameter, so provide the Jobname as a parameter name, and the value of a parameter is [name] in the report field.

Here is the screenshot of the Text Box Properties box:

Configure Action to View SQL Job Steps

Right-click on the Job Detail textbox and select Text Box Properties.

The dialog box will open. Select Action to configure various actions. In this demo, we want to open the SQLJobHistory report to apply the actions below:

• Enable as an action: Go to Report
• Specify a report: A list of reports will appear in the drop-down box. Select SQLJobCommand from the list.
• Use parameters to run the report: This defines variables used to run the report. The SQLJobCommand is a parameterized report, and it uses the Jobname parameter. Select Jobname as a parameter name and [name] as a parameter value.

Below is a screenshot of the Text Box Properties window.

Click OK to save the data set and close the dialog box.

Preview the SQL Server Agent Report

Once configured, we are ready to preview the report. Open the JobMain report in Report Designer and select Preview.

The preview section shows the list of SQL Server Agent jobs created, as shown in the image below.

Under the Job Name column, click on DatabaseBackup – USER_DATABASES – FULL. It opens another report that shows the execution history of the report (see below image).

Return to the previous report (see below) and click Job Detail for DatabaseBackup – USER_DATABASES – FULL.

This will open another report detailing the steps the SQL job did run, as seen in the following screenshot.

This report can be deployed to the reporting server. To learn more about the deployment process of SSRS reports, check out the following article: SQL Server Reporting Services SSRS Deployment and Implementation.

Summary

This tip explained how to create an SSRS report to view details of a SQL Server Agent job, including the execution history, details of the tasks, and job steps of a selected SQL job.

Next Steps

• Read SQL Server Reporting Services (SSRS) Tutorial to learn more about SQL Server reporting service.
• Read SQL Server Reporting Services SSRS Deployment and Implementation to learn more about the deployment process.