Command Shell Examples
Useful SQL Queries
Heartbeat Failure and Failed to Connect Alerts with Duration - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Heartbeat Failure and Failed to Connect Alerts with Duration

Heartbeat Failure and Failed to Connect Alerts with Duration

  • Comments 1
  • Likes

I find this especially useful in determining out of box heartbeat settings, since the default 3 minutes is almost never an optimal setting.  Without the information returned from this query, I generally suggest 9 minutes out of box (adjusting the agent interval from 60 to 180 seconds).

/*
Heartbeat Failure and Failed to Connect with Duration
Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/)
05-13-2011
*/
 
DECLARE @MGID AS INT,
    @TimeZoneOffset AS INT,
    @OffSetDays AS INT,
    @StartDate AS DATETIME,
    @EndDate AS DATETIME,
    @Computer AS VARCHAR(MAX)
    
SET @MGID = 1
SET @TimeZoneOffset = 5
SET @OffSetDays = 60
SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE()))
SET @EndDate = DATEADD(hour, @TimeZoneOffset, GETDATE())
SET @Computer = 'computer.domain.com'
 
SELECT DISTINCT 
        vALERT.AlertName AS Alert,
        vME.DisplayName AS Computer,
        vALERT.RaisedDateTime AS Raised, 
        CASE vRES.ResolutionStateName
            WHEN 'New' THEN DATEDIFF(MINUTE, vALERT.RaisedDateTime, GETDATE())
            WHEN 'Closed' THEN DATEDIFF(MINUTE, vALERT.RaisedDateTime, vSTATE.StateSetDateTime)
            ELSE DATEDIFF(MINUTE, vALERT.RaisedDateTime, GETDATE())
        END AS Duration, 
        vRES.ResolutionStateName AS 'Resolution State',
        vSTATE.StateSetByUserId AS 'Last Modified By'
FROM  Alert.vALERT AS vALERT LEFT OUTER JOIN
        (SELECT AlertGuid, ResolutionState,
                CAST(StateSetDateTime AS DATETIME) AS StateSetDateTime,
                CAST(StateSetByUserId AS VARCHAR) AS StateSetByUserId
        FROM   Alert.vALERTResolutionState AS A
        WHERE
                (StateSetDateTime =
                   (SELECT MAX(StateSetDateTime) AS Expr1
                    FROM   Alert.vALERTResolutionState AS B
                    WHERE (A.AlertGuid = AlertGuid))) AND
                (ResolutionState =
                   (SELECT MAX(ResolutionState) AS Expr1
                    FROM   Alert.vALERTResolutionState AS B
                    WHERE (A.AlertGuid = AlertGuid)))) AS vSTATE ON vALERT.AlertGuid = vSTATE.AlertGuid INNER JOIN
                   vResolutionState AS vRES ON vSTATE.ResolutionState = vRES.ResolutionStateId INNER JOIN
                   vManagedEntity AS vME ON vALERT.ManagedEntityRowId = vME.ManagedEntityRowId
WHERE --(vME.DisplayName = @Computer) AND
        (vALERT.AlertName IN ('Health Service Heartbeat Failure', 'Failed to Connect to Computer')) AND
        (vALERT.RaisedDateTime BETWEEN @StartDate AND @EndDate)
ORDER BY Duration DESC

Note: Uncomment the first WHERE clause to filter specific computer.  This could be used in linked report dataset.

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • woot! awesome query man. :)