• I added last run time and duration, and FQDN for msdb table to allow this to be used in a stored procedure or elsewhere than Query Analyser 😀 :

    select distinct j.Name as "Job Name", j.description as "Job Description", h.run_date as LastStatusDate, h.run_time as LastStatusTime, h.run_duration as LastStatusDuration,

    case h.run_status

    when 0 then 'Failed'

    when 1 then 'Successful'

    when 3 then 'Cancelled'

    when 4 then 'In Progress'

    end as JobStatus

    from msdb..sysJobHistory h, msdb..sysJobs j

    where j.job_id = h.job_id

    and h.step_id = 1

    and h.run_date =

    (select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id)

    and h.run_time =

    (select max(hj.run_time) from msdb..sysJobHistory hj where h.job_id = hj.job_id)

    order by 1