Job Status Script

select b.name,a.step_name,

c.message,

case  c.run_status when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'In progress'

else 'NO STATUS' END as status

,substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6), 1, 2)

            +    ':'

            + substring (

            right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.run_time), 6) ,3 ,2)

            +    ':'

            + substring (

            right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6) ,5 ,2) as run_time

,substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8), 1, 4)

            +    '/'

            + substring (

            right (stuff (' ', 1, 1, '00000000') + convert(varchar(8), c.run_date), 8) ,5 ,2)

            +    '/'

            + substring (

            right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8) ,7 ,2) as run_date

from msdb..sysjobsteps a, msdb..sysjobhistory c,msdb..sysjobs b

where a.step_id=c.step_id and a.job_id=c.job_id

and

a.last_run_date=c.run_date

and

a.last_run_time=c.run_time

and a.job_id=b.job_id