This is a great script I received from PSS working on a case.
Run against the DW and enter your server name near the bottom.
SELECT
ME.FullName,
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 (SR.StringResourceSystemName LIKE ‘System.Availability.StateData.Reasons.[0-9]%’)
AND DS.LanguageCode = ‘ENU’
AND ME.FullName like ‘%SERVER NAME%’ –Change name here or leave %% for ALL SERVERS
ORDER BY OutageStartDateTime
Result output will dispaly all of the reasons an agent went gray:
FullName OutageStartDateTime OutageDays ReasonCode ReasonString
Microsoft.SystemCenter.HealthService:ADPAPP 2012-12-23 04:59:41.300 206 0
The heartbeat from System Center Management Service is missing.
Can you give me the full query for this? The 2nd inner on seems to be missing the = and the REPLACE will not function as is here. Plus, there is double quotes that are missmatched…ETC.
Thanks,
Gene
Sorry Gene, I just looked thru my email and I do not have it. I’m not a SQL guru at all so I do not know what to correct for you here. This sucks as I need it too. I simply copied and pasted the working query into my post here, and apparnently it got managled somehow.
Lets try this again…
SELECT
ME.FullName,
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 (SR.StringResourceSystemName LIKE ‘System.Availability.StateData.Reasons.[0-9]%’)
AND DS.LanguageCode = ‘ENU’
AND ME.FullName like ‘%%’ –Change name here
ORDER BY OutageStartDateTime