SQL Lion

It’s been a while when I wrote my last article. But thanks to all your comments, suggestions and support which motivates me to keep writing and today, I am starting a new column in my blog i.e. SSRS.  :)

Problem Statement: Many times the DBAs, administrators and even the top managers would like to see and track the daily status of all the JOBs running on a server i.e. specifically the production servers. But not all the people have access to production servers, except the administrators and publishers. Hence, they are expecting some other way which not only gives information about the status of all the jobs but should be easy enough to understand it.

Solution: Why not to create a custom report to do the same job what “Job Activity Monitor” is doing. Even we can do better as per our requirement. Hence, I am going to show how to build a custom report in SSRS to display the status of all the jobs running under a given server.

As you all might know, we have lots of system stored procedures, views and tables that are facilitating us in doing enormous jobs in our daily work. I am going to list down few of them which helped me to figure out information related to jobs.

• master.dbo.xp_sqlagent_enum_jobs: This one I like the most. It will tell you about the job running status, the job start time, etc. But I picked only Job running status from here. As sometimes in job activity monitor and job history dialog box, you can see the Step status is in progress mode even though job status is showing as completed. Most probably because of refreshment delay in UI cache.  The “running” column will tell you whether the job is in progress, retrying or completed. But it cannot state whether it is Failed, Cancelled or Succeed. Below is the column description for “running”:

• msdb.dbo.sysjobservers: This is one interesting table, which will give information about the last execution of the job like the last run status of the job i.e. SUCCESS, FAILED, CANCELED. But it will not state anything about the current running JOB. Hence, this one too is not good enough to accomplish our entire requirement. The description of the column “last_run_outcome” :

• msdb..sysjobs: This will give you everything related to metadata of JOBs like jobname, job enabled or not, starting step id, etc.
• msdb..sysjobsteps: This is the place where we can get all the steps information for a particular given job. Joining with sysjobs, it will filter for a given job.
• msdb..sysjobhistory: This is another table which gives details about the running status for each step like run_status, run_date, run_time, run_duration. The description of the column “run_status”:

• As we can see any one of the above is not sufficient to get all the information that we need. Hence, I used all of them to get information similar to what “Job Activity Monitor” and “Job History” can provide.If the job is not running for the given day or it is disabled or idle, then we have to do left outer join msdb..sysjobsteps and msdb..sysjobhistory to get idle job information.

The below script will give you information like job name, job starting time, job running duration, current job status, overall job summary message, detail step message in case of failure or last step detail message.

DECLARE @HistoryRange int = -1
SET NOCOUNT ON;
DECLARE @xp_results TABLE (
    job_id                UNIQUEIDENTIFIER NOT NULL,
    last_run_date         INT              NOT NULL,
    last_run_time         INT              NOT NULL,
    next_run_date         INT              NOT NULL,
    next_run_time         INT              NOT NULL,
    next_run_schedule_id  INT              NOT NULL,
    requested_to_run      INT              NOT NULL,
    request_source        INT              NOT NULL,
    request_source_id     sysname          COLLATE database_default NULL,
    running               INT              NOT NULL,
    current_step          INT              NOT NULL,
    current_retry_attempt INT              NOT NULL,
    job_state             INT              NOT NULL);
 
DECLARE @job_owner AS sysname;
 
SET @job_owner = SUSER_SNAME();
 
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner;
 
 
;WITH   CTESteps
AS     (SELECT Isjs.job_id,
               Isjs.step_name,
               isjh.step_id AS RunningStep_id,
               Isjh.run_status,
               MAX(Isjs.Step_id) OVER (PARTITION BY Isjs.job_id) AS MaxStepID,
               MAX(Isjh.Step_id) OVER (PARTITION BY Isjs.job_id) AS MaxRunningStepID,
               Isjh.[message],
               MIN(CAST (STR(Isjh.run_date) AS DATETIME) + CAST (STUFF(STUFF(REPLACE(STR(Isjh.run_time, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS TIME)) OVER (PARTITION BY Isjs.job_id) AS Job_Start_DateTime,
               MAX(CAST (STR(Isjh.run_date) AS DATETIME) + CAST (STUFF(STUFF(REPLACE(STR(Isjh.run_time, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS TIME)) OVER (PARTITION BY Isjs.job_id) AS Job_End_DateTime,
               CAST(CAST (STUFF(STUFF(REPLACE(STR(Isjh.run_duration % 240000, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS TIME) AS Step_Duration,
               Isjh.run_duration / 240000 AS ExtraDays
        FROM   (SELECT *
                FROM   msdb..sysjobsteps WITH (NOLOCK)
               ) AS Isjs
               LEFT OUTER JOIN
               (SELECT *
                FROM   msdb..sysjobhistory s WITH (NOLOCK)
                WHERE  run_date >= (select MAX(run_date) from msdb..sysjobhistory t (nolock) where t.run_date >= CONVERT(varchar(8),Dateadd(DD,@HistoryRange,getdate()),112) and t.job_id = s.job_id and t.step_id = 1)
					   AND run_time >= (select MAX(run_time) from msdb..sysjobhistory t (nolock) where t.run_date = s.run_date and t.job_id = s.job_id and t.step_id = 1)
                ) AS Isjh
               ON Isjs.step_id = Isjh.step_id AND Isjs.job_id = Isjh.job_id
        )
 
,CTEJobs AS(
SELECT A.job_id,
	   MAX(Job_Start_DateTime) AS Job_Start_DateTime,
       MAX(CASE 
			WHEN RunningStep_id = MaxRunningStepID THEN CAST ((DATEDIFF(DD, Job_Start_DateTime, (Job_End_DateTime + Step_Duration) - CAST (Job_Start_DateTime AS TIME)) + ExtraDays) AS VARCHAR (10)) + ' Days ' + CAST (CAST ((Job_End_DateTime + Step_Duration) - CAST (Job_Start_DateTime AS TIME) AS TIME) AS VARCHAR (8)) 
	   END) AS Job_Duration,
       MIN(CASE 
				WHEN MaxStepID = MaxRunningStepID AND RunningStep_id = MaxRunningStepID AND run_status IN (1, 4) THEN ' SUCCESS' 
				WHEN MaxStepID != MaxRunningStepID AND RunningStep_id = MaxRunningStepID AND run_status IN (1, 4) THEN 'IN PROGRESS' 
				WHEN RunningStep_id = MaxRunningStepID AND run_status IN (0, 3) THEN 'FAILED' 
				WHEN RunningStep_id = MaxRunningStepID AND run_status = 2 THEN 'RETRYING' 
			END) AS Job_Status,
       'Last Running Step Status: [ ' + MIN(CASE 
								WHEN MaxStepID = MaxRunningStepID AND RunningStep_id = MaxRunningStepID AND run_status IN (1, 4) THEN ' SUCCESS' 
								WHEN MaxStepID != MaxRunningStepID AND RunningStep_id = MaxRunningStepID AND run_status IN (1, 4) THEN 'IN PROGRESS' 
								WHEN RunningStep_id = MaxRunningStepID AND run_status IN (0, 3) THEN 'FAILED' 
								WHEN RunningStep_id = MaxRunningStepID AND run_status = 2 THEN 'RETRYING' 
							  END  
						+ ' ] ||'+CHAR(13)+'Last Running Step#:' 
						+ CASE 
							WHEN RunningStep_id = MaxRunningStepID THEN STR(MaxRunningStepID) + '  (' + step_name + ') ||'+CHAR(13) + ISNULL([message], '') 
						  END )AS Last_Step_Message
FROM   CTESteps A
GROUP BY A.job_id
)
 
 
SELECT  
Job_Name,
ISNULL(STUFF(STUFF(STUFF(CONVERT(varchar(50), Job_Start_DateTime,109),21,4,' '),1,4,CAST(MONTH(Job_Start_DateTime) AS VARCHAR(2)) + '/'), LEN(MONTH(Job_Start_DateTime)) + 4,1,'/'),'') AS Job_Start_DateTime,
ISNULL(CASE WHEN Job_Status IN('IN PROGRESS','RETRYING') 
 THEN CAST (DATEDIFF(DD, Job_Start_DateTime, (GETDATE()) - CAST (Job_Start_DateTime AS TIME)) AS VARCHAR (10)) + ' Days ' + CAST (CAST ((GETDATE()) - CAST (Job_Start_DateTime AS TIME) AS TIME) AS VARCHAR (8)) 
	 ELSE Job_Duration END,'') AS Job_Duration,
Job_Status,
CASE WHEN Job_Start_DateTime IS NULL THEN 'Job is not Running.' 
	 WHEN Job_Status = 'IN PROGRESS' THEN 'Job is in Progress.'
	 WHEN Job_Status = 'RETRYING' THEN 'Job is in between Retries.' 
	 ELSE LEFT(last_outcome_message, charindex('.',last_outcome_message,1)) END AS Job_Message,
CASE WHEN Job_Status = 'SUCCESS' THEN last_outcome_message
	 ELSE ISNULL(Last_Step_Message,'') END AS Last_Step_Message
FROM 
(
SELECT   J.name AS Job_Name,
         A.Job_Start_DateTime,
         A.Job_Duration,
		 CASE WHEN A.Job_Status NOT IN('IN PROGRESS',  'RETRYING') AND C.last_run_outcome = 1 THEN 'SUCCESS'
			  WHEN A.Job_Status NOT IN('IN PROGRESS',  'RETRYING') AND C.last_run_outcome = 0 THEN 'FAILED'
			  WHEN A.Job_Status NOT IN('IN PROGRESS',  'RETRYING') AND C.last_run_outcome = 3 THEN 'CANCELED'
			  WHEN A.Job_Status = 'IN PROGRESS' AND B.running IN (1, 2, 7) THEN 'IN PROGRESS' 
			  WHEN A.Job_Status = 'IN PROGRESS' AND B.running = 3 THEN 'RETRYING'
			  WHEN A.Job_Status = 'IN PROGRESS' AND B.running NOT IN (1,2,7,3) THEN 'SUCCESS'
			  ELSE ISNULL(A.Job_Status,'IDLE') END AS Job_Status,
		 C.last_outcome_message,
         A.Last_Step_Message
FROM     CTEJobs AS A
         INNER JOIN 
         msdb..sysjobs AS J ON A.job_id = J.job_id
         LEFT OUTER JOIN
         @xp_results AS B ON A.job_id = B.job_id
         LEFT OUTER JOIN
         msdb.dbo.sysjobservers C ON A.job_id = C.job_id
) JOBs 
;

OUTPUT:

So with the help of above script, we can see all the information that we want to know related to a job. Now, let’s go for a feasible way to publish it so that the top managers and other users who do not have direct access to the server can also see this information. And of course we need to put it in a more presentable way than what it looks here.  :)

Follow the below steps to create SSRS report for this:

Step1: Open BIDS, create a new project and name it “StatusReport” as below.

Step2: Now in the solution explorer, right click on “Reports” and click on “Add » New Item…”

Step3: In the “Add New Item” dialog box, add a report as shown below.

This will create one empty report.

Step4: Under “Report Data” panel, add a new data source to the report.

Under Data Source Properties dialog box, enter the name of the data source as “JobStatusDataSource” and fill the rest as shown below.

This is the place where you are going to specify the server, for which the jobs needs to be tracked. And “msdb” is the database where SQL SERVER stores all jobs information.

Step5: Once the data source is created, create the dataset which will contain all the required job information. Under “Report Data” panel, add dataset under “Datasets” as shown below.

Name the dataset as “DataSetDW” and fill all other information as shown below. Copy the query from the above script and paste it in “Query” section, but remove the part that is highlighted in RED, as the parameter should come from the report.

Click on “Refresh Fields”, it will ask for value of the parameter @HistoryRange, enter the value “-1” and click on OK. Then Press OK.

SSRS will automatically create a parameter named “HistoryRange” under “Parameters” section as shown below.

Step6: Double click the parameter named “HistoryRange”.  Change the datatype to Integer and set the values as below in “Available Values” Pane.

In the original query i.e. in dataset, the values will be subtracted from the date part of today’s date (i.e. getdate()), to see historical job information.

Click on the “Default Values” in the left pane, and set the default value to -1 i.e. for current date.

Step7: Add images that are required to visualize data graphically under “Images” section. It’s up to the user’s comfort and taste.

Step8: Drag one table from “Toolbox” to the report work area and add 5 columns to it as shown below:

Right click on this tablix and go to properties and set the dataset for the tablix as “DataSetDW” and check the “Repeat header columns on each page”.

Step9: Right click on the row detail header and add a row Inside Group – Above as shown below:

Once the row is added, select the first Cell and right click and select the “Group Properties” as shown below.

Add “Group on” as [Job_Name] as below and click OK as shown below.

Step10: Add all required Columns to the row grouping as shown below.

Step11: Select all the cells in the lower row and click on “Merge Cells” as below:

Add “Last_Step_Message” column to the merged row cell.

Step12: Right click on the row header of merged row and click on “Row Visibility…”

Select “Hide” option for initial run and check the “Display can be toggled” checkbox and configure the values as shown below:

Step13: Primary work for the report is done; the rest related to background, foreground and other properties can be configured to give better look and feel.

Step14: Deploy the reports on the report server.

Step15: Go to Report Manger and navigate to “Data Sources” in report properties. Store the credential in the server so that whoever has the access to report manager can see the reports. Configure it as shown below and press Apply.

Step16: If necessary you can create the subscriptions so that the administrators and managers can get a daily report in email stating the status of the jobs.

Finally our report will looks like this:

I have scraped out the job names for security reason.

Hope you all would have enjoyed reading this article. Waiting for your valuable feedback and comments. I have attached the .rdl file too; hope that will help you readers.

Download .rdl file: JOB Running Status Report RDL (586)

Keep reading; hopefully I will publish my next article demonstrating Cube Process status in the coming week.  :)