Hey, here’s Martin again.
Did you ever wanted to know, what the effective next maintenance window for a specific Computer in System Center 2012 Configuration Manager is? There isn’t a built-in Report, that will give you the infomation you are looking for. And if you would like to calculate for yourself, have fun, when your maintenence window was starting a year ago…
After a bit googling, a found this blog post, which describes the prcedure for SCCM 2007:
http://social.technet.microsoft.com/wiki/contents/articles/7870.sccm-2007-create-report-of-upcoming-maintenance-windows-by-client.aspx
It will work, when you Change the following on the Report Query:
;WITH AllServiceWindows AS (
-- All Service Windows for every resource and collection
SELECT fcm.ResourceID
, c.Name AS Collection
, nsw.Duration
, nsw.NextServiceWindow
, ROW_NUMBER() OVER (PARTITION BY ResourceID ORDER BY nsw.NextServiceWindow) AS RowNumber
FROM vSMS_ServiceWindow AS sw
JOIN v_FullCollectionMembership AS fcm
ON sw.SiteID = fcm.CollectionID
JOIN v_Collection AS c
ON sw.SiteID = c.CollectionID
CROSS
APPLY dbo.SCCM_GetNextServiceWindow(sw.Schedules, sw.RecurrenceType) AS nsw
)
SELECT sys.Name0 AS [Computer Name]
, sys.Distinguished_Name0 AS [AD DN]
, asw.Collection AS [Collection]
, CONVERT(CHAR(5), DATEADD(MINUTE, asw.Duration, 0), 108) AS [Duration (HH:MM)]
, CONVERT(CHAR(16), asw.NextServiceWindow, 120) AS [Next Maintenance Window]
, DATENAME(WEEKDAY, asw.NextServiceWindow) AS [Day of Week]
FROM v_R_System as sys
LEFT
JOIN AllServiceWindows AS asw
ON sys.ResourceID = asw.ResourceID
WHERE sys.Client0 = 1
AND asw.RowNumber = 1
ORDER
BY asw.NextServiceWindow
, asw.Collection
, sys.Name0
You have to Change the line “sys.description0 AS [Description]” because description0 does not longer exist in SCCM 2012. I just choose to Show the distinguished Name of the Computer object.
Hope this helps 🙂
Thank you for this query. However, it would be helpful to note that the SCCM_GetNextServiceWindow function needs to be created, otherwise this will fail.