Nice one. Where was this a couple of weeks ago when I was looking to build a job dashboard.

I actually ended up taking things to bits, breaking it down and building it back up in pieces.

I used far more CTEs than I needed but at the time needed to see what was going on.

I actually did the dashboard in PHP because I am just getting to grips with .net and had all the fancy stuff for hover tips and expanding sections already.

It doesn't have a grouped history as with the article though. Just displays all currently active jobs, and looks at the status of their most recent run.

;WITH fullJobHistory AS

(

--Just so I can use a nice date everywhere else

SELECT CAST(CAST(run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(run_time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS datetime) AS RunDate,

*

FROM msdb.dbo.sysjobhistory

),

baseJobRecord AS

(

--the top level entries and number them in reverse

SELECT ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY Rundate DESC) AS [jobNumber],

*

FROM fullJobHistory

WHERE step_id=0

)

,

stepFailures AS

(

--get the failure messages for each failed bit

SELECT o.job_id,

o.jobNumber,

f.[message] AS [stepError]

FROM baseJobRecord o

LEFT JOIN baseJobRecord o2

ON o2.job_id = o.job_id

AND o2.jobNumber = o.jobNumber - 1

JOIN fullJobHistory f

ON f.job_id = o.job_id

AND f.step_id > 0

AND f.run_status = 0

AND (o2.instance_id IS NULL

OR f.instance_id BETWEEN o.instance_id AND o2.instance_id)

WHERE o.run_status = 0

AND o.jobNumber = 1

GROUP BY o.job_id,

o.jobNumber,

f.[message]

)

--bung it all together in an output.

SELECT DISTINCT

o.run_status AS [Status],

j.name AS [Job Name],

o.RunDate AS [Last Ran],

--concatenate individual step errors

--using br/hr here since it is for a web tooltip

STUFF((SELECT '<br /><hr />' + CASE

WHEN o2.run_status = 1 THEN NULL

ELSE isnull(s.stepError,o2.[message])

END

FROM baseJobRecord o2

LEFT JOIN stepFailures s

ON s.job_id = o2.job_id

AND s.jobNumber = o2.jobNumber

WHERE o2.job_id = o.job_id

AND o2.jobNumber = o.jobNumber

FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,12,'')

AS [Error]

FROM msdb.dbo.sysjobs j

JOIN baseJobRecord o

ON o.job_id = j.job_id

AND o.jobnumber = 1

WHERE j.enabled = 1

ORDER BY 1,2

Will definitely be looking at your Reporting services bits though.