Root Management Server Unavailable History with Reason

/*

RMS Unavailable History with Reason

http://blogs.technet.com/b/jonathanalmquist/

04-19-2011

Updated 04-20-2011 with outage durations dd:hh:mm

*/

SELECT

    ME.Path,

    HSO.StartDateTime AS OutageStartDateTime,

    CAST (DATEDIFF(SECOND, HSO.StartDateTime, HSO.EndDateTime)/86400 AS VARCHAR(5)) + ':' + 

        CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, HSO.StartDateTime, HSO.EndDateTime), ''), 114) AS 'Duration (dd:hh:mm)',

    HSO.ReasonCode,

    DS.Name AS ReasonString

FROM  vManagedEntity AS ME INNER JOIN

    vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId = ME.ManagedEntityRowId INNER JOIN

    vStringResource AS SR ON HSO.ReasonCode = 

    REPLACE(LEFT(SR.StringResourceSystemName, LEN(SR.StringResourceSystemName)

        - CHARINDEX('.', REVERSE(SR.StringResourceSystemName))), 'System.Availability.StateData.Reasons.', '') INNER JOIN

    vDisplayString AS DS ON DS.ElementGuid = SR.StringResourceGuid

WHERE (SR.StringResourceSystemName LIKE 'System.Availability.StateData.Reasons.[0-9]%') AND

        (ME.Path in (

            SELECT DISTINCT Path

            FROM vManagedEntity

            WHERE FullName LIKE 'Microsoft.SystemCenter.NotificationServer:%')

            )

ORDER BY OutageStartDateTime DESC