SCOM – SCRIPT – Fonction powershell pour lister toutes les instances d’une machine

par Christophe Jourdan | Juin 12, 2019 | PowerShell, Script, supervision | 0 commentaires
Le script ci-dessous intègre la requête SQL de récupération des instances d’une machine, sous forme de fonction.
<pre class="wp-block-syntaxhighlighter-code"># SCRIPT THAT QUERY SCOM DATABASE TO GET ALL INSTANCES OF ALL CLASSES FOR A GIVEN COMPUTER ($TargetAgent)
# $TargetComputer must be Short name of computer because we look for this specific string in name, displayname and path of instances.
 
param(
#Short name of computer
$TargetComputer = "MyComputer"
)
 
 
# Function  Invoke-InstancesFromSQL
 
Function Invoke-InstancesFromSQL
                            {
                                param(
                                    [string]$dataSource = "MyScomSQLServer\OPSMGR",
                                    [string]$database = "OperationsManager",
                                    [string]$TargetComputer,
                                    [string]$sqlCommand = 
                                            $("Use $Database
                                                SELECT 
                                                   MTV.DisplayName as ClassName
                                                   ,MEGV.Path as Instance_Path
                                                   ,MEGV.Id as Instance_Id
                                                   ,MEGV.[DisplayName] as 'Entity_DisplayName'
                                                   ,MEGV.[Name] as 'Entity_Name'
                                                   ,MEGV.[FullName] as Entity_FullName
                                                   ,[IsManaged]
                                                   ,[IsDeleted]
                                                   ,HealthState = 
                                                    CASE WHEN InMaintenanceMode = '0'
                                                          THEN 
                                                            CASE [HealthState]
                                                            WHEN '0' THEN 'Not Monitored'
                                                            WHEN '1' THEN 'OK'
                                                            WHEN '2' THEN 'Warning'
                                                            WHEN '3' THEN 'Critical'
                                                            END
                                                        WHEN InMaintenanceMode = '1'
                                                        THEN 
                                                            CASE [HealthState]
                                                            WHEN '0' THEN 'In Maintenance Mode'
                                                            WHEN '1' THEN 'OK'
                                                            WHEN '2' THEN 'Warning'
                                                            WHEN '3' THEN 'Critical'
                                                            END
                                                    END
                                                  ,Is_Available = CASE [IsAvailable]
                                                        WHEN '1' THEN 'YES'
                                                        WHEN '2' THEN 'NO'
                                                        END
                                                   
                                                  ,In_MaintenanceMode = CASE [InMaintenanceMode]
                                                        WHEN '0' THEN 'NO'
                                                        WHEN '1' THEN 'YES'
                                                        END
                                                   
                                                  ,Start_Of_Maintenance = CASE WHEN InMaintenanceMode = '0' 
                                                        THEN null
                                                        ELSE MMV.StartTime
                                                        END
                                                  ,End_Of_Maintenance = CASE WHEN InMaintenanceMode = '0'
                                                        THEN null
                                                        ELSE MMV.ScheduledEndTime
                                                        END
                                                                                                   
                                                  ,Maintenance_RootCause = 
                                                        CASE WHEN InMaintenanceMode = '0'
                                                          THEN null
                                                            ELSE 
                                                                CASE MMV.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
                                                            END
                                                   
                                                    ,Maintenance_Reason =   
                                                        CASE WHEN InMaintenanceMode = '0' 
                                                            THEN null
                                                                ELSE MMV.Comments
                                                        END
       
       
                                              FROM [OperationsManager].[dbo].[ManagedEntityGenericView] MEGV
 
                                              INNER JOIN [dbo].[ManagedTypeView] MTV on MEGV.MonitoringClassId = MTV.Id
                                              INNER JOIN [OperationsManager].[dbo].[MaintenanceModeView] MMV on MEGV.id = MMV.BaseManagedEntityId
                                              WHERE (MEGV.Name  like '%$TargetComputer%' OR MEGV.DisplayName  like '%$TargetComputer%' OR MEGV.Path  like '%$TargetComputer%')
                                              and MTV.LanguageCode = 'ENU'
                                              and MEGV.HealthState is not null
                                              and MEGV.IsDeleted <> '1'
                                              ORDER BY MTV.DisplayName
                                            ")
                                           )
 
 
                                    $connectionString = "Data Source=$dataSource; " +
                                    "Integrated Security=SSPI; " +
                                    "Initial Catalog=$database"
 
                                $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
                                $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
                                 
                                try
                                {
                                $connection.Open()
                                }
                                catch
                                {
                                write-host -F Red $("Error during sql connection - check the credentials used").ToUpper()
                                exit 1
                                }
 
                                $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
                                $dataset = New-Object System.Data.DataSet
                                $adapter.Fill($dataSet) | Out-Null
 
                                $connection.Close()
                                $dataSet.Tables
 
                              }
 
 
[array]$table = Invoke-InstancesFromSQL -TargetComputer $TargetComputer
 
 
write-host "`nCOMPUTER: $TargetComputer"
 
Write-Host "`nNb Of Objects:"$table.count""
 
$table | ft -Property ClassName,Instance_Path,Entity_DisplayName,Entity_Name,Entity_FullName,HealthState,Is_Available,In_MaintenanceMode,Start_Of_Maintenance,End_Of_Maintenance,Maintenance_RootCause,Maintenance_Reason
 
 
 
# TO FILTER ON SPECIFIC CLASS
# $table | Where-Object {$_.CLASS_NAME -in ('Windows computer','Health Service Watcher','VMware vSphere Host','VMWare Virtual Machine','HPE ProLiant Server')} | ft -AutoSize
 
 
 
# SIMULATE START OF MAINTENANCE MODE FOR THE INSTANCES
#$table | foreach {Get-SCOMClassInstance -id $_.instance_id} | foreach {start-SCOMMaintenanceMode -instance $_ -WhatIf -EndTime $((Get-Date).AddHours(1))}
</pre>