VIEW to list running and completed jobs

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

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