PI Services

/**** Unreachable Computer Event from Specific Group  ****/
 
Use OperationsManagerDW
 
DECLARE @startdate     datetime
DECLARE @enddate      datetime
DECLARE @computergroup1     VARCHAR(50)
 
SET @startdate = '2017-01-01 00:00:00'
SET @enddate = '2017-01-31 00:00:00'
SET @computergroup1 = 'My_Group'
 
 
SELECT 
apv.ParameterValue as SystemName
,(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) as DownDateTime, 
(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),arsv.StateSetDateTime)) as RestoredDateTime,
adv.CustomField2 as OutageType, 
adv.CustomField3 as RootCause, 
adv.CustomField4 as Reason,
adv.DBLastModifiedByUserId as UserID
FROM Alert.vAlert av
JOIN Alert.vAlertDetail adv on av.AlertGuid = adv.AlertGuid
JOIN Alert.vAlertResolutionState arsv on av.AlertGuid = arsv.AlertGuid
JOIN Alert.vAlertParameter apv on av.AlertGuid = apv.AlertGuid
WHERE AlertName = 'Failed to Connect to Computer'
AND arsv.ResolutionState = '255'
--AND adv.CustomField2 IS NOT NULL
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),av.RaisedDateTime)) between @startdate and @enddate
AND apv.ParameterValue IN (
SELECT vManagedEntity.DisplayName
FROM  vManagedEntity 
INNER JOIN vRelationship ON vManagedEntity.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId 
INNER JOIN vManagedEntity AS ManagedEntity_1 ON vRelationship.SourceManagedEntityRowId = ManagedEntity_1.ManagedEntityRowId
WHERE (ManagedEntity_1.DisplayName = @computergroup1)
)
 
UNION ALL
 
SELECT
vme.displayname,
(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vmm.StartDateTime)) as DownDateTime, 
(DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vmm.EndDateTime)) as RestoredDateTime,
'OutageType' =
CASE
  vmm.PlannedMaintenanceInd
  WHEN '1' THEN 'Scheduled'
  WHEN '0' THEN 'Unscheduled'
END,
'RootCause' =
CASE
  vmmh.ReasonCode
  WHEN '0' THEN 'Other (Planned)'
  WHEN '1' THEN 'Other (Unplanned)'
  WHEN '2' THEN 'Hardware: Maintenance (Planned)'
  WHEN '3' THEN 'Hardware: Maintenance (Unplanned)'
  WHEN '4' THEN 'Hardware: Installation (Planned)'
  WHEN '5' THEN 'Hardware: Installation (Unplanned)'
  WHEN '6' THEN 'Operating System: Reconfiguration (Planned)'
  WHEN '7' THEN 'Operating System: Reconfiguration (Unplanned)'
  WHEN '8' THEN 'Application: Maintenance (Planned)'
  WHEN '9' THEN 'Application: Maintenance (Unplanned)'
  WHEN '10' THEN 'Application: Installation (Planned)'
  WHEN '11' THEN 'Application: Unresponsive'
  WHEN '12' THEN 'Application:  Unstable'
  WHEN '13' THEN 'Security Issue'
  WHEN '14' THEN 'Loss of network connectivity (Unplanned)'
END,
vmmh.Comment as Reason,
vmmh.userid as UserID
FROM vMaintenanceMode vmm
JOIN vManagedEntity vme on vmm.managedentityrowid = vme.managedentityrowid
JOIN vMaintenanceModeHistory vmmh on vmm.maintenancemoderowid = vmmh.maintenancemoderowid
WHERE vme.FullName LIKE '%HealthServiceWatcher%'
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vmm.StartDateTime)) between @startdate and @enddate
AND vme.displayname IN (
SELECT vManagedEntity.DisplayName
FROM  vManagedEntity 
INNER JOIN vRelationship ON vManagedEntity.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId 
INNER JOIN vManagedEntity AS ManagedEntity_1 ON vRelationship.SourceManagedEntityRowId = ManagedEntity_1.ManagedEntityRowId
WHERE (ManagedEntity_1.DisplayName = @computergroup1)
)
ORDER BY DownDateTime DESC

/****** Scom Agent Extended Infos ******/
 
Use OperationsManager
 
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() 
)
,
FailoverRelation (SourceEntityId,agent,FailoverServer,TargetEntityId)
AS
(
SELECT R.SourceEntityID,SourceBME.DisplayName as Agent,TargetBME.DisplayName as FailoverServer, 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_MicrosoftSystemCenterHealthServiceSecondaryCommunication() 
)
 
 
SELECT 
                   MTV_HS.[DisplayName]
                  ,PrimaryRelation.PrimaryServer
                  ,FailoverServer
                  ,Failover_State = CASE
                               WHEN FailoverRelation.FailoverServer IS NULL THEN 'NO FAILOVER'
                               ELSE FailoverRelation.FailoverServer
                               END
                  ,[Port]
      ,[InstallTime]
      ,[MaximumQueueSize]
      ,Patch = CASE 
                    WHEN [PatchList] like '%UR4%' THEN 'RU4'
                               WHEN [PatchList] like '%UR8%' THEN 'RU8'
                               WHEN [PatchList] like '%UR11%' THEN 'RU11'
                               WHEN [PatchList] = '' THEN '[NO_DATA]'
                               END
      ,[IsManuallyInstalled]
      ,[Version]
      ,[ActionAccountIdentity]
      ,[ProxyingEnabled]
      ,[HeartbeatInterval]
      ,[NumberOfMissingHeartBeatsToMarkMachineDown_27AD2E30_EFE0_1A73_8C9D_F0A22B073227] as NumberOfMissingHeartBeatsToMarkMachineDown
                  ,MTV_OS.DisplayName as OS
                  ,MTV_OS.CSDVersion_AFE62B62_74FC_2F06_D8A0_DEE31F14CD33 as ServicePack
                  ,MTV_OS.LogicalProcessors_5CAE4847_F75B_01D0_156E_1658D557B739 as Logic_CPU
                      
  FROM [OperationsManager].[dbo].[MTV_HealthService] as MTV_HS
  INNER JOIN  [dbo].[MTV_Microsoft$Windows$OperatingSystem] as MTV_OS on MTV_HS.DisplayName = MTV_OS.PrincipalName
 
  INNER JOIN PrimaryRelation on PrimaryRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
  LEFT OUTER JOIN FailoverRelation on FailoverRelation.SourceEntityId = MTV_HS.BaseManagedEntityId
    
  WHERE IsAgent = '1'