Command Shell Examples
Useful SQL Queries
Root Management Server Unavailable History with Reason - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Root Management Server Unavailable History with Reason

Root Management Server Unavailable History with Reason

  • Comments 2
  • Likes
/*
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

 

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

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • Getting the following syntax error while executing this query against DW DB, am missing anything?

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near 'SR'.

    Msg 102, Level 15, State 1, Line 9

    Incorrect syntax near ')'.

  • @Ramesh - Please use the link at end of post to download the formatted TSQL.  There are problems with copy/paste from the code snippet window.