La requête ci-dessous permet de lister depuis la base OperationsManager, avec un paramètre J-n, les alertes Heartbeat Failure des agents, par Primary Server, en affichant la durée de l’alerte, son eventuel ticket associé, son status et le status du maintenance mode de l’objet.
/****** HEARTBEAT FAILURE FOR AGENTS PRIMARY MANAGED BY SPECIFIC MS WITH DURATION, STATUS AND OBJECT MAINTENANCE MODE STATUS ******/
Use OperationsManager
DECLARE @PrimaryServer VARCHAR(50)
DECLARE @startdate datetime
DECLARE @Offset int
DECLARE @enddate datetime
DECLARE @seconds int
DECLARE @AlertPattern VARCHAR(50)
DECLARE @TicketPattern VARCHAR(50)
SET @PrimaryServer = 'MyPrimaryMS' – Provide a name of Primary if you need to filter on.
SET @enddate = GETDATE()
SET @Offset = 4 – Provide number of days to look back (limited to operational db retention
SET @startdate = DATEADD(day,-@Offset,@enddate)
SET @AlertPattern = 'heartbeat'
SET @TicketPattern = 'Ticket_Number' -- Provide string pattern that must be found in each ticket
;
WITH
PrimaryRelation (SourceEntityId,agent,PrimaryServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as PrimaryServer, R.TargetEntityID
FROM Relationship R WITH (NOLOCK)
JOIN BaseManagedEntity SourceBME
ON R.SourceEntityID = SourceBME.BaseManagedEntityID
JOIN BaseManagedEntity TargetBME
ON R.TargetEntityID = TargetBME.BaseManagedEntityID
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication()
AND TargetBME.IsDeleted <> '1' -- AND THE PRIMARY SERVER IS NOT WAITING TO BE DELETED
AND SourceBME.IsDeleted <> '1' -- AND THE AGENT IS NOT WAITING TO BE DELETED
)
-- Get alert
SELECT
PrimaryServer
,AlertStringName
,MonitoringObjectDisplayName AS Computer
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)) as DownDateTime
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)) as RestoredDateTime
,Duration = (
SELECT CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) / 86400 ) + ' days ' +
CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) % 86400 / 3600) + ' hours ' +
CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) % 86400 % 3600 / 60) + ' min ' +
CONVERT(varchar, (DateDiff (s, (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)), (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeResolved)))) % 86400 % 3600 % 60 % 60) + ' sec '
)
,TicketID = CASE
WHEN TicketID is null THEN 'NO_TICKET'
WHEN TicketId not like '%'+@TicketPattern+'%' THEN 'NO_TICKET'
ELSE TicketID
END
,Alert_ResolutionState = CASE
WHEN AV.ResolutionState = '255' THEN 'Closed'
WHEN AV.ResolutionState = '254' THEN 'Resolved'
WHEN AV.ResolutionState = '251' THEN 'Blackout'
WHEN AV.ResolutionState = '250' THEN 'Scheduled'
WHEN AV.ResolutionState = '249' THEN 'Acknowledge'
WHEN AV.ResolutionState = '248' THEN 'Assigned to Engineering'
WHEN AV.ResolutionState = '247' THEN 'Awaiting Evidence'
WHEN AV.ResolutionState = '3' THEN 'Notified'
WHEN AV.ResolutionState = '0' THEN 'New'
END
,'Object_was_InMM' = CASE
WHEN av.MonitoringObjectInMaintenanceMode = '0' THEN 'NO'
WHEN av.MonitoringObjectInMaintenanceMode = '1' THEN 'YES'
END
FROM [dbo].[AlertView] av
INNER JOIN [OperationsManager].[dbo].[MTV_HealthService] as MTV_HS on MTV_HS.DisplayName = MonitoringObjectDisplayName
INNER JOIN PrimaryRelation on PrimaryRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
WHERE name like '%'+@AlertPattern+'%'
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.TimeRaised)) between DATEADD(day,-@Offset,@enddate) and @enddate
--AND PrimaryServer like '%'+@PrimaryServer+'%' -- Uncomment to see agents managed from specific primary server