question

Juhij avatar image
0 Votes"
Juhij asked Juhij answered

SCOM- How to get number of servers currently in Maintenance mode using sql query ?

Hi Team,

I'm working on a Dashboard requirement so I need to show the numbers of servers currently in Maintenance mode using sql query.

msc-operations-manager
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexZhu-MSFT avatar image
0 Votes"
AlexZhu-MSFT answered

Hi,

We can use the following sql query or similar to check the servers in MM.

 Use OperationsManager
 SELECT
       [ScheduleName]
       , ( SELECT  BaseManagedEntity.DisplayName + '; '
   FROM  BaseManagedEntity with (NOLOCK)
   left join [OperationsManager].[dbo].[ScheduleEntity] on BaseManagedEntity.BaseManagedEntityId = ScheduleEntity.BaseManagedEntityId 
   where  ScheduleEntity.ScheduleId = MMS.ScheduleId
    FOR XML PATH('') 
    ) as ObjectName
     
       , case 
          when Recursive = 0 then 'False' 
          when Recursive = 1 then 'True' 
          else 'Undefined' 
        end as "Recursive" 
       , case 
          when IsEnabled = 0 then 'False' 
          when IsEnabled = 1 then 'True' 
          else 'Undefined' 
        end as "IsEnabled" 
       , case 
          when Status = 0 then 'Not Running' 
          when Status = 1 then 'Running' 
          else 'Running' 
        end as "Status" 
           
       , case 
          when IsRecurrence = 0 then 'False' 
          when IsRecurrence = 1 then 'True' 
          else 'Undefined' 
        end as "IsRecurrence" 
       ,[Duration]
       ,[Comments]
       ,[User]
       ,[NextRunTIme]
       ,[LastRunTIme]
   FROM [OperationsManager].[dbo].[MaintenanceModeSchedule] as MMS with (NOLOCK)

Alternatively, we can create a powershell widget within the dashboard to display the total numbers in MM.

 $servers_in_MM = @(Get-SCOMClass -Name Microsoft.Windows.Computer | Get-SCOMClassInstance |? {$_.InMaintenanceMode -eq $true}).count
    
 $dataObject = $ScriptContext.CreateInstance("xsd://foo!bar/baz")
 $dataObject["Id"] = $servers_in_MM.tostring()
 $dataobject["Maintenace Mode"]= $servers_in_MM.tostring() + " servers total in MM"
 $ScriptContext.ReturnCollection.Add($dataObject)

Screenshots of lab testing for your reference:

name the widget
130870-scom-dashboard-new-01.png

enter the script
130850-scom-dashboard-new-02.png

the result
130915-scom-dashboard-new-03.png


Alex
If the response is helpful, please click "Accept Answer" and upvote it.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Juhij avatar image
0 Votes"
Juhij answered

Not able to find the count using the above sql query. I only need the count to show in the dashboard

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.