Challenge Accepted

Once in a while, a disaster happens and when it happens, the first question will be which servers were affected?

Unfortunately, the answer to this question is not accurate. You are probably thinking that I am wrong. Since the “Failed to connect to computer” alert will pop up when the agent will stop sending a heartbeat for 3 seconds (the default value) and no ping answer from the Management Server. This is a great monitor, however, the issue comes up when the agent has some kind of a problem. Let’s say the agent had a problem before the disaster such as service being shut down or data being corrupted or even somehow the agent is deleted from the server. In these scenarios, the “Failed to connect to computer” alert will not be pop up. I’m sure many of the SCOM administrators are familiar with the problem.

This problem motivated me to create a new management pack.

My solution is based on SQL query and ping check from a management server.

The following steps will explain to you how to solve the problem:

First, get all the unhealthy agents that are not in maintenance mode. The reason for this is that the unhealthy agents are the only agents that could be affected by the disaster. Second, check the ping status from agent’s management server (Note: The management server itself can be shut down, therefore, if necessary we will check the ping from the secondary management server and if needed from the RMS server). The output will be stored as an event in the Operations Manager database. The third step is to run a simple SQL query that will return a list of servers that have been affected as a result of the disaster (I added a sample query at the end of this page).

While I was working on this management pack I had another idea, which was to repair the unhealthy agent automatically. Let me explain since we are already collecting unhealthy agents that answer to ping, we can easily reach the conclusion, that the problem is not a server. Therefore, trying to repair the agent is necessary. So to repair the agent, all we will do is run a task of restarting the agent. For the agents that have a problem with collecting performance data, we will run the flush agent task.

Link to the SCOM Administration Add-Ons.

SQL Query Example:

DECLARE @StartTime DateTime

DECLARE @EndTime DateTime




SET @StartTime =DATEADD(MINUTE, ((DATEPART(MINUTE,  GETUTCDATE()) / 5) * 5)-30, DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE()), 0))

SET @EndTime =  DATEADD(MINUTE, ((DATEPART(MINUTE,  GETUTCDATE()) / 5) * 5), DATEADD(HOUR, DATEDIFF(HOUR, 0, GETUTCDATE()), 0))




SELECT

       v.TimeGenerated

       ,DATEADD(MINUTE, ((DATEPART(MINUTE, v.TimeGenerated) / 5) * 5), DATEADD(HOUR, DATEDIFF(HOUR, 0, v.TimeGenerated), 0)) AS TimeGeneratedFixed

       ,EventParametersXML

       ,y.Status

       ,y.StatusCode

       ,y.ResponseTime

       ,y.AgentServerName

       ,y.ManagementServerName

INTO #EventAllView

FROM EventAllView v

OUTER APPLY (SELECT

              CAST(v.EventParameters AS XML) AS EventParametersXML) x

OUTER APPLY (SELECT

              x.value('Param[1]', 'VARCHAR(80)') AS Status

              ,x.value('Param[2]', 'VARCHAR(80)') AS StatusCode

              ,x.value('Param[3]', 'VARCHAR(80)') AS ResponseTime

              ,x.value('Param[4]', 'VARCHAR(80)') AS AgentServerName

              ,x.value('Param[5]', 'VARCHAR(80)') AS ManagementServerName

       FROM x.EventParametersXML.nodes('/') AS NodeValues (x)) y

WHERE

PublisherName = 'ServerConnectivityCheck'

AND TimeGenerated >= @StartTime

AND DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TimeGenerated), 0) <= @EndTime




;WITH TimesCTE ([Date])

AS

(

       SELECT

       @StartTime

       UNION ALL

       SELECT DATEADD(MINUTE, 5, [Date]) FROM TimesCTE WHERE [Date] < @EndTime

)

SELECT

res.AgentServerName,

res.[Date],

v.TimeGenerated,

v.Status,

v.StatusCode,

v.ResponseTime,

v.ManagementServerName into #tmpPivot

FROM

(

       SELECT

       srv.AgentServerName,

       c.[Date]

       FROM

       (

              SELECT DISTINCT AgentServerName FROM #EventAllView

       ) srv

       CROSS APPLY

       (

              SELECT [Date] FROM TimesCTE

       ) c   

) res

JOIN

#EventAllView v

       ON DATEADD(MINUTE, ((DATEPART(MINUTE, v.TimeGenerated) / 5) * 5), DATEADD(HOUR, DATEDIFF(HOUR, 0, v.TimeGenerated), 0)) = res.[Date]

       AND v.AgentServerName = res.AgentServerName

ORDER BY

res.AgentServerName,

res.[Date]







DECLARE @CMD VARCHAR(MAX)

SET @CMD = 'SELECT * FROM (SELECT AgentServerName, StatusCode, CONVERT(VARCHAR(80), DATEADD(HOUR, (DATEDIFF(HOUR, GETUTCDATE(), GETDATE())), [Date]), 108) AS [Date] FROM #tmpPivot) AS SourceTable PIVOT (MAX(StatusCode) FOR [Date] IN ('




SET @CMD = @CMD + (

       SELECT

       LEFT(txt.grouped, LEN(txt.grouped) - 1)

       FROM

       (

              SELECT

              '[' + CONVERT(VARCHAR(80), DATEADD(HOUR, (DATEDIFF(HOUR, GETUTCDATE(), GETDATE())), [Date]), 108) + '],' AS [text()]

              FROM

              #tmpPivot

              GROUP BY

              CONVERT(VARCHAR(80), DATEADD(HOUR, (DATEDIFF(HOUR, GETUTCDATE(), GETDATE())), [Date]), 108)

                       ORDER BY

                       CONVERT(VARCHAR(80), DATEADD(HOUR, (DATEDIFF(HOUR, GETUTCDATE(), GETDATE())), [Date]), 108)

              FOR XML PATH('')

       ) txt(grouped))




SET @CMD = @CMD + ')) AS PivotTable ORDER BY AgentServerName'




EXEC (@CMD)







DROP TABLE #EventAllView

DROP TABLE #tmpPivot