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