;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

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