Root Management Server Unavailable History with Reason

  • Article 
 /*
 RMS Unavailable History with Reason
 https://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

 

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.