TechNet Wiki v2

How To

Calculating the next maintenance window in SCCM is a bit troublesome as you normally have to use WMI to get the data struc schedule data structure based on the current date/time.

Actions Required Resolve the "How-To"

Run the following T-SQL on your SCCM site database:

CREATE FUNCTION


[dbo].[SCCM_GetNextServiceWindow](@ScheduleToken

AS CHAR

(16), @RecurrenceType

AS

INT

) RETURNS @NextServiceWindow TABLE (ScheduleToken CHAR

(16), RecurrenceType

INT

, NextServiceWindow DATETIME, Duration

INT

, IsGMTTime

BIT

AS


BEGIN

--1 Occurs on 1/1/2012 12:00 AM 00011A8500080000

--2 Occurs every 3 day(s) effective 1/1/2012 8:00 PM 02811A8040100018

--3 Occurs every 3 week(s) on Saturday effective 1/1/2012 8:00 PM 02811A80401F6000

--3 Occurs every 1 week(s) on Saturday effective 1/1/2012 8:00 PM 02811A80401F2000

--5 Occurs day 2 of every 2 month(s) effective 1/1/2012 8:00 PM 02811A8040288800

--5 Occurs day 31 of every 1 month(s) effective 1/1/2012 8:00 PM 02811A80402FC400

--5 Occurs the last day of every 3 months effective 1/1/2012 8:00 PM 02811A8040280C00

--5 Occurs the last day of every 1 months effective 1/1/2012 8:00 PM 02811A8040280400

--4 Occurs the Third Monday of every 1 month(s) effective 1/1/2012 4:00 AM 00811A9E08221600

--4 Occurs the Last Wednesday of every 1 month(s) effective 1/1/2012 8:00 PM 02811A8040241000

--4 Occurs the Fourth Wednesday of every 1 month(s) effective 1/1/2012 8:00 PM 02811A8040241800

--4 Occurs the Last Monday of every 1 month(s) effective 1/1/2012 8:00 PM 02811A8040221000

--3 Occurs every 1 week(s) on Monday effective 1/1/2012 4:00 AM 00811A9E081A2000

--
http://msdn.microsoft.com/en-us/library/cc143300.aspx

DECLARE @RecurrenceType_NONE INT

, @RecurrenceType_DAILY

INT

, @RecurrenceType_WEEKLY

INT

, @RecurrenceType_MONTHLYBYWEEKDAY

INT

, @RecurrenceType_MONTHLYBYDATE

INT

SELECT @RecurrenceType_NONE = 1

, @RecurrenceType_DAILY = 2

, @RecurrenceType_WEEKLY = 3

, @RecurrenceType_MONTHLYBYWEEKDAY = 4

, @RecurrenceType_MONTHLYBYDATE = 5

--
http://msdn.microsoft.com/en-us/library/cc143505.aspx

--DECLARE @RecurrenceType INT; SET @RecurrenceTypeecurrenceType_WEEKLY = 3

, @RecurrenceType_MONTHLYBYWEEKDAY = 4

, @RecurrenceType_MONTHLYBYDATE = 5

--
http://msdn.microsoft.com/en-us/library/cc143505.aspx

--DECLARE = @RecurrenceType_WEEKLY

--DECLARE @ScheduleToken CHAR(16); SET @ScheduleToken = '00811A9E081A2000'

DECLARE @ScheduleStartTime INT

SET


@ScheduleStartTime =

CAST(CONVERT(BINARY

(4),

LEFT

(@ScheduleToken, 8), 2)

AS

INT

)

DECLARE @ScheduleDuration INT

SET


@ScheduleDuration  =

CAST(CONVERT(BINARY

(4),

RIGHT

(@ScheduleToken, 8), 2)

AS

INT

)

-- Duration is in minutes

DECLARE @Duration INT

SET


@Duration = @ScheduleStartTime % POWER(2, 6)

-- Calculate the start time

DECLARE @StartTime DATETIME; SET @StartTime = CONVERT

(DATETIME,

'01/01/1970 00:00:00')

SET @StartTime = DATEADD(YEAR, (@ScheduleStartTime / POWER(2,6)) % POWER(2, 6), @StartTime)

SET @StartTime = DATEADD(MONTH, ((@ScheduleStartTime / POWER(2,12)) % POWER(2, 4)) - 1, @StartTime)

SET @StartTime = DATEADD(DAY, ((@ScheduleStartTime / POWER(2,16)) % POWER(2, 5)) - 1, @StartTime)

SET @StartTime = DATEADD(HOUR, (@ScheduleStartTime / POWER(2,21)) % POWER(2, 5), @StartTime)

SET @StartTime = DATEADD(MINUTE, (@ScheduleStartTime / POWER(2,26)) % POWER(2, 5), @StartTime)

-- Determinte UTC and Flags

DECLARE @IsGMTTime BIT

SET


@IsGMTTime     =

CAST

(@ScheduleDuration % POWER(2, 1)

AS

BIT

)

DECLARE @Flags INT

SET


@Flags         = (@ScheduleDuration / POWER(2,19)) % POWER(2, 3)

-- Calculate the total duration in minutes

SET

@Duration = @Duration + ((@ScheduleDuration / POWER(2,22)) % POWER(2, 5)) * 24 * 60

-- DAYS

SET

@Duration = @Duration + ((@ScheduleDuration / POWER(2,27)) % POWER(2, 5)) * 60

-- HOURS

DECLARE @Now DATETIME

IF @IsGMTTime = 1

BEGIN

SET @Now = GETUTCDATE()

END ELSE


BEGIN

SET @Now = GETDATE()

END

DECLARE @NextMaintenanceWindow DATETIME

IF @RecurrenceType = @RecurrenceType_NONE

BEGIN

IF DATEADD(MINUTE

, @Duration, @StartTime) >
 @Now

BEGIN

SET @NextMaintenanceWindow = @StartTime

END

END ELSE


IF @RecurrenceType = @RecurrenceType_DAILY

BEGIN

IF DATEADD(MINUTE

, @Duration, @StartTime) >
 @Now

BEGIN

SET @NextMaintenanceWindow = @StartTime

END ELSE


BEGIN

-- Calculate the daily interval in minutes

DECLARE @DailyInterval INT

SET @DailyInterval = ((@ScheduleDuration / POWER(2,3)) % POWER(2, 5)) * 24 * 60

SET @DailyInterval = @DailyInterval + ((@ScheduleDuration / POWER(2,8)) % POWER(2, 5)) * 60

SET @DailyInterval = @DailyInterval + (@ScheduleDuration / POWER(2,13)) % POWER(2, 6)

-- Calculate the total number of completed intervals

DECLARE @DailyNumberOfCompletedIntervals INT

SET


@DailyNumberOfCompletedIntervals = ROUND(

CAST(DATEDIFF(MINUTE

, @StartTime, @Now)

AS


DECIMAL

) / @DailyInterval, 0, 0)

-- Calculate the next interval

DECLARE @DailyNextInterval DATETIME; SET @DailyNextInterval = DATEADD(MINUTE, @DailyNumberOfCompletedIntervals * @DailyInterval, @StartTime)

-- Recalc the next interval if the next interval plus the expected duration is in the past

IF DATEADD(MINUTE

, @Duration, @DailyNextInterval)
 < @Now

BEGIN

SET @DailyNextInterval = DATEADD(MINUTE, (@DailyNumberOfCompletedIntervals + 1) * @DailyInterval, @StartTime)

END

SET @NextMaintenanceWindow = @DailyNextInterval

END

END ELSE


IF @RecurrenceType = @RecurrenceType_WEEKLY

BEGIN

DECLARE @WeeklyInterval INT

SET


@WeeklyInterval = (@ScheduleDuration / POWER(2,13)) % POWER(2, 3)

DECLARE @WeeklyDoW INT

SET


@WeeklyDoW      = (@ScheduleDuration / POWER(2,16)) % POWER(2, 3)

-- Adjust the start time to match the next day of week that matches the interval

DECLARE @WeeklyStartTime DATETIME; SET @WeeklyStartTime = DATEADD(DAY, (7 - DATEPART(WEEKDAY, @StartTime) + @WeeklyDoW % 7), @StartTime)

IF DATEADD(MINUTE

, @Duration, @WeeklyStartTime)
 > @Now

BEGIN

SET @NextMaintenanceWindow = @WeeklyStartTime

END ELSE


BEGIN

-- Calculate the total number of completed intervals

DECLARE @WeeklyNumberOfCompletedIntervals INT

SET


@WeeklyNumberOfCompletedIntervals = ROUND(

CAST

(DATEDIFF(WEEK, @WeeklyStartTime, @Now)

AS


DECIMAL

) / @WeeklyInterval, 0, 0)

-- Calculate the next interval

DECLARE @WeeklyNextInterval DATETIME; SET @WeeklyNextInterval = DATEADD(WEEK, @WeeklyNumberOfCompletedIntervals * @WeeklyInterval, @WeeklyStartTime)

-- Recalc the next interval if the next interval plus the expected duration is in the past

IF DATEADD(MINUTE

, @Duration, @WeeklyNextInterval)
 < @Now

BEGIN

SET @WeeklyNextInterval = DATEADD(WEEK, (@WeeklyNumberOfCompletedIntervals + 1) * @WeeklyInterval, @WeeklyStartTime)

END

SET @NextMaintenanceWindow = @WeeklyNextInterval

END

END ELSE


IF @RecurrenceType = @RecurrenceType_MONTHLYBYWEEKDAY

BEGIN

DECLARE @MonthlyBWWeek INT

SET


@MonthlyBWWeek     = (@ScheduleDuration / POWER(2,9)) % POWER(2, 3)

DECLARE @MontlhyBWInterval INT

SET


@MontlhyBWInterval = (@ScheduleDuration / POWER(2,12)) % POWER(2, 4)

DECLARE @MonthlyBWDoW INT

SET


@MonthlyBWDoW      = (@ScheduleDuration / POWER(2,16)) % POWER(2, 3)

-- Calculate the total number of completed intervals

DECLARE @MonthlyBWNumberOfCompletedIntervals INT

SET


@MonthlyBWNumberOfCompletedIntervals = ROUND(

CAST(DATEDIFF(MONTH

, @StartTime, @Now)

AS


DECIMAL

) / @MontlhyBWInterval, 0, 0)

IF @MonthlyBWWeek = 0

BEGIN

-- Calculate the next interval

DECLARE @MonthlyBWLDOMNextInterval DATETIME; SET @MonthlyBWLDOMNextInterval = DATEADD(MONTH, @MonthlyBWNumberOfCompletedIntervals * @MontlhyBWInterval, @StartTime)

-- Calculate last day of month

SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBWLDOMNextInterval, DATEADD(DAY

,
 -1, DATEADD(M, DATEDIFF(

MONTH, 0, @MonthlyBWLDOMNextInterval) + 1, 0))), @MonthlyBWLDOMNextInterval)

-- Calculate the last day of the week for the month

SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, -(7 - DATEPART(WEEKDAY, @MonthlyBWLDOMNextInterval) + @MonthlyBWDoW % 7), @MonthlyBWLDOMNextInterval)

IF DATEADD(MINUTE

, @Duration, @MonthlyBWLDOMNextInterval)
 < @Now

BEGIN

-- Recalc for the next month interval

SET @MonthlyBWLDOMNextInterval = DATEADD(MONTH, (@MonthlyBWNumberOfCompletedIntervals + 1) * @MontlhyBWInterval, @StartTime)

-- Calculate last day of month

SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBWLDOMNextInterval, DATEADD(DAY

,
 -1, DATEADD(M, DATEDIFF(

MONTH, 0, @MonthlyBWLDOMNextInterval) + 1, 0))), @MonthlyBWLDOMNextInterval)

-- Calculate the last day of the week for the month

SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, -(7 - DATEPART(WEEKDAY, @MonthlyBWLDOMNextInterval) + @MonthlyBWDoW % 7), @MonthlyBWLDOMNextInterval)

END

SET @NextMaintenanceWindow = @MonthlyBWLDOMNextInterval

END ELSE


BEGIN

-- Calculate the next interval

DECLARE @MonthlyBWNextInterval DATETIME; SET @MonthlyBWNextInterval = DATEADD(MONTH, @MonthlyBWNumberOfCompletedIntervals * @MontlhyBWInterval, @StartTime)

-- Set the date to the first day of the month

SET @MonthlyBWNextInterval = DATEADD(DAY, -(DAY(@MonthlyBWNextInterval) - 1), @MonthlyBWNextInterval)

-- Set the date to the first day of week in the month

SET @MonthlyBWNextInterval = DATEADD(DAY, (7 - DATEPART(WEEKDAY, @MonthlyBWNextInterval) + @MonthlyBWDoW) % 7, @MonthlyBWNextInterval)

-- Calculate date based on the week number to add

SET @MonthlyBWNextInterval = DATEADD(WEEK, @MonthlyBWWeek-1, @MonthlyBWNextInterval)

IF DATEADD(MINUTE

, @Duration, @MonthlyBWNextInterval)
 < @Now

BEGIN

-- Recalc for the next month interval

SET @MonthlyBWNextInterval = DATEADD(MONTH, (@MonthlyBWNumberOfCompletedIntervals + 1) * @MontlhyBWInterval, @StartTime)

-- Set the date to the first day of the month

SET @MonthlyBWNextInterval = DATEADD(DAY, -(DAY(@MonthlyBWNextInterval) - 1), @MonthlyBWNextInterval)

-- Set the date to the first day of week in the month

SET @MonthlyBWNextInterval = DATEADD(DAY, (7 - DATEPART(WEEKDAY, @MonthlyBWNextInterval) + @MonthlyBWDoW % 7), @MonthlyBWNextInterval)

-- Calculate date based on the week number to add

SET @MonthlyBWNextInterval = DATEADD(WEEK, @MonthlyBWWeek-1, @MonthlyBWNextInterval)

END

SET @NextMaintenanceWindow = @MonthlyBWNextInterval

END

END ELSE


IF @RecurrenceType = @RecurrenceType_MONTHLYBYDATE

BEGIN

DECLARE @MontlhyBDInterval INT

SET


@MontlhyBDInterval = (@ScheduleDuration / POWER(2,10)) % POWER(2, 4)

DECLARE @MonthlyBDDoM INT

SET


@MonthlyBDDoM      = (@ScheduleDuration / POWER(2,14)) % POWER(2, 5)

IF @MonthlyBDDoM = 0

BEGIN

@MontlhyBDInterval = (@ScheduleDuration / POWER(2,10)) % POWER(2, 4)

DECLARE @MonthlyBDDoM INT

SET


@MonthlyBDDoM      = (@ScheduleDuration / POWER(2,14)) % POWER(2, 5)

IF @MonthlyBDDoM = 0

        /* This
is 
the last 
day of 
month logic */


         
        -- Calculate the total number of completed intervals
        DECLARE
@MonthlyBDLDOMNumberOfCompletedIntervals INT;
SET 
@MonthlyBDLDOMNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(MONTH, @StartTime, @Now)
AS 
DECIMAL) / @MontlhyBDInterval, 0, 0)
         
        -- Calculate the next interval
        DECLARE
@MonthlyBDLDOMNextInterval DATETIME; SET
@MonthlyBDLDOMNextInterval = DATEADD(MONTH, @MonthlyBDLDOMNumberOfCompletedIntervals * @MontlhyBDInterval, @StartTime)
 
        -- Calculate last day of month
        SET
@MonthlyBDLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBDLDOMNextInterval, DATEADD(DAY,
 -1, DATEADD(M, DATEDIFF(MONTH, 0, @MonthlyBDLDOMNextInterval) + 1, 0))), @MonthlyBDLDOMNextInterval)
         
        -- Recalc the next interval if the next interval plus the expected duration is in the past
        IF DATEADD(MINUTE, @Duration, @MonthlyBDLDOMNextInterval)
 < @Now BEGIN
            SET
@MonthlyBDLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBDLDOMNextInterval, DATEADD(DAY,
 -1, DATEADD(M, DATEDIFF(MONTH, 0, DATEADD(MONTH, (@MonthlyBDLDOMNumberOfCompletedIntervals + 1) * @MontlhyBDInterval,
 @StartTime)) + 1, 0))), @MonthlyBDLDOMNextInterval)
        END
         
        SET
@NextMaintenanceWindow = @MonthlyBDLDOMNextInterval
    END
ELSE 
BEGIN
        -- Check to make sure we won't loop forever if more than 31 days some how ends up in the token
        IF @MonthlyBDDoM > 31
SET 
@MonthlyBDDoM = 31

terval

END -- Adjust the start time to match the next day of month that matches the interval


        DECLARE
@MonthlyBDStartTime DATETIME; SET
@MonthlyBDStartTime = DATEADD(DAY, (31 - DATEPART(DAY, @StartTime) + @MonthlyBDDoM % 31), @StartTime)
         
        -- This loop is used multiple times to search for the next valid date that falls on the desired day of month
        WHILE(DATEPART(DAY, @MonthlyBDStartTime) <> @MonthlyBDDoM)
BEGIN
            SET
@MonthlyBDStartTime = DATEADD(DAY, (31 - DATEPART(DAY, @MonthlyBDStartTime) + @MonthlyBDDoM)
 % 31, @MonthlyBDStartTime)
        END
         
        IF DATEADD(MINUTE, @Duration, @MonthlyBDStartTime)
 > @Now BEGIN
            SET
@NextMaintenanceWindow = @MonthlyBDStartTime
        END
ELSE 
BEGIN
            -- Calculate the total number of completed intervals
            DECLARE
@MonthlyBDNumberOfCompletedIntervals INT;
SET 
@MonthlyBDNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(MONTH, @MonthlyBDStartTime, @Now)
AS 
DECIMAL) / @MontlhyBDInterval, 0, 0)
             
            -- Calculate the next interval
            DECLARE
@MonthlyBDNextInterval DATETIME; SET
@MonthlyBDNextInterval = DATEADD(MONTH, @MonthlyBDNumberOfCompletedIntervals * @MontlhyBDInterval, @MonthlyBDStartTime)
             
            WHILE(DATEPART(DAY, @MonthlyBDNextInterval) <> @MonthlyBDDoM)
BEGIN
                SET
@MonthlyBDNextInterval = DATEADD(DAY, (31 - DATEPART(DAY, @MonthlyBDNextInterval) + @MonthlyBDDoM
 % 31), @MonthlyBDNextInterval)
            END
             
            -- Recalc the next interval if the next interval plus the expected duration is in the past
            IF DATEADD(MINUTE, @Duration, @MonthlyBDNextInterval)
 < @Now BEGIN
                SET
@MonthlyBDNextInterval = DATEADD(MONTH, (@MonthlyBDNumberOfCompletedIntervals + 1) * @MontlhyBDInterval, @MonthlyBDNextInterval)
                 
                WHILE(DATEPART(DAY, @MonthlyBDNextInterval) <>
 @MonthlyBDDoM) BEGIN
                    SET
@MonthlyBDNextInterval = DATEADD(DAY, (31 - DATEPART(DAY, @MonthlyBDNextInterval) + @MonthlyBDDoM
 % 31), @MonthlyBDNextInterval)
                END

, @MonthlyBDNextInterval) <>
 @MonthlyBDDoM)

BEGIN

SET END


             
            SET
@NextMaintenanceWindow = @MonthlyBDNextInterval
        END
    END
END
 
INSERT
INTO 
@NextServiceWindow VALUES
(@ScheduleToken, @RecurrenceType, @NextMaintenanceWindow, @Duration, @IsGMTTime)
 
RETURN
END




To create a simple report that displays the client name and the next maintenance window use the following SQL for an SCCM report:


;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.description0
     APPLY dbo.SCCM_GetNextServiceWindow(sw.Schedules, sw.RecurrenceType)
AS 
nsw
old;">AS 
[Description]
     , 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

SCCM 2007: Create Report of Upcoming Maintenance Windows by Client - TechNet Articles - United States (English) - TechNet Wiki

How To

SCCM 2007: Create Report of Upcoming Maintenance Windows by Client

How To

Actions Required Resolve the "How-To"

References