PI Services

/* All Overrides for Rules of language code EUN and FRA, with Original MP and Rule, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Rule_MP_Name
,rv.DisplayName as Rule_Name
,IsEnabledByDefault = CASE
    WHEN rv.Enabled = '0' THEN 'NO'
    WHEN rv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM ModuleOverride mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN ruleview rv on rv.Id = mo.ParentId 
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = rv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND rv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')

--ORDER BY mpv2.DisplayName

/*All Overrides for Monitor of language code EUN and FRA, with Original MP and Monitor, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Monitor_MP_Name
,mv.DisplayName as Monitor_Name
,IsEnabledByDefault = CASE
    WHEN mv.Enabled = '0' THEN 'NO'
    WHEN mv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM MonitorOverride  mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN monitorview mv on mv.Id = mo.MonitorId
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = mv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND mv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')
ORDER BY mpv2.DisplayName

-- All Overrides for Discoveries of language code EUN and FRA, with Original MP and Discovery, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Discovery_MP_Name
,dv.DisplayName as Discovery_Name
,mo.ParentType
,IsEnabledByDefault = CASE
    WHEN dv.Enabled = '0' THEN 'NO'
    WHEN dv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM ModuleOverride mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN DiscoveryView dv on dv.Id = mo.ParentId 
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = dv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND dv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')
AND mo.ParentType = 'Discovery'
ORDER BY mpv2.DisplayName

-- QUERY TO DISPLAY ALL RULES WITH TARGET CLASSES AND THEIR OVERRIDE STATE (ANYWAY MORE THAN 1 OVERRIDE PER RULE) - (FILTERED ON 'ENU' AND 'FRA' LANGUAGES)
-- THE FINAL 'GROUP BY' CLAUSE IS TO DISPLAY ONLY ONE RULE OCCURENCE (WE ONLY WANT TO KNOW THAT RULE IS OVERRIDEN AT LEAST ONE TIME.
-- THAT IS EQUIVALENT TO DELETE THE 'ALL' STATEMENT IN UNION CLAUSE.

PRINT 'ALL RULES THAT IS OVERRIDEN AT LEAST 1 TIME OR NOT';

WITH TEMP1 (Rule_Name,Target_Class,Override_State)
AS
(
SELECT rv.DisplayName as Rule_Name
,MTV.DisplayName as Target_Class
,Override_State =  CASE
    WHEN mo.ParentId is null THEN 'NOT OVERRIDEN'
    WHEN mo.ParentId is not null THEN 'OVERRIDEN'
    END
FROM RuleView rv
FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId
INNER JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id
WHERE rv.Id  not in (select mo.ParentId from ModuleOverride mo)
AND rv.LanguageCode in ('ENU','FRA')
AND MTV.LanguageCode in ('ENU','FRA')

UNION ALL

SELECT rv.DisplayName as Rule_name
,MTV.DisplayName as Target_Class
,Override_State =  CASE
    WHEN mo.ParentId is null THEN 'NOT OVERRIDEN'
    WHEN mo.ParentId is not null THEN 'OVERRIDEN'
    END
FROM RuleView rv
FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId
JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id
WHERE rv.Id  in (select mo.ParentId from ModuleOverride mo)
AND rv.LanguageCode in ('ENU','FRA')
AND MTV.LanguageCode in ('ENU','FRA')
)

SELECT Rule_Name
,Target_Class
,Override_State
 FROM TEMP1
 GROUP BY Rule_Name,Target_Class,Override_State
 ORDER BY Rule_Name
 

###########################################################################################################################################
## SCRIPT TO START/STOP/DISPLAY MAINTENANCE MODE FOR HEALTH SERVICE WATCHER OBJECTS THAT DEPENDS FROM SPECIFIC MANAGEMENT SERVER OR GATEWAY
## Author: CJOURDAN
###########################################################################################################################################
#Parameters
    # $MGroup : Management Group to connect to
    # $MStoConnect : Management server to connect to
    # $PrimMS : Management server or Gateway FQDN from which depend agents.
    # $Reason : Reason for Maintenance Mode (Valid Reason are: PlannedOther
    #                                                          UnPlannedOther
    #                                                          PlannedHardwareMaintenance 
    #                                                          UnplannedHardwareMaintenance 
    #                                                          PlannedHardwareInstallation
    #                                                          UnplannedHardwareInstallation
    #                                                          PlannedOperatingSystemReconfiguration
    #                                                          UnplannedOperatingSystemReconfiguration
    #                                                          PlannedApplicationMaintenance
    #                                                          ApplicationInstallation
    #                                                          ApplicationUnresponsive
    #                                                          ApplicationUnstable
    #                                                          SecurityIssue
    #                                                          LossOfNetworkConnectivity)
    # 
    # $Comment : Comment for Maintenance Mode
    # $MMAction : START or STOP OR DISPLAY Maintenance Mode
    # $MMDuration : Maintenance Mode Duration in minutes (only valuable for START $MMAction)

    # EXAMPLES:
    # Display Health Service Watcher Maintenance Mode Status for agents primary managed by MyPriMaryMS.mydomain.home
        # .\ScomSwitchMM.ps1 -MGroup MyMGROUP -MStoConnect MyMS.mydomain.home -PrimMS MyPriMaryMS.mydomain.home -MMAction display
    # Start Health Service Watcher Maintenance Mode for agents primary managed by MyPriMaryMS.mydomain.home during 60 min
        # .\ScomSwitchMM.ps1 -MGroup MyMGROUP -MStoConnect MyMS.mydomain.home -PrimMS MyPriMaryMS.mydomain.home -MMAction start -MMDuration 60






Param(
[Parameter(Mandatory=$false)]
$ClassName = "Health Service Watcher",
[Parameter(Mandatory=$false)]
$MGroup = "MyMGROUP",
[Parameter(Mandatory=$false)]
$MStoConnect= "MyMS.mydomain.home",
[Parameter(Mandatory=$true)]
$PrimMS= "MyPriMaryMS.mydomain.home",
[Parameter(Mandatory=$false)]
$Reason = "PlannedOther",
[Parameter(Mandatory=$false)]
$Comment = "",
[Parameter(Mandatory=$true)]
$MMAction = $(Read-Host -Prompt "PROVIDE TYPE OF MAINTENANCE MODE ACTION: START or STOP OR DISPLAY"),
[Parameter(Mandatory=$false)]
# $MMDuration in minutes
$MMDuration = 0
)



if ($MMAction -notmatch ".*STOP.*|.*START.*|.*DISPLAY.*")
{
write-host -ForegroundColor Yellow "YOU MUST PROVIDE VALID MAINTENANCE MODE ACTION TYPE: START or STOP or DISPLAY"
EXIT 1
}

$cred = $(Get-Credential -Credential "MyDomain\")

    


#Import du module SCOM
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Error during import of SCOM PS Module"
EXIT 1
}


#Connection au management group $MGroup
try
{
New-SCOMManagementGroupConnection -ComputerName $MStoConnect -Credential $cred
}
catch
{
write-host -ForegroundColor red "Error during connection to MS $MStoConnect - CHECK CREDENTIALS USED"
EXIT 1
}



# Get Scom agents that have $PrimMS as Primary Management Server
$agents = get-scomagent | where {$_.PrimaryManagementServerName -eq $PrimMS}

if (!($agents))
    {
    write-host "NO SCOM AGENTS FOUND FOR SERVER $PrimMS - CHECK THAT THE NAME IS CORRECT (YOU MUST PROVIDE FQDN) OR THAT THIS SERVER BELONG TO $MGroup MANAGEMENT GROUP"
    EXIT 1
    }

# Get "Health Service Watcher" Class Instances
$class = Get-SCOMClass -DisplayName $ClassName




switch($MMAction)
    {
    START
                          {


                            # Get instances of "Health Service Watcher" Class
                            $ClassInstances = Get-SCOMClassInstance -Class $class

                            # Class to put in MM Table
                            $ClassInstToMM = @()

                            # Get class instances that match agents
                            foreach ($agent in $agents)
                            { 
                            $ClassInstToMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                            }


                            $starttime = $(get-date).ToUniversalTime()  
                            $endtime = $(get-date).AddMinutes($MMDuration).ToUniversalTime()

                            # PUT IN Maintenance Mode
                            $($ClassInstToMM | foreach {$_.schedulemaintenancemode($starttime,$endtime,$Reason,$Comment)})


                            # Display effective instances in MM
                            write-host "EFFECTIVE INSTANCES OF CLASS '$ClassName' IN MAINTENANCE MODE (Primary Managed by $PrimMS):"

                                # Get instances of "Health Service Watcher" Class
                                $ClassInstances = Get-SCOMClassInstance -Class $class

                                $ClassInstInMM = @()

                                # Get class instances that match agents
                                foreach ($agent in $agents)
                                { 
                                $ClassInstInMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                                }

                                $ClassInstInMM

                            }

    STOP
                                 {


                            # Get instances of "Health Service Watcher" Class
                            $ClassInstances = Get-SCOMClassInstance -Class $class

                            # Class to put out of MM Table
                            $ClassInstOoMM = @()

                            # Get class instances that match agents
                            foreach ($agent in $agents)
                            { 
                            $ClassInstOoMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                            }

                            # Only Endttime in necessary and it is now
                            $endtime = $(get-date).ToUniversalTime()  
                            

                            # PUT IN Maintenance Mode
                            $($ClassInstOoMM | foreach {$_.stopmaintenancemode($endtime)})


                            # Display effective instances in MM
                            write-host "EFFECTIVE INSTANCES OF CLASS '$ClassName' OUT OF MAINTENANCE MODE (Primary Managed by $PrimMS):"

                                # Get instances of "Health Service Watcher" Class
                                $ClassInstances = Get-SCOMClassInstance -Class $class

                                $ClassInstOoMM = @()

                                # Get class instances that match agents
                                foreach ($agent in $agents)
                                { 
                                $ClassInstOoMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                                }

                                $ClassInstOoMM

                            }


    DISPLAY                  
    
                                {

                            # Display effective instances in MM
                            write-host "`n INSTANCES OF CLASS '$ClassName' MAINTENANCE MODE STATE (Primary Managed by $PrimMS):"

                                # Get instances of "Health Service Watcher" Class
                                $ClassInstances = Get-SCOMClassInstance -Class $class

                                $ClassInstMM = @()
                                
                                # Get class instances that match agents
                                foreach ($agent in $agents)
                                { 
                                $ClassInstMM += ($ClassInstances | Where-Object {$_.DisplayName -eq $agent.displayname})
                                }

                                $ClassInstMM

                                }



    }

declare @StartDate  datetime
declare @EndDate    datetime
set @EndDate = GETDATE()
set @StartDate = DATEADD(day,-60,@EndDate)
 
SELECT DISTINCT MP.MPFriendlyName AS 'Management Pack'
, aov.ParentType AS 'Type'
,(CASE 
       WHEN AOV.OverrideType = 'RuleProperty' THEN R.RuleName 
       WHEN AOV.OverrideType = 'MonitorProperty' THEN M.MonitorName 
       WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.RuleName 
       WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.MonitorName 
 END) AS 'NameType',
mt.DisplayName AS ContextDisplayName
, aov.OverrideableParameterName
, aov.Value,
(CASE Enforced 
       WHEN '0' THEN 'False' 
       WHEN '1' THEN 'True' 
  END) AS 'Enforced'
  , aov.LastModified
  , aov.TimeAdded
  ,(CASE 
  WHEN AOV.OverrideType = 'RuleProperty' THEN 
             (CASE R.RuleEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
 
  WHEN AOV.OverrideType = 'MonitorProperty' THEN 
             (CASE M.MonitorEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
 
  WHEN AOV.OverrideType = 'RuleConfiguration' THEN 
             (CASE R.RuleEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
             
  WHEN AOV.OverrideType = 'MonitorConfiguration' THEN 
             (CASE M.MonitorEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True'
             WHEN '4' THEN 'True' 
             END) 
END) AS 'Enable_by_default', MPSTORE.MPFriendlyName AS 'MP Stored'
 
FROM AllOverrideView AS aov 
LEFT OUTER JOIN Rules AS R WITH (nolock) ON aov.TargetId = R.RuleId AND (aov.OverrideType = 'RuleProperty' OR aov.OverrideType = 'RuleConfiguration') 
LEFT OUTER JOIN Monitor AS M WITH (nolock) ON aov.TargetId = M.MonitorId AND (aov.OverrideType = 'MonitorProperty' OR aov.OverrideType = 'MonitorConfiguration') 
LEFT OUTER JOIN  ManagementPack AS MP WITH (nolock) ON (CASE 
                                                                                              WHEN AOV.OverrideType = 'RuleProperty' THEN R.ManagementPackId 
                                                                                              WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.ManagementPackId
                                                                                              WHEN AOV.OverrideType = 'MonitorProperty' THEN M.ManagementPackId 
                                                                                              WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.ManagementPackId
                                                                                              END) = MP.ManagementPackId 
INNER JOIN ManagementPack AS MPSTORE WITH (nolock) ON MPSTORE.ManagementPackId = aov.ManagementPackId 
LEFT OUTER JOIN ManagedTypeView AS mt WITH (nolock) ON mt.Id = aov.ContextId 
LEFT OUTER JOIN ManagedTypeView AS mtv WITH (nolock) ON mtv.Id = aov.ContextObjectId
 
WHERE (MP.MPFriendlyName IS NOT NULL) 
AND (aov.LastModified BETWEEN @StartDate AND @EndDate) 
OR (MP.MPFriendlyName IS NOT NULL) 
AND (aov.TimeAdded BETWEEN @StartDate AND @EndDate)
 
ORDER BY aov.TimeAdded DESC

/**** 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'

 
 
# Fonction de recuperation de compteur de performance dans la base OperationsManager
 
    Function GetPerfValueFromScomv2($managementServer,$user,$passwd,$criteria,$monitoringClassId = $null,$monitoringObjectName = $null,$monitoringObjectDisplayName = $null,$objectName = $null,$counterName = $null,$instanceName = $null,$HourOffset,$startTime = (Get-Date).AddHours(-$HourWindow),$endTime = (Get-Date))
    {
 

#Fonction d'ajout de critere a la requete 
function Add-Criteria() 
{ 
    param($criteria,$clause) 
     
    if ($criteria -ne $null) 
        {$criteria += " AND $clause"} 
    else 
        {$criteria = $clause} 
         
    return($criteria) 
} 
 
 

#Import du module Scom 
import-module OperationsManager 
 
#Connexion au management server 
 
 
$mgConn = New-Object Microsoft.EnterpriseManagement.ManagementGroupConnectionSettings($managementServer)
$mgConn.UserName = $user
$mgConn.Password = $passwd
$mg = New-Object Microsoft.EnterpriseManagement.ManagementGroup($mgConn)
 
 
if ($criteria -eq $null) { 
    if ($monitoringClassId -ne $null) { $criteria = Add-Criteria -criteria $criteria -clause "MonitoringClassId = '{$monitoringClassId}'" } 
    if ($monitoringObjectDisplayName -ne $null) { $criteria = Add-Criteria -criteria $criteria -clause "MonitoringObjectDisplayName = '$monitoringObjectDisplayName'" } 
    if ($monitoringObjectName -ne $null) { $criteria = Add-Criteria -criteria $criteria -clause "MonitoringObjectName = '$monitoringObjectName'" } 
    if ($objectName -ne $null) { $criteria = Add-Criteria -criteria $criteria -clause "ObjectName = '$objectName'" } 
    if ($counterName -ne $null) { $criteria = Add-Criteria -criteria $criteria -clause "CounterName = '$counterName'" } 
    if ($instanceName -ne $null) { $criteria = Add-Criteria -criteria $criteria -clause "InstanceName = '$instanceName'" } 
} 
 
#Creation du reader 
$reader = $mg.GetMonitoringPerformanceDataReader($criteria) 
while ($reader.Read()) 
{ 
    #Creation de l'objet de performance
    $perfData = $reader.GetMonitoringPerformanceData() 
    $valueReader = $perfData.GetValueReader($startTime,$endTime) 
 
    #Renvoi des valeurs 
    while ($valueReader.Read()) 
    { 
        $perfValue = $valueReader.GetMonitoringPerformanceDataValue() 
        $perfValue 
    } 
}
 
}
 
 
    

##############################################################################

# SCRIPT DE RECUPERATION DES ALERTES D'UNE APPLIANCE HPONEVIEW ET MISE EN ETAT CLEARED
# author: cjourdan

 

# PARAMETRES:
# $ApplianceName: Nom de l'appliance
# $RessourceName: Nom de la ressource (Server,Enclosure ...).
# $DomainName: Domaine AD.
# $AlertSeverity: Critical, Warning, OK, Unknown, Disabled
# $AlertState: Active, Locked, Cleared, Pending, Running, Completed, Interrupted, Error, Warning, Terminated, Killed.
# $DescInclude: Chaine de caractère dans la champ Description a inclure dans la recherche.
# $DescExclude: Chaine de caractère dans la champ Description a exclure de la recherche.
# $DayOffset = Recuperer les alertes plus recente que J-DayOffset.


# Apres Recuperation des alertes, le script demande validation pour mettre les alertes en état Cleared


Param(

[Parameter(Mandatory=$false)]

$ApplianceName = "MyAppliance",

[Parameter(Mandatory=$false)]

# Server,Enclosure ...

$RessourceName = "MyResource",

[Parameter(Mandatory=$false)]

$DomainName = "MyDomain",

[Parameter(Mandatory=$false)]

$AlertSeverity = "Critical",

[Parameter(Mandatory=$false)]

$AlertState = "Cleared",

$DescInclude = "*",

$DescExclude = "azerty",

$DayOffset = 2

)

 

$user = Read-Host -Prompt "Enter MyDomain Credential without Domain" 

$passwd = Read-Host -Prompt "Password" -AsSecureString

 

if (-not (get-module HPOneview.200)) 

{
 

    Import-Module HPOneView.200
 

}

 

# Connexion a l'appliance OneView

if (-not($global:ConnectedSessions))

{ 
       

       Connect-HPOVMgmt -Hostname $ApplianceName -UserName $user -Password $passwd -AuthLoginDomain $DomainName
 

}

 

write-host -BackgroundColor White -ForegroundColor Blue "APPLIANCE ONEVIEW: $ApplianceName"`n

 

# NB: Preciser au minimum un filtre de recherche avant le '|' afin de reduire le temps de recuperation des alertes

Try

{

    $Alerts = Get-HPOVAlert -ApplianceConnection $ApplianceName -Severity $AlertSeverity | Where-Object {$_.AlertState -eq $AlertState `

    -AND $_.associatedresource -like "*$RessourceName*" `

    -AND $_.description -like "*$DescInclude*" `

    -AND $_.description -notlike "*$DescExclude*"`

    -AND $([datetime]$_.created) -gt $(get-date).AddDays(-$DayOffset)

    } 

 

    $FormAlerts = foreach ($alert in $Alerts)

        {

        Write-output "DESCRIPTION: "$alert.description"" ;

        Write-Output "SEVERITY: "$alert.severity"" ;

        Write-Output "STATE: "$alert.alertState"" ;

        Write-Output "CREATED: "(get-date -format G $alert.Created)"" ;

        Write-Output "CLEARED: "(get-date -format G $alert.clearedtime)"" ;

        Write-Output "RESSOURCE_NAME: "$alert.associatedresource.resourcename"" ; 

        Write-Output "APPLIANCE: "$alert.ApplianceConnection.Name"" ;

        Write-Output "" ; 

        Write-Output "*******************" ;

        Write-Output ""

        } 

 

    write-host -BackgroundColor White -ForegroundColor Blue "CRITERES DE RECHERCHE: Resource: $RessourceName / Severity: $AlertSeverity / Status: $AlertState /  a inclure: $DescInclude / a exclure: $DescExclude / Plus recentes que: J-$DayOffset"`n

    $count=$Alerts | Measure-Object | select count -ExpandProperty count

    write-host -BackgroundColor White -ForegroundColor Blue "NOMBRE D ALERTES: $count" `n

    $FormAlerts

 

}

 

Catch

{

       Write-Error -ErrorRecord $_ -ErrorAction Stop

}


$answer = Read-Host -Prompt "VOULEZ VOUS MODIFIER L'ETAT DES CES ALERTES EN ETAT 'CLEARED' ?: Y/N"
 

switch($answer)

    {

        "Y" {"MISE EN ETAT CLEARED"}

        "N" {"AUCUNE ACTION" ; exit}

        default {"MAUVAISE REPONSE - AUCUNE ACTION" ; exit}

    }

 

# Clear Alerts


         Try

         {

          $updatedAlert = $Alerts | set-HPOVAlert -Cleared
         
         }

             Catch

             {
             Write-Error -ErrorRecord $_ -ErrorAction Stop
             }


    $updatedAlert

 

    Write-Host -ForegroundColor Green "MISE EN ETAT CLEARED TERMINEE SANS ERREUR !"

#SCOM - SCRIPT DE REQUETE D'ETAT DES OBJETS D'UNE LISTE DE CLASSE


#Variables
$MGroup = "MyGroup"
$MS= "MyMS"
$cred = Get-Credential "MyDomain\"

#Liste de classe a recuperer
$ClassList = ('SQL DB Engine','SQL Database','SQL DB File','SQL Agent','SQL Reporting Services','SQL Analysis Services')


#Import du module SCOM
try
{
Import-Module -Name OperationsManager -ErrorAction stop
}
catch
{
write-host -ForegroundColor red "Erreur lors de l'import du module SCOM"
}


#Connection au management group $MGroup
New-SCOMManagementGroupConnection -ComputerName $MS -Credential $cred


#Recuperation des classes
$MonitoringClasses = $ClassList | foreach {Get-SCOMClass -DisplayName $_}

if ($MonitoringClasses -eq $null)
    {
    write-host "ERROR - UNABLE TO RETRIEVE CLASSES" -ForegroundColor red
    }


write-host "#################################################### `n"
write-host "SCOM Management Group: $MGroup `n"
write-host "#################################################### `n`n"


#Recuperation des instances de chaque classe
foreach ($class in $MonitoringClasses)
{
        $MonitoringObjects =  $class | Get-SCOMClassInstance -erroraction silentlycontinue | Sort-Object -Descending -Property HealthState
 

        write-host -BackgroundColor white -ForegroundColor blue "         **************************************************************** `n"
        write-host -BackgroundColor white -ForegroundColor blue "         *** OBJETS DE LA CLASSE "$class.displayname" `n"
        write-host -BackgroundColor white -ForegroundColor blue "         **************************************************************** `n`n"

 

        write-host "         --- $(get-date -Format F) --- `n`n"

        write-host "         NUMBER OF OBJECT: $($MonitoringObjects | measure-object | select-object -property count -expandproperty count) `n`n"

        write-host "         STATE ----------------- OBJECT`n`n"

 

            foreach ($object in $MonitoringObjects)
            {
            switch ($object.HealthState)
                {
                "Success"          {write-host "         " -NoNewline ; write-host -ForegroundColor green $object.HealthState -NoNewline ; write-host " -------- " -NoNewline ; write-host  $object.FullName}
                "Error"            {write-host "         " -NoNewline ; write-host -ForegroundColor red $object.HealthState -NoNewline ; write-host " -------- " -NoNewline ;write-host  $object.FullName}
                "Warning"          {write-host "         " -NoNewline ; write-host -ForegroundColor yellow $object.HealthState -NoNewline ; write-host " -------- " -NoNewline ;write-host  $object.FullName}
                "Uninitialized"    {write-host "         " -NoNewline ; write-host -ForegroundColor blue $object.HealthState -NoNewline ; write-host " -------- " -NoNewline ;write-host  $object.FullName}
                }

            }

         "`n"

}

#Fermeture de la connexion
Get-SCOMManagementGroupConnection | Remove-SCOMManagementGroupConnection