MSSQL Job(작업) 결과 조회 쿼리

Job 결과가 궁금할때 조회하는 쿼리 (수집용)

select job_id, name, step_id, step_name, subsystem, command, database_name,

       last_run_outcome, last_run_date,

       (substring(last_run_time, 1, 2) + ':' + substring(last_run_time, 3, 2) + ':' + substring(last_run_time, 5, 2)) as last_run_time,

       (substring(last_run_duration, 1, 2) + ':' + substring(last_run_duration, 3, 2) + ':' + substring(last_run_duration, 5, 2)) as last_run_duration,

       last_run_retries,

       sMessage

from (

       select a.job_id, name, step_id, step_name, subsystem, command, database_name,

             (case last_run_outcome when 0 then '실패'

                           when 1 then '성공' else '알수없음' end) as last_run_outcome,

             last_run_date,

             right('S000000' + convert(varchar(8), last_run_time), 6) as last_run_time,

             right('S000000' + convert(varchar(8), last_run_duration), 6) as last_run_duration,

             last_run_retries,

             (case when last_run_outcome = 0 then (

                    select top 1 [message]

                                 from msdb.dbo.sysjobhistory

                           where job_id = a.job_id and run_date = b.last_run_date and run_time = b.last_run_time

             ) else '' end) as sMessage

       from msdb.dbo.sysjobs a

             left join msdb.dbo.sysjobsteps b on a.job_id = b.job_id

       where enabled = 1

) as O

order by name, step_id