Job History

SELECT job.name As JobName,
history.step_name,
CAST(msdb.dbo.agent_datetime(history.run_date, history.run_time) AS DATE) AS Run_Start_Date,
msdb.dbo.agent_datetime(history.run_date, history.run_time) AS Run_Start_date_time,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 'Run_time',


STUFF(
STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'),
9,
0,
':'
) 'duration'
, msdb.dbo.fn_get_job_end_datetime(history.run_date, history.run_time, history.run_duration)
as Run_End_date_time,

DATENAME(weekday,CAST(msdb.dbo.agent_datetime(run_date, run_time) AS DATE)) AS Run_DayofWeek,

history.step_name,
Case
When history.run_status = 0 then 'Failed'
When history.run_status = 1 then 'Succeeded'
When history.run_status = 2 then 'Retry'
When history.run_status = 3 then 'Canceled'
end as Run_status,
message,
instance_id,
step.command,
history.job_id,
history.step_id,
sql_message_id,
sql_severity,

--run_date,
--run_time,
--run_duration,
AVG(history.run_duration) OVER (PARTITION BY history.job_id) AS AverageRuntime,
MAX(history.run_duration) OVER (PARTITION BY history.job_id) AS MaxRuntime,
MIN(history.run_duration) OVER (PARTITION BY history.job_id) AS MinRuntime,
 history.server
FROM msdb..sysjobhistory history
 JOIN msdb..sysjobs job
ON job.job_id = history.job_id
left outer JOIN msdb..sysjobsteps step
ON step.job_id = job.job_id
AND step.step_id = job.start_step_id
WHERE
--history.step_id = 0 AND
CAST(msdb.dbo.agent_datetime(run_date, run_time) AS DATE) >= GETDATE() - 3
and job.name not in ('DevOps - PerfMon Counter Collection' , 'Shrink_All_Log_Files', 'DevOps - Load Session Status per minute', 'DevOps - Update Stats USER_DATABASES', 'DevOps - Load SystemHealthSession' , 'syspolicy_purge_history', 'SSIS Server Maintenance Job', 'Database_File_Growth_Data_Collection') --- filter out noise
ORDER BY Run_Start_date_time  DESC;