Dark mode

Dark mode

There are 0 results matching

article card image dark article card image light

Published by · Jun 18, 2024 tools · 2 mins read

Introducing: Configuration Manager Set Implicit Uninstall Flag Tool

Setting Configuration Manager ConfigMgr Implicit Uninstall Flag with PowerShell for Required Application Deployments ...

See More
article card image dark article card image light

Published by · Jun 11, 2024 configmgr · 2 mins read

Configuration Manager Next Maintenance Window SQL Function

Get Next Configuration Manager Maintenance Window from a Schedule Token with Offset Days using an SQL Function. ...

See More
article card image dark article card image light

Published by · Jun 3, 2024 tools · 2 mins read

Introducing: Windows User Rights Assignment Tool - Part 3

Add, Remove, or Replace Windows Rights Assignment with our PowerShell Tool. ...

See More
article card image dark article card image light

Published by · May 28, 2024 tools · 2 mins read

Introducing: Windows User Rights Assignment Tool - Part 2

Get and Report Windows Rights Assignment with our PowerShell Tool. ...

See More
article card image dark article card image light

Published by · May 22, 2024 tools · 1 mins read

Introducing: Windows User Rights Assignment Tool - Part 1

Get Windows Rights Assignment with our PowerShell Tool. ...

See More
article card image dark article card image light

Published by · Apr 11, 2024 tools · 2 mins read

Introducing: Intune Linux Onboarding Tool

Onboard Ubuntu Linux devices to Microsoft Intune using a bash script. Installs prerequisites and starts the user-driven enrollment. ...

See More
article card image dark article card image light

Published by · Apr 11, 2024 tools · 2 mins read

Introducing: Intune macOS Onboarding Tool

Onboard macOS devices to Microsoft Intune using a bash script that initiates the process. Optionally, the script converts mobile accounts, resets the FileVault key, and removes ...

See More
article card image dark article card image light

Published by · Jan 23, 2024 tools · 3 mins read

Introducing: Intune Device Renaming Tool

Rename Intune Devices by setting a Prefix or using a User Attribute as Prefix. Supports Windows, macOS, and Linux ...

See More
article card image dark article card image light

Published by · Dec 8, 2023 intune · 5 mins read

Intune Logs: A Deep Dive into Locations, Interpretation, and Configuration

A Comprehensive Guide to Locations, Interpretation, and Configuration of Intune Logs ...

See More
article card image dark article card image light

Published by · Aug 14, 2023 configmgr · 2 mins read

Configuration Manager Console Extension to show Device Collection Membership with Console Builder

Use the Configuration Manager Console Builder, to add Collection Membership View to the Device Node ...

See More
article card image dark article card image light

Published by · Aug 3, 2023 tools · 3 mins read

Introducing: Configuration Manager SSRS Dashboards

A Configuration Manager Dashboards solution with Reports for Software Updates, Bitlocker and more ...

See More
article card image dark article card image light

Published by · Aug 3, 2023 tools · 2 mins read

Introducing: PowerShell WMI Management Toolkit Module

Streamline your WMI Namespace, Class, and Instance Management with our PowerShell Module ...

See More
article card image dark article card image light

Published by · Jul 14, 2023 configmgr · 1 mins read

Configuration Manager detailed, filterable Port Documentation

Configuration Manager detailed, filterable port documentation as an excel document ...

See More
article card image dark article card image light

Published by · Jul 14, 2023 configmgr · 3 mins read

Configuration Manager PXE TFTP Window Size Bug

Configuration Manager TFTP Block Size and TFTP Window Size Correct Configuration ...

See More
article card image dark article card image light

Published by · Jun 18, 2023 tools · 4 mins read

Introducing: Configuration Manager Client Cache Cleanup Tool

Cleaning the Configuration Manager Client Cache the Right Way with PowerShell and Configuration Baselines ...

See More
article card image dark article card image light

Published by · Jun 18, 2023 tools · 2 mins read

Introducing: Windows Cache Cleanup Tool

Cleaning Windows and Configuration Manager Caches for Configuration Manager Build and Capture Task Sequence or Standalone Use ...

See More
article card image dark article card image light

Published by · Jun 17, 2023 tools · 1 mins read

Introducing: Windows Update Database Reinitialization Tool

Proactively repair corrupted Windows Update Database with Powershell and Configuration Manager ...

See More
article card image dark article card image light

Published by · Mar 31, 2023 tools · 3 mins read

Introducing: Configuration Manager SQL Products Reporting

A Complete SQL Products reporting solution using Configuration Manager ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 configmgr · 1 mins read

Application Detection Method using the Configuration Manager Application Version

Replace hardcoded application version in scripts, with the Configuration Manager Application Version ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 tools · 3 mins read

Introducing: Certificate Management Toolkit

Managing Certificates with Configuration Manager and PowerShell by using just the Public Key ...

See More
article card image dark article card image light

Published by · Jan 7, 2019 reports · 2 mins read

Configuration Manager Device Boundary and Network Information Report

List Device Boundaries and Network Information with Configuration Manager ...

See More
article card image dark article card image light

Published by · Sep 9, 1980 help · 5 mins read

MEM.Zone Blog Publishing Documentation

Publishing Documentation for MEM.Zone ...

See More

We couldn’t find anything related to

“SCCM”

BLOG / configmgr zone

Configuration Manager Next Maintenance Window SQL Function

Published by Popovici Ioan · Jun 11, 2024 · 2 mins read
article card image dark article card image light

Quick Summary

Getting the Next Maintenance Window with SQL is pretty complicated, fortunately Adam Weigert already created a great function for this purpose.

I took the liberty to modify it a bit fix a few bugs and add Offset Days support. Microsoft did not update it’s documentation on how to read the Offset Days from the Schedule Token so I had to dig a bit in order to get it right.

Prerequisites


Recurrence Types

The ScheduleToken class uses two DWORDs to store the schedule and there are 5 Recurrence Types as described in the documentation.

article card image configmgr-recurrence-types.jpg

However after some digging I found that there is a new 6th Recurrence Type called MONTHLYBYWEEKDAYBASE closely related to MONTHLYBYWEEKDAY.

MONTHLYBYWEEKDAY

SCHED_TOKEN_RECUR_MONTHLY_BY_WEEKDAY

  3 3 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1
  1 0 9 8 7 6 5 4 3 2 1 0 9 8 7 6 5 4 3 2 1 0 9 8 7 6 5 4 3 2 1 0
 +---------+---------+-----+-----+-------+-----+-----------------+
 | Duration| Duration|Flags| Week|Num of |Week |     Unused    |U|
 | Hours   | Days    |     | Day |months |Order|               |T|
 |         |         |     |     |       |     |               |C|
 +---------+---------+-----+-------------------------------------+
Notes

I wonder what section could be used here to store the new value? 😂

MONTHLYBYWEEKDAYBASE

The new MONTHLYBYWEEKDAY Recurrence Type uses the same structure as the MONTHLYBYWEEKDAY but makes use of the Unused bits at the 6th position to store the Offset Days value.

SCHED_TOKEN_RECUR_MONTHLY_BY_WEEKDAY_BASE

  3 3 2 2 2 2 2 2 2 2 2 2 1 1 1 1 1 1 1 1 1 1
  1 0 9 8 7 6 5 4 3 2 1 0 9 8 7 6 5 4 3 2 1 0 9 8 7 6 5 4 3 2 1 0
 +---------+---------+-----+-----+-------+-----+------+----------+
 | Duration| Duration|Flags| Week|Num of |Week | Off- | Unused |U|
 | Hours   | Days    |     | Day |months |Order| set  |        |T|
 |         |         |     |     |       |     | Days |        |C|
 +---------+---------+-----+-------------------------------------+

Parameters

ScheduleToken

Specifies the schedule token in the form of a DWORD.

RecurrenceType

Specifies the maintenance window recurrence type.
Accepted values are:

  • ‘1’ - None
  • ‘2’ - Daily
  • ‘3’ - Weekly
  • ‘4’ - Monthly by Weekday
  • ‘5’ - Monthly by Date
  • ‘6’ - Monthly by Weekday Base (Offset Days)

Code

The ufn_CM_GetNextMaintenanceWindow function returns the Next Maintenance Window and a few other for a given Schedule Token.

  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/*##=============================================*/

Preview

article card image ssms-ufn_CM_GetNextMaintenanceWindow.gif

SHARE

article card image dark article card image light

Published by · Mar 31, 2023 tools · 3 mins read

Introducing: Configuration Manager SQL Products Reporting

A Complete SQL Products reporting solution using Configuration Manager ...

See More
article card image dark article card image light

Published by · Jun 3, 2024 tools · 2 mins read

Introducing: Windows User Rights Assignment Tool - Part 3

Add, Remove, or Replace Windows Rights Assignment with our PowerShell Tool. ...

See More