Function GetLogicDiskFreeSpState { param( [string]$dataSource = "SQLSERV1\OPSMGR", [string]$database = "OperationsManager", [string]$UnitMonitor = "Microsoft.Windows.Server.%.LogicalDisk.FreeSpaceMonitorRollup", [string]$TypeName = "Microsoft.Windows.Server.%.LogicalDisk", [string]$sqlCommand = $(" /* QUERY THAT GET STATE OF SPECIFIC MONITOR STATE FOR ALL COMPUTERS */ Use $database DECLARE @UnitMonitor VARCHAR(100) DECLARE @TypeName VARCHAR(100) SET @UnitMonitor = '%'+'$UnitMonitor'+'%' SET @TypeName = '$TypeName' PRINT 'MONITOR: ' + @UnitMonitor PRINT 'CRITERIAS:' ; WITH MAININFO (Monitor,Computer,Disk,HealthState, LastModified,IsAvailable,InMaintenanceMode) AS ( SELECT MV.Name as Monitor ,MEGV.path as Computer ,MEGV.Name as Disk ,HealthState = CASE WHEN InMaintenanceMode = '0' OR InMaintenanceMode is null THEN CASE MEGV.IsAvailable WHEN '0' THEN 'KO - STATE IS NOT AVAILABLE' -- THIS MEAN THAT THE STATE IS GRAYED IN SCOM CONSOLE DESPITE OF THE OBJECT IS NOT IN MAINTENANCE MODE (AGENT FUNCTIONNAL PROBLEM) WHEN '1' THEN CASE SV.[HealthState] WHEN '0' THEN 'Not Monitored' WHEN '1' THEN 'OK' WHEN '2' THEN 'Warning' WHEN '3' THEN 'Critical' END END WHEN InMaintenanceMode = '1' THEN CASE MEGV.IsAvailable WHEN '0' THEN 'KO - STATE IS NOT AVAILABLE' -- THIS MEAN THAT THE STATE IS GRAYED IN SCOM CONSOLE DESPITE OF THE OBJECT IS IN MAINTENANCE MODE (AGENT FUNCTIONNAL PROBLEM) WHEN '1' THEN CASE SV.[HealthState] WHEN '0' THEN 'In Maintenance Mode' WHEN '1' THEN 'OK' WHEN '2' THEN 'Warning' WHEN '3' THEN 'Critical' END END END ,SV.[LastModified] as LastModified ,MEGV.IsAvailable ,MEGV.InMaintenanceMode FROM [OperationsManager].[dbo].[StateView] SV INNER JOIN [dbo].[ManagedEntityGenericView] MEGV on SV.BaseManagedEntityId = MEGV.BaseManagedEntityId INNER JOIN [dbo].[MonitorView] MV on SV.MonitorId = MV.id INNER JOIN [dbo].[ManagedTypeView] MTV on MEGV.MonitoringClassId = MTV.Id WHERE MV.Name like @UnitMonitor AND MTV.Name like @TypeName ) SELECT MAININFO.Monitor ,MAININFO.Computer ,MAININFO.Disk ,MAININFO.HealthState ,MAININFO.LastModified ,MAININFO.IsAvailable ,MAININFO.InMaintenanceMode FROM MAININFO ORDER BY computer,Disk " ) ) $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) $command.CommandTimeout=300 $connection.Open() $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command $dataset = New-Object System.Data.DataSet $adapter.Fill($dataSet) | Out-Null $connection.Close() #Display Time of Query "`n" write-host "Query Date: $(get-date -Format F)" -NoNewline "`n" #Display Criterias Write-Host "STATE OF MONITOR `"$UnitMonitor`":" "`n" #Display Nb of rows write-host Nb Of Object: $($dataset.Tables.defaultview.Count) "`n" $dataSet.Tables } try { GetLogicDiskFreeSpState | ft -AutoSize } catch { write-host -F Red "ERROR DURING EXECUTION OF GetLogicDiskFreeSpState FUNCTION - CHECK THAT YOU ARE LOGGED WITH A RIGHT ACCOUNT OR THAT THE SQL QUERY IS CORRECT" }