Checking current job status from the SQL Server Agent

Wrote a bit of code for the LinkedIn nagios users group and figured i'd post it here as well. This reports back the current status of all enabled jobs within SQL server at the time of running. It can be used to identify failed, hung or long running jobs. Tested on SQL 2000, 2005 and 2008.

SELECT

[sj_].[name]

,[sjh].[run_status]

,[status] = CASE [sjh].[run_status]

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry (step only)'

WHEN 3 THEN 'Canceled'

WHEN 4 THEN 'In-progress message'

WHEN 5 THEN 'Unknown'

END

FROM [msdb]..[sysjobs] [sj_]

INNER JOIN [msdb]..[sysjobhistory] [sjh] ON [sj_].[job_id] = [sjh].[job_id]

WHERE

[sj_].[enabled] = 1 AND

[sjh].[step_id] = 0 AND

[sjh].[instance_id] = (SELECT MAX([instance_id]) FROM [msdb]..[sysjobhistory] WHERE [job_id] = [sj_].[job_id])

ORDER BY

[sj_].[name]