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.

I hope you find this query useful in your daily SCOM routine.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.