Heartbeat Failure and Failed to Connect Alerts with Duration

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 (https://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.

Comments

• Anonymous
  May 24, 2011
  woot! awesome query man. :)