SCOM SQL Script to show gray agent data historical reasons

July 17, 2013 1:02 pm / 3 Comments /

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.

Posted in: SCOM Downloads

3 Thoughts on “SCOM SQL Script to show gray agent data historical reasons”

  1. Gene on August 8, 2013 at 5:19 pm said:

    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

    • admin on August 8, 2013 at 5:30 pm said:

      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.

  2. admin on September 6, 2013 at 3:13 pm said:

    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

Leave a Reply

Post Navigation