Infrastructure – System Center Operations Manager – SQL Query for SCOM Maintenance mode schedules

SCOM maintenance schedules list views only display names and comments. In order to view affected objects you are required to open the schedules to see the server list. This SQL query will display semicolon delimited list of the affects objects for each schedule.

Below is a SQL query you can utilize to see all SCOM maintenance schedules in your Operations Manager Management Group.

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)

Note that the object names are semicolon delimited to show you the systems that are included in the named maintenance schedule.