Select sj.name as job, left (SJH_D.STEP_NAME, 20) AS [STEP NAME],
--What is it in englishcase sjh.run_status when 0 Then 'Failed' When 1 Then 'succeeded' when 2 Then 'Retry' When 3 Ten 'Canceled' Else 'Unknown'end As Status,
--Convert Integer Date To Regular DateTimesubstring (Cast), 5, 2) '/' Right (Cast (SJH.Run_DATE AS CHAR (8)), 2) '/' Left (sjh.run_date as char (8)), 4) AS [DATE]
- Change Run Time Into Something You Can Recognize (HH: MM: SS), Left (Right ('000000' Cast (SJH.Run_Time As Varchar (10)), 6), 2) ':' Substring ( Right ('000000' CAST (SJH.Run_Time As Varchar (10)), 6), 3, 2) ':' Right (Right ('000000' Cast (SJH.Run_Time As Varchar (10)), 6), 2) AS [Time]
--Rror Message, SJH_D.MESSAGE AS ERROR
From msdb.dbo.sysjobs sj --job id and name
--Job Historyinner Join Msdb.dbo.sysjobhistory SJHON SJ.JOB_ID = SJH.JOB_ID - Fail DetailsLeft Join Msdb.dbo.sysjobhistory SJH_DON SJH.JOB_ID = SJH_D.JOB_ID and SJH_D.STEP_ID> 0
--Only enabled jobswhere sj.enabled = 1 - ONLY JOB OUTCOME NOT Each Step Outcomeand SJH.STEP_ID = 0 - Only Failed or Cancelled Jobsand (SJH.RUN_STATUS = 0 or sjh.run_status = 3)
