Gray Agents With Reason

 /*
 Gray agents with reason
 https://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.

Comments

• Anonymous
  January 01, 2003
  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...

• Anonymous
  November 12, 2011
  Jonathon, Have you seen a situation where the duration is a negative number?