A few years ago I wrote some TSQL to return all grey agents with the reason code. This worked fine in SCOM 2007, but it doesn’t work in 2012 environments for some reason. I basically just modified the WHERE clause, removing a bunch of SELECT statements – I’m not sure why I added those additional SELECT statements, but there must have been a reason.
I am reposting the TSQL here, updated for SCOM 2012. There was also a small bug fixed with the outage days column – my initial query did not use UTC time in the DATEDIFF calculation, which would cause a negative value for newly grey agents and was off n hours depending on your local time zone.
/* Gray agents with reason Jonathan Almquist (email@example.com) Updated 02-24-2014 */ USE OperationsManagerDW SELECT ME.Path, HSO.StartDateTime AS OutageStartDateTime, DATEDIFF (DD, HSO.StartDateTime, GETUTCDATE()) 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 DS.LanguageCode = 'ENU' ORDER BY OutageStartDateTime