Le script ci-dessous intègre la requête SQL de récupération des instances d’une machine, sous forme de fonction.
All_InstancesForOneAgent_vSQLQuery.ps1
<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>
