ConfigMgr – List all Servers with Maintenance Windows

Developed a report to show all servers that have collection maintenance windows. Very handy for cross checking servers in multiple maintenance windows, or supplying a list to support change control. Here is the query:

 -- List Computers with Maintenance Window
SELECT  sw.Name AS [MW Name], sw.Description, 
    sw.StartTime, 
    sw.Duration AS 'Duration Minutes',
    sw.IsEnabled AS 'MW Enabled', 
    sys.Name0 AS ServerName, 
    sys.Operating_System_Name_and0 AS OperatingSystem
FROM  dbo.v_ServiceWindow AS sw INNER JOIN 
      dbo.v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID 
INNER JOIN  dbo.v_R_System sys ON fcm.ResourceID = sys.ResourceID 
WHERE  (sys.Operating_System_Name_and0 LIKE '%server%') 
ORDER BY [MW Name], sys.Name0