VIEW to list running and completed jobs

,

Install the view and run as-is. If you don't have permissions to run 'ad hoc distributed queries', you need to run this:

-----------
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
-----------

I've tested this on 2016 with no issues.

-Dean

CREATE VIEW [dbo].[v_View_Agent_Job_Run_History] AS
/*
Dean Bell
March 2019
deanbell2000@hotmail.com

--You'll need to have 'ad hoc distributed queries' permission to run.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
*/
With [TIMES] As (
	SELECT [Instance_ID],[Job_ID]
		,CONVERT(datetime,CONVERT(varchar,[Run_DATE]))[Run_DATE]
		,CONVERT(time,STUFF(STUFF(RIGHT('00000'+CONVERT(varchar,[Run_TIME]),6),3,0,':'),6,0,':'))[Start_TIME]
		,CONVERT(time,(Case
			When [Run_Duration]=0 Then CONVERT(varchar,'00:00:01')
			When [Run_Duration]>=240000
			Then CONVERT(varchar,'23:59:59')		
			Else STUFF(STUFF(RIGHT('00000'+CONVERT(varchar,[Run_Duration]),6),3,0,':'),6,0,':')
			End))[Duration]
	FROM [msdb].[dbo].[sysjobhistory]
),[DATES] As (
	SELECT *,[Run_DATE]+Cast([Start_TIME] as datetime)+Cast([Duration] as datetime)[End_DATETIME]
	FROM [TIMES]
),[JOBS] As (
	SELECT H.[Server],H.[Job_ID],J.[name][Job_Name],H.[Instance_ID]
		,(CASE H.[Run_Status]
			WHEN 0 THEN 'Failed'
			WHEN 1 THEN 'Succeeded'
			WHEN 2 THEN 'Retry'
			WHEN 3 THEN 'Cancelled'
			ELSE Cast(H.[Run_Status] as varchar(5)) END)[Run_Status]
		,CONVERT(date,T.[Run_DATE])[Run_DATE]
		,LEFT(T.[Start_TIME],8)[Start_TIME]
		,LEFT(CONVERT(time,T.[End_DATETIME]),8)[End_TIME]
		,LEFT(T.[Duration],8)[Run_Time]
		,H.[step_id][Step_Num]
		,H.[Step_Name]
		,H.[MESSAGE][Log_Msg]
	FROM [msdb].[dbo].[sysjobs][J]
	JOIN [msdb].[dbo].[sysjobhistory][H] ON H.[job_id]=J.[job_id]
	JOIN [DATES][T] ON T.[Instance_ID]=H.[Instance_ID]
	WHERE H.[Run_Status]4
	-----------------------------------------------------------------
	--This UNION retrieves jobs actively RUNNING which requires advanced permissions
	--to run OPENROWSET. Comment this UNION out if you don't need to include RUNNING jobs.
	UNION ALL
	Select O.[Originating_Server][Server],A.[Job_ID],
		J.[name][Job_Name],'9999999'[Instance_ID],'Running'[Run_Status]
		,CONVERT(date,(Case When A.[Start_execution_date]>T.[End_DATETIME]
			Then A.[Start_execution_date] Else T.[End_DATETIME] End))[Run_DATE]
		,LEFT(CONVERT(time,(Case When A.[Start_execution_date]>T.[End_DATETIME]
			Then A.[Start_execution_date] Else T.[End_DATETIME] End)),8)[Start_TIME]
		,''[End_TIME]
		,CONVERT(varchar,DATEDIFF(ss,Case When A.[Start_execution_date]>T.[End_DATETIME]
				Then A.[Start_execution_date] Else T.[End_DATETIME] End,getdate())/86400) -- # of Days in case goes over 24hrs
			+':'
			+CONVERT(varchar,DATEADD(ms,(DATEDIFF(ss,Case When A.[Start_execution_date]>T.[End_DATETIME]
				Then A.[Start_execution_date] Else T.[End_DATETIME] End,getdate())%86400)*1000,0)
			,108)[Run_Time]
		,LEFT(O.[Current_Execution_Step],CharIndex('(',O.[Current_Execution_Step])-1)[Step_Num]
		,SubString(LEFT(O.[Current_Execution_Step],LEN(O.[Current_Execution_Step])-1),CharIndex('(',O.[Current_Execution_Step])+1,999)[Step_Name]
		,'Running step: '+O.[Current_Execution_Step][Log_Msg]
	FROM [msdb].[dbo].[sysjobactivity][A]
	JOIN [msdb].[dbo].[sysjobs][J] ON J.[Job_ID]=A.[Job_ID]
	--Get active running jobs.
	JOIN --OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=yes;','msdb.dbo.sp_help_job @execution_status=1')[O]
	OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
	,'set fmtonly off;
	DECLARE 
	  @job_id                     UNIQUEIDENTIFIER = NULL,  
	  @job_name                   SYSNAME          = NULL,  
	  @job_aspect                 VARCHAR(9)       = NULL,  
	  @job_type                   VARCHAR(12)      = NULL,
	  @owner_login_name           SYSNAME          = NULL,
	  @subsystem                  NVARCHAR(40)     = NULL,
	  @category_name              SYSNAME          = NULL,
	  @enabled                    TINYINT          = NULL,
	  @execution_status           INT              = NULL,
	  @date_comparator            CHAR(1)          = NULL,
	  @date_created               DATETIME         = NULL,
	  @date_last_modified         DATETIME         = NULL,
	  @description                NVARCHAR(512)    = NULL

     EXEC msdb.dbo.sp_help_job @job_id,  
         @job_name,
         @job_aspect,
         @job_type,
         @owner_login_name,
         @subsystem,
         @category_name,
         @enabled,
         @execution_status,
         @date_comparator,
         @date_created,
         @date_last_modified,
         @description
         WITH RESULT SETS
         ((
             job_id                      UNIQUEIDENTIFIER, 
             originating_server          NVARCHAR(30), 
             name                        SYSNAME, 
             [enabled]                   TINYINT, 
             [description]               NVARCHAR(512), 
             start_step_id               INT, 
             category                    SYSNAME, 
             [owner]                     SYSNAME, 
             notify_level_eventlog       INT, 
             notify_level_email          INT, 
             notify_level_netsend        INT, 
             notify_level_page           INT, 
             notify_email_operator       SYSNAME, 
             notify_netsend_operator     SYSNAME, 
             notify_page_operator        SYSNAME, 
             delete_level                INT, 
             date_created                DATETIME, 
             date_modified               DATETIME, 
             version_number              INT, 
             last_run_date               INT, 
             last_run_time               INT, 
             last_run_outcome            INT, 
             next_run_date               INT, 
             next_run_time               INT, 
             next_run_schedule_id        INT, 
             current_execution_status    INT, 
             current_execution_step      SYSNAME, 
             current_retry_attempt       INT, 
             has_step                    INT, 
             has_schedule                INT, 
             has_target                  INT, 
             [type]                      INT 
          ))')[O]
		ON O.[Job_ID]=A.[Job_ID] AND A.[start_execution_date]>'2000-01-01' AND A.[job_history_id] is NULL
			AND A.[Start_execution_date]>DATEADD(dd,-30,getdate())
	LEFT JOIN [DATES][T] ON T.[job_id]=J.[Job_ID]
		AND T.[INSTANCE_ID]=(Select MAX([INSTANCE_ID]) From [TIMES] Where [Job_ID]=J.[Job_ID])
	-----------------------------------------------------------------
),[AVG_RUNTIME] As (
	SELECT [Job_ID],[Step_Name]
		,LEFT(CONVERT(time,CONVERT(datetime,AVG(CONVERT(float,CONVERT(datetime,isNULL([Run_Time],'00:00:00')))))),8)[Avg_RunTime]
	FROM [JOBS]
	WHERE [Run_Status]='Succeeded'
	GROUP BY [Job_ID],[Step_Name]
)
SELECT Top 10000
	[Server],J.[Job_ID],[Job_Name],[Instance_ID],[Run_Status]
	,[Run_DATE][Start_DATE],[Start_TIME],[End_TIME]
	,[Run_Time],isNULL([Avg_Runtime],'')[Avg_Runtime]
	,[Step_Num],J.[Step_Name],[Log_Msg]
FROM [JOBS][J]
LEFT JOIN [AVG_RUNTIME][A] ON A.[Job_ID]=J.[Job_ID] AND A.[Step_Name]=J.[Step_Name]
ORDER BY J.[Instance_ID] DESC;
GO

Rate

5 (1)

Share

Share

Rate

5 (1)