Job History

1

Hi all,

I'm trying to write a query that will return the last job history for all jobs on my server.

The columns I am looking for are Job_Name, Last Run Date, Last Run Time, Status (success/failure)

2

you can start with this

Use msdb
go
select j.name, h.step_name, h.run_date, 
		case h.run_status
			when 0 then 'Failed'
			when 1 then 'Succeeded'
			when 2 then 'Retry'
			when 4 then 'Canceled'
			when 5 then 'InProgess'
		else
			'Unknown'
		end as Status
  from MSDB.dbo.sysjobhistory h
	join MSDB.dbo.sysjobs j
		on h.Job_Id = j.Job_ID
3 
SELECT 
    j.name AS Job_Name, 
    STUFF(STUFF(query1.run_date, 7, 0, '-'), 5, 0, '-') AS Last_Run_Date,
    STUFF(STUFF(RIGHT('000000' + CAST(query1.run_time AS varchar(6)), 6)
        , 5, 0, ':'), 3, 0, ':') AS Last_Run_Time,
    CASE query1.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In progress'
        ELSE 'Unknown'
    END AS Status
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY jh.job_id 
        ORDER BY run_date DESC, run_time DESC) AS row_num
    FROM msdb.dbo.sysjobhistory jh
    WHERE jh.step_id = 0
) AS query1
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = query1.job_id
WHERE query1.row_num = 1 AND j.enabled = 1
ORDER BY query1.run_status, j.name
4

Many Thanks Scott!

That's just what I was looking for.

5

You're welcome!