Command Shell Examples
Useful SQL Queries
Gray Agents With Reason - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Gray Agents With Reason

Gray Agents With Reason

  • Comments 2
  • Likes
/*
Gray agents with reason
http://blogs.technet.com/b/jonathanalmquist/
02-26-2011
*/
SELECT
    ME.Path,
    HSO.StartDateTime AS OutageStartDateTime,
    DATEDIFF (DD, hso.StartDateTime, GETDATE()) AS OutageDays,
    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 (HSO.EndDateTime IS NULL)
    AND (SR.StringResourceSystemName LIKE 'System.Availability.StateData.Reasons.[0-9]%')
    AND    (HSO.StartDateTime =
            (SELECT MAX(StartDateTime) AS Expr1
            FROM   vHealthServiceOutage AS HSO2
            WHERE (ManagedEntityRowId = HSO.ManagedEntityRowId) AND (EndDateTime IS NULL)))
    AND (HSO.StartDateTime >
            (SELECT MAX(EndDateTime) AS Expr1
            FROM   vHealthServiceOutage AS HSO2
            WHERE (ManagedEntityRowId = HSO.ManagedEntityRowId)))
ORDER BY OutageStartDateTime

 

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
  • Jonathon,

    Have you seen a situation where the duration is a negative number?

  • Joe - I recall seeing this once before, but cannot recall what it was that caused this.  Maybe some date-time discrepencies on the agent machine, or possibly my TSQL is missing something...