Quick Summary

  1/*
  2.SYNOPSIS
  3    Gets the next maintenance window from a schedule token.
  4.DESCRIPTION
  5    Gets the next maintenance window from a schedule token.
  6.PARAMETER ScheduleToken
  7    Specifies the schedule token.
  8.PARAMETER RecurrenceType
  9    Specifies the maintenance window recurrence type.
 10    Accepted values are:
 11        '1' - None
 12        '2' - Daily
 13        '3' - Weekly
 14        '4' - Monthly by Weekday
 15        '5' - Monthly by Date
 16        '6' - Monthly by Weekday Base (Offset Days)
 17.EXAMPLE
 18    SELECT * FROM dbo.ufn_CM_GetNextMaintenanceWindow('00811A9E081A2000', 3)
 19.NOTES
 20    Created by Ioan Popovici
 21    All credit goes to Adam Weigert and Ed Price for the original code. I only reformated it a bit.
 22    Requires SELECT access on dbo.vSMS_ServiceWindow and on itself for smsschm_users (SCCM Reporting).
 23    Replace the <SITE_CODE> with your CM Site Code and uncomment SSMS region if running directly from SSMS.
 24    Run the code in SQL Server Management Studio.
 25.LINK
 26    https://social.technet.microsoft.com/wiki/contents/articles/7870.sccm-2007-create-report-of-upcoming-maintenance-windows-by-client.aspx (Adam Weigert)
 27.LINK
 28    https://MEMZ.one/Dashboards
 29.LINK
 30    https://MEMZ.one/Dashboards-HELP
 31.LINK
 32    https://MEMZ.one/Dashboards-ISSUES
 33*/
 34
 35/*##=============================================*/
 36/*## QUERY BODY                                  */
 37/*##=============================================*/
 38/* #region QueryBody */
 39
 40/* #region SSMS */
 41--USE [CM_<SITE_CODE>]
 42
 43/* Drop function if it exists */
 44--IF OBJECT_ID('[dbo].[ufn_CM_GetNextMaintenanceWindow]') IS NOT NULL
 45--  BEGIN
 46--      DROP FUNCTION [dbo].[ufn_CM_GetNextMaintenanceWindow]
 47--  END
 48--GO
 49/* #endregion */
 50
 51/* #region create ufn_CM_GetNextMaintenanceWindow */
 52CREATE FUNCTION [dbo].[ufn_CM_GetNextMaintenanceWindow] (
 53    @ScheduleToken      AS CHAR(16)
 54    , @RecurrenceType   AS INT
 55)
 56RETURNS @NextServiceWindow TABLE (
 57    ScheduleToken         CHAR(16)
 58    , RecurrenceType      INT
 59    , StartTime           DATETIME
 60    , NextServiceWindow   DATETIME
 61    , Duration            INT
 62    , IsServiceWindowOpen BIT
 63    , IsUTCTime           BIT
 64)
 65AS
 66    BEGIN
 67
 68        --1 Occurs on 1/1/2012 12:00 AM                                                             00011A8500080000
 69        --2 Occurs every 1 day(s) effective 1/1/2012 8:00 PM                                        01CA8C80C0100008
 70        --2 Occurs every 3 day(s) effective 1/1/2012 8:00 PM                                        02811A8040100018
 71        --3 Occurs every 3 week(s) on Saturday effective 1/1/2012 8:00 PM                           02811A80401F6000
 72        --3 Occurs every 1 week(s) on Saturday effective 1/1/2012 8:00 PM                           02811A80401F2000
 73        --5 Occurs day 2 of every 2 month(s) effective 1/1/2012 8:00 PM                             02811A8040288800
 74        --5 Occurs day 31 of every 1 month(s) effective 1/1/2012 8:00 PM                            02811A80402FC400
 75        --5 Occurs the last day of every 3 months effective 1/1/2012 8:00 PM                        02811A8040280C00
 76        --5 Occurs the last day of every 1 months effective 1/1/2012 8:00 PM                        02811A8040280400
 77        --4 Occurs the Third Monday of every 1 month(s) effective 1/1/2012 4:00 AM                  00811A9E08221600
 78        --4 Occurs the Last Wednesday of every 1 month(s) effective 1/1/2012 8:00 PM                02811A8040241000
 79        --4 Occurs the Fourth Wednesday of every 1 month(s) effective 1/1/2012 8:00 PM              02811A8040241800
 80        --4 Occurs the Last Monday of every 1 month(s) effective 1/1/2012 8:00 PM                   02811A8040221000
 81        --6 Occurs 7 day(s) after the First Monday of every 1 months effective 01-Jan-2024 01:00:00 00211D80083213C0
 82
 83        -- http://msdn.microsoft.com/en-us/library/cc143300.aspx Jump
 84        DECLARE @RecurrenceType_NONE               INT
 85            , @RecurrenceType_DAILY                INT
 86            , @RecurrenceType_WEEKLY               INT
 87            , @RecurrenceType_MONTHLYBYWEEKDAY     INT
 88            , @RecurrenceType_MONTHLYBYDATE        INT
 89            , @RecurrenceType_MONTHLYBYWEEKDAYBASE INT
 90
 91        SELECT @RecurrenceType_NONE                = 1
 92            , @RecurrenceType_DAILY                = 2
 93            , @RecurrenceType_WEEKLY               = 3
 94            , @RecurrenceType_MONTHLYBYWEEKDAY     = 4
 95            , @RecurrenceType_MONTHLYBYDATE        = 5
 96            , @RecurrenceType_MONTHLYBYWEEKDAYBASE = 6
 97
 98        -- http://msdn.microsoft.com/en-us/library/cc143505.aspx Jump
 99
100        --DECLARE @RecurrenceType    INT;       SET @RecurrenceType      = @RecurrenceType_DAILY
101        --DECLARE @ScheduleToken     CHAR(16);  SET @ScheduleToken       = '01CA8C80C0100008'
102        DECLARE @ScheduleStartTime   INT;       SET @ScheduleStartTime   = CAST(CONVERT(BINARY(4), LEFT(@ScheduleToken, 8), 2) AS INT)
103        DECLARE @ScheduleDuration    BIGINT;    SET @ScheduleDuration    = CAST(CONVERT(BINARY(4), RIGHT(@ScheduleToken, 8), 2) AS BIGINT)
104        DECLARE @IsServiceWindowOpen BIT;       SET @IsServiceWindowOpen = 0
105
106        -- Duration is in minutes
107        DECLARE @Duration INT; SET @Duration = @ScheduleStartTime % POWER(2, 6)
108
109        -- Calculate the start time
110        DECLARE @StartTime DATETIME; SET @StartTime = CONVERT(DATETIME, '01/01/1970 00:00:00')
111        SET @StartTime = DATEADD(YEAR, (@ScheduleStartTime / POWER(2,6)) % POWER(2, 6), @StartTime)
112        SET @StartTime = DATEADD(MONTH, ((@ScheduleStartTime / POWER(2,12)) % POWER(2, 4)) - 1, @StartTime)
113        SET @StartTime = DATEADD(DAY, ((@ScheduleStartTime / POWER(2,16)) % POWER(2, 5)) - 1, @StartTime)
114        SET @StartTime = DATEADD(HOUR, (@ScheduleStartTime / POWER(2,21)) % POWER(2, 5), @StartTime)
115        SET @StartTime = DATEADD(MINUTE, (@ScheduleStartTime / POWER(2,26)) % POWER(2, 5), @StartTime)
116
117        -- Determine UTC and Flags and Offset Days
118        DECLARE @IsUTCTime     BIT; SET @IsUTCTime     = CAST(@ScheduleDuration % POWER(2, 1) AS BIT)
119        DECLARE @Flags         INT; SET @Flags         = (@ScheduleDuration / POWER(2,19)) % POWER(2, 3)
120        DECLARE @OffsetDays    INT; SET @OffsetDays    = (@ScheduleDuration / POWER(2,6)) % POWER(2, 3)
121
122        -- Calculate the total duration in minutes
123        SET @Duration = @Duration + ((@ScheduleDuration / POWER(2,22)) % POWER(2, 5)) * 24 * 60 -- DAYS
124        SET @Duration = @Duration + ((@ScheduleDuration / POWER(2,27)) % POWER(2, 5)) * 60      -- HOURS
125
126        DECLARE @Now DATETIME
127
128        IF @IsUTCTime = 1 BEGIN
129            SET @Now = GETUTCDATE()
130        END ELSE BEGIN
131            SET @Now = GETDATE()
132        END
133
134        DECLARE @NextMaintenanceWindow DATETIME
135
136        IF @RecurrenceType = @RecurrenceType_NONE BEGIN
137            IF DATEADD(MINUTE, @Duration, @StartTime) > @Now BEGIN
138                SET @NextMaintenanceWindow = @StartTime
139            END
140        END ELSE IF @RecurrenceType = @RecurrenceType_DAILY BEGIN
141            IF DATEADD(MINUTE, @Duration, @StartTime) > @Now BEGIN
142                SET @NextMaintenanceWindow = @StartTime
143            END ELSE BEGIN
144                -- Calculate the daily interval in minutes
145                DECLARE @DailyInterval INT
146
147                SET @DailyInterval = ((@ScheduleDuration / POWER(2,3)) % POWER(2, 5)) * 24 * 60
148                SET @DailyInterval = @DailyInterval + ((@ScheduleDuration / POWER(2,8)) % POWER(2, 5)) * 60
149                SET @DailyInterval = @DailyInterval + (@ScheduleDuration / POWER(2,13)) % POWER(2, 6)
150
151                -- Calculate the total number of completed intervals
152                DECLARE @DailyNumberOfCompletedIntervals INT; SET @DailyNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(MINUTE, @StartTime, @Now) AS DECIMAL) / @DailyInterval, 0, 0)
153
154                -- Calculate the next interval
155                DECLARE @DailyNextInterval DATETIME; SET @DailyNextInterval = DATEADD(MINUTE, @DailyNumberOfCompletedIntervals * @DailyInterval, @StartTime)
156
157                -- Recalculate the next interval if the next interval plus the expected duration is in the past
158                IF DATEADD(MINUTE, @Duration, @DailyNextInterval) < @Now BEGIN
159                    SET @DailyNextInterval = DATEADD(MINUTE, (@DailyNumberOfCompletedIntervals + 1) * @DailyInterval, @StartTime)
160                END
161
162                SET @NextMaintenanceWindow = @DailyNextInterval
163            END
164        END ELSE IF @RecurrenceType = @RecurrenceType_WEEKLY BEGIN
165            DECLARE @WeeklyInterval INT; SET @WeeklyInterval = (@ScheduleDuration / POWER(2,13)) % POWER(2, 3)
166            DECLARE @WeeklyDoW      INT; SET @WeeklyDoW      = (@ScheduleDuration / POWER(2,16)) % POWER(2, 3)
167
168            -- Adjust the start time to match the next day of week that matches the interval
169            DECLARE @WeeklyStartTime DATETIME; SET @WeeklyStartTime = DATEADD(DAY, (7 - DATEPART(WEEKDAY, @StartTime) + @WeeklyDoW % 7), @StartTime)
170
171            IF DATEADD(MINUTE, @Duration, @WeeklyStartTime) > @Now BEGIN
172                SET @NextMaintenanceWindow = @WeeklyStartTime
173            END ELSE BEGIN
174                -- Calculate the total number of completed intervals
175                DECLARE @WeeklyNumberOfCompletedIntervals INT; SET @WeeklyNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(WEEK, @WeeklyStartTime, @Now) AS DECIMAL) / @WeeklyInterval, 0, 0)
176
177                -- Calculate the next interval
178                DECLARE @WeeklyNextInterval DATETIME; SET @WeeklyNextInterval = DATEADD(WEEK, @WeeklyNumberOfCompletedIntervals * @WeeklyInterval, @WeeklyStartTime)
179
180                -- Recalculate the next interval if the next interval plus the expected duration is in the past
181                IF DATEADD(MINUTE, @Duration, @WeeklyNextInterval) < @Now BEGIN
182                    SET @WeeklyNextInterval = DATEADD(WEEK, (@WeeklyNumberOfCompletedIntervals + 1) * @WeeklyInterval, @WeeklyStartTime)
183                END
184
185                SET @NextMaintenanceWindow = @WeeklyNextInterval
186            END
187        END ELSE IF @RecurrenceType = @RecurrenceType_MONTHLYBYWEEKDAY OR @RecurrenceType = @RecurrenceType_MONTHLYBYWEEKDAYBASE BEGIN
188            DECLARE @MonthlyBWWeek     INT; SET @MonthlyBWWeek     = (@ScheduleDuration / POWER(2,9)) % POWER(2, 3)
189            DECLARE @MontlhyBWInterval INT; SET @MontlhyBWInterval = (@ScheduleDuration / POWER(2,12)) % POWER(2, 4)
190            DECLARE @MonthlyBWDoW      INT; SET @MonthlyBWDoW      = (@ScheduleDuration / POWER(2,16)) % POWER(2, 3)
191
192            -- Calculate the total number of completed intervals
193            DECLARE @MonthlyBWNumberOfCompletedIntervals INT; SET @MonthlyBWNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(MONTH, @StartTime, @Now) AS DECIMAL) / @MontlhyBWInterval, 0, 0)
194
195            IF @MonthlyBWWeek = 0 BEGIN
196                -- Calculate the next interval
197                DECLARE @MonthlyBWLDOMNextInterval DATETIME; SET @MonthlyBWLDOMNextInterval = DATEADD(MONTH, @MonthlyBWNumberOfCompletedIntervals * @MontlhyBWInterval, @StartTime)
198
199                -- Calculate last day of month
200                SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBWLDOMNextInterval, DATEADD(DAY, -1, DATEADD(M, DATEDIFF(MONTH, 0, @MonthlyBWLDOMNextInterval) + 1, 0))), @MonthlyBWLDOMNextInterval)
201
202                -- Calculate the last day of the week for the month
203                SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, -(7 - DATEPART(WEEKDAY, @MonthlyBWLDOMNextInterval) + @MonthlyBWDoW % 7), @MonthlyBWLDOMNextInterval)
204
205                IF DATEADD(MINUTE, @Duration, @MonthlyBWLDOMNextInterval) < @Now BEGIN
206                    -- Recalculate for the next month interval
207                    SET @MonthlyBWLDOMNextInterval = DATEADD(MONTH, (@MonthlyBWNumberOfCompletedIntervals + 1) * @MontlhyBWInterval, @StartTime)
208
209                    -- Calculate last day of month
210                    SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBWLDOMNextInterval, DATEADD(DAY, -1, DATEADD(M, DATEDIFF(MONTH, 0, @MonthlyBWLDOMNextInterval) + 1, 0))), @MonthlyBWLDOMNextInterval)
211
212                    -- Calculate the last day of the week for the month
213                    SET @MonthlyBWLDOMNextInterval = DATEADD(DAY, -(7 - DATEPART(WEEKDAY, @MonthlyBWLDOMNextInterval) + @MonthlyBWDoW % 7), @MonthlyBWLDOMNextInterval)
214                END
215
216                SET @NextMaintenanceWindow = DATEADD(DAY, @OffsetDays, @MonthlyBWLDOMNextInterval)
217            END ELSE BEGIN
218                -- Calculate the next interval
219                DECLARE @MonthlyBWNextInterval DATETIME; SET @MonthlyBWNextInterval = DATEADD(MONTH, @MonthlyBWNumberOfCompletedIntervals * @MontlhyBWInterval, @StartTime)
220
221                -- Set the date to the first day of the month
222                SET @MonthlyBWNextInterval = DATEADD(DAY, -(DAY(@MonthlyBWNextInterval) - 1), @MonthlyBWNextInterval)
223
224                -- Set the date to the first day of week in the month
225                SET @MonthlyBWNextInterval = DATEADD(DAY, (7 - DATEPART(WEEKDAY, @MonthlyBWNextInterval) + @MonthlyBWDoW) % 7, @MonthlyBWNextInterval)
226
227                -- Calculate date based on the week number to add
228                SET @MonthlyBWNextInterval = DATEADD(WEEK, @MonthlyBWWeek-1, @MonthlyBWNextInterval)
229
230                IF DATEADD(MINUTE, @Duration, @MonthlyBWNextInterval) < @Now BEGIN
231                    -- Recalculate for the next month interval
232                    SET @MonthlyBWNextInterval = DATEADD(MONTH, (@MonthlyBWNumberOfCompletedIntervals + 1) * @MontlhyBWInterval, @StartTime)
233
234                    -- Set the date to the first day of the month
235                    SET @MonthlyBWNextInterval = DATEADD(DAY, -(DAY(@MonthlyBWNextInterval) - 1), @MonthlyBWNextInterval)
236
237                    -- Set the date to the first day of week in the month
238                    SET @MonthlyBWNextInterval = DATEADD(DAY, (7 - DATEPART(WEEKDAY, @MonthlyBWNextInterval) + @MonthlyBWDoW % 7), @MonthlyBWNextInterval)
239
240                    -- Calculate date based on the week number to add
241                    SET @MonthlyBWNextInterval = DATEADD(WEEK, @MonthlyBWWeek-1, @MonthlyBWNextInterval)
242                END
243
244                SET @NextMaintenanceWindow = DATEADD(DAY, @OffsetDays, @MonthlyBWNextInterval)
245            END
246        END ELSE IF @RecurrenceType = @RecurrenceType_MONTHLYBYDATE BEGIN
247            DECLARE @MontlhyBDInterval INT; SET @MontlhyBDInterval = (@ScheduleDuration / POWER(2,10)) % POWER(2, 4)
248            DECLARE @MonthlyBDDoM      INT; SET @MonthlyBDDoM      = (@ScheduleDuration / POWER(2,14)) % POWER(2, 5)
249
250            IF @MonthlyBDDoM = 0 BEGIN
251                /* This is the last day of month logic */
252
253                -- Calculate the total number of completed intervals
254                DECLARE @MonthlyBDLDOMNumberOfCompletedIntervals INT; SET @MonthlyBDLDOMNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(MONTH, @StartTime, @Now) AS DECIMAL) / @MontlhyBDInterval, 0, 0)
255
256                -- Calculate the next interval
257                DECLARE @MonthlyBDLDOMNextInterval DATETIME; SET @MonthlyBDLDOMNextInterval = DATEADD(MONTH, @MonthlyBDLDOMNumberOfCompletedIntervals * @MontlhyBDInterval, @StartTime)
258
259                -- Calculate last day of month
260                SET @MonthlyBDLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBDLDOMNextInterval, DATEADD(DAY, -1, DATEADD(M, DATEDIFF(MONTH, 0, @MonthlyBDLDOMNextInterval) + 1, 0))), @MonthlyBDLDOMNextInterval)
261
262                -- Recalculate the next interval if the next interval plus the expected duration is in the past
263                IF DATEADD(MINUTE, @Duration, @MonthlyBDLDOMNextInterval) < @Now BEGIN
264                    SET @MonthlyBDLDOMNextInterval = DATEADD(DAY, DATEDIFF(DAY, @MonthlyBDLDOMNextInterval, DATEADD(DAY, -1, DATEADD(M, DATEDIFF(MONTH, 0, DATEADD(MONTH, (@MonthlyBDLDOMNumberOfCompletedIntervals + 1) * @MontlhyBDInterval, @StartTime)) + 1, 0))), @MonthlyBDLDOMNextInterval)
265                END
266
267                SET @NextMaintenanceWindow = @MonthlyBDLDOMNextInterval
268            END ELSE BEGIN
269                -- Check to make sure we won't loop forever if more than 31 days some how ends up in the token
270                IF @MonthlyBDDoM > 31 SET @MonthlyBDDoM = 31
271
272                -- Adjust the start time to match the next day of month that matches the interval
273                DECLARE @MonthlyBDStartTime DATETIME; SET @MonthlyBDStartTime = DATEADD(DAY, (31 - DATEPART(DAY, @StartTime) + @MonthlyBDDoM % 31), @StartTime)
274
275                -- This loop is used multiple times to search for the next valid date that falls on the desired day of month
276                WHILE(DATEPART(DAY, @MonthlyBDStartTime) <> @MonthlyBDDoM) BEGIN
277                    SET @MonthlyBDStartTime = DATEADD(DAY, (31 - DATEPART(DAY, @MonthlyBDStartTime) + @MonthlyBDDoM) % 31, @MonthlyBDStartTime)
278                END
279
280                IF DATEADD(MINUTE, @Duration, @MonthlyBDStartTime) > @Now BEGIN
281                    SET @NextMaintenanceWindow = @MonthlyBDStartTime
282                END ELSE BEGIN
283                    -- Calculate the total number of completed intervals
284                    DECLARE @MonthlyBDNumberOfCompletedIntervals INT; SET @MonthlyBDNumberOfCompletedIntervals = ROUND(CAST(DATEDIFF(MONTH, @MonthlyBDStartTime, @Now) AS DECIMAL) / @MontlhyBDInterval, 0, 0)
285
286                    -- Calculate the next interval
287                    DECLARE @MonthlyBDNextInterval DATETIME; SET @MonthlyBDNextInterval = DATEADD(MONTH, @MonthlyBDNumberOfCompletedIntervals * @MontlhyBDInterval, @MonthlyBDStartTime)
288
289                    WHILE(DATEPART(DAY, @MonthlyBDNextInterval) <> @MonthlyBDDoM) BEGIN
290                        SET @MonthlyBDNextInterval = DATEADD(DAY, (31 - DATEPART(DAY, @MonthlyBDNextInterval) + @MonthlyBDDoM % 31), @MonthlyBDNextInterval)
291                    END
292
293                    -- Recalculate the next interval if the next interval plus the expected duration is in the past
294                    IF DATEADD(MINUTE, @Duration, @MonthlyBDNextInterval) < @Now BEGIN
295                        SET @MonthlyBDNextInterval = DATEADD(MONTH, (@MonthlyBDNumberOfCompletedIntervals + 1) * @MontlhyBDInterval, @MonthlyBDNextInterval)
296
297                        WHILE(DATEPART(DAY, @MonthlyBDNextInterval) <> @MonthlyBDDoM) BEGIN
298                            SET @MonthlyBDNextInterval = DATEADD(DAY, (31 - DATEPART(DAY, @MonthlyBDNextInterval) + @MonthlyBDDoM % 31), @MonthlyBDNextInterval)
299                        END
300                    END
301
302                    SET @NextMaintenanceWindow = @MonthlyBDNextInterval
303                END
304            END
305        END
306
307        /* Check if the maintenance window is open */
308        IF DATEADD(mi, @Duration, @NextMaintenanceWindow) > CURRENT_TIMESTAMP AND @NextMaintenanceWindow < CURRENT_TIMESTAMP
309            SET @IsServiceWindowOpen = 1 -- 1 = Open, 0 = Closed
310
311        /* Create result table */
312        INSERT INTO @NextServiceWindow VALUES (@ScheduleToken, @RecurrenceType, @StartTime, @NextMaintenanceWindow, @Duration, @IsServiceWindowOpen, @IsUTCTime)
313
314        /* Return result */
315        RETURN
316    END
317/* #endregion */
318
319/* #endregion */
320/*##=============================================*/
321/*## END QUERY BODY                              */
322/*##=============================================*/