Dark mode

Dark mode

There are 0 results matching

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 / tools zone

Introducing: Configuration Manager SSRS Dashboards

Published by Popovici Ioan · Aug 3, 2023 · 3 mins read
article card image dark article card image light

Quick Summary

Introducing a free solution of dashboards and reports, for Microsoft Endpoint Configuration Manager.
The wealth of data these reports offer is like nothing you have ever seen, and they can help you troubleshoot a wide array of problems.

Overview

Software Update Dashboard

The main navigable dashboard, independent of Software Update Groups and comes with an array of filtering options and sub-reports.

  • Site - Alerts
  • Software Update - Compliance by Collection
  • Software Update - Compliance by Device
  • Software Update - Scan Status by Collection
  • Software Update - Update Point Sync Status

Bitlocker - Compliance by Collection (MBAM)

A standalone complete solution for monitoring Bitlocker (MBAM) compliance and non-compliance reasons.

Bitlocker - Compliance by Collection

A standalone complete solution for monitoring Bitlocker compliance, key upload and non-compliance reasons.

Operating System - Version Compliance by Collection

A standalone solution for monitoring Feature Update or Windows Version Compliance.

Operating System - Windows 11 Readiness by Collection

A standalone solution for checking Windows 11 Upgrade Readiness.

Notes

The Dashboards can be installed using the provided PowerShell installer or manually.


Prerequisites

Package

Discovery

  • Add l additional user and device discovery attribute
  • Add co additional user and device discovery attribute
Notes

Run discovery after adding the l and co attributes.

Software

  • Configuration Manager with Windows Update Services integration.
  • SQL Server Reporting Services (SSRS) 2017 or above.
  • SQL Compatibility Level 130 or above.

SQL Function

Notes

The function will be automatically created by the installer script.
For manual installation see Create SQL Function.

SQL Rights

Additional SELECT rights need to be granted to the smsschm_users group for the objects below.

  • ufn_CM_GetNextMaintenanceWindow
  • fnListAlerts
  • vSMS_ServiceWindow
  • vSMS_SUPSyncStatus
Notes

Rights will be automatically granted by the installer script.
For manually granting permissions see Grant SQL Permissions.


Automatic Installation

## Get syntax help
Get-Help .\Install-SRSReport.ps1

## Install with extensions
.\Install-SRSReport.ps1 -ReportServerUri 'http://CM-SQL-RS-01A/ReportServer' -ReportFolder '/ConfigMgr_XXX/SRSDashboards' -ServerInstance 'CM-SQL-RS-01A' -Database 'CM_XXX' -Overwrite -Verbose

## Install without extensions (Permissions will still be granted on prerequisite views and tables)
.\Install-SRSReport.ps1 -ReportServerUri 'http://CM-SQL-RS-01A/ReportServer' -ReportFolder '/ConfigMgr_XXX/SRSDashboards' -ServerInstance 'CM-SQL-RS-01A' -Database 'CM_XXX' -ExcludeExtensions -Verbose

## Install Extensions only
.\Install-SRSReport.ps1 -ServerInstance 'CM-SQL-RS-01A' -Database 'CM_XXX' -ExtensionsOnly -Overwrite -Verbose
Notes

When using Basic Authentication with your SQL server, add the UseSQLAuthentication switch.
If the installer fails you can always do a Manual Installation.


Manual Installation

Upload reports to SSRS, update the data source, grant the necessary permissions and optionally install the helper function.

Upload Reports to SSRS

Notes

Reports must be placed in the same folder on the report server.

Create SQL Function

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

This step is optional. When skipped, the next maintenance window will not be displayed.

Grant SQL Permissions

  • Paste the code below in SSMS.
  • Click Execute to add the necessary permissions to your database.
 1/*
 2.SYNOPSIS
 3    Grants SQL permissions for report prerequisites.
 4.DESCRIPTION
 5    Grants SQL permissions for report prerequisites.
 6.EXAMPLE
 7    Run in SQL Server Management Studio (SSMS).
 8.NOTES
 9    Created by Ioan Popovici
10    Replace the <SITE_CODE> with your CM Site Code and uncomment SSMS region if running directly from SSMS.
11.LINK
12    https://MEM.Zone
13.LINK
14    https://MEMZ.one/Dashboards-GIT
15.LINK
16    https://MEMZ.one/Dashboards-ISSUES
17*/
18
19/*##=============================================*/
20/*## QUERY BODY                                  */
21/*##=============================================*/
22/* #region QueryBody */
23
24/* #region SSMS */
25-- USE [CM_<SITE_CODE>]
26-- GO
27/* #endregion */
28
29/* #region Grant Permissions */
30/* Grant select rights for this function to CM reporting users */
31GRANT SELECT ON OBJECT::dbo.ufn_CM_GetNextMaintenanceWindow TO smsschm_users;
32
33/* Grant select right for the fnListAlerts function to CM reporting users */
34GRANT SELECT ON OBJECT::dbo.fnListAlerts TO smsschm_users;
35
36/* Grant select right for the vSMS_ServiceWindow view to CM reporting users */
37GRANT SELECT ON OBJECT::dbo.vSMS_ServiceWindow TO smsschm_users;
38
39/* Grant select right for the vSMS_SUPSyncStatus view to CM reporting users */
40GRANT SELECT ON OBJECT::dbo.vSMS_SUPSyncStatus TO smsschm_users;
41/* #endregion */
42
43/* #endregion */
44/*##=============================================*/
45/*## END QUERY BODY                              */
46/*##=============================================*/

.
+-- (D) _Dashboard - Software Updates
    +-- (C) Device Update Compliance
    |   +-- (R) Software Update - Compliance by Collection
    |       +-- (R) Software Update - Compliance by Device
    |
    +-- (C) Missing updates by Classification
    |   +-- (R) Software Update - Compliance by Collection
    |       +-- (R) Software Update - Compliance by Device
    |
    +-- (C) Device Update Agent Scan States
    |   +-- (R) Software Update - Scan Status by Collection
    |
    +-- (C) Overall Update Groups Compliance
    |
    +-- (C) Top 5 Devices with Missing Updates by Classification
    |   +-- (R) Software Update - Compliance by Collection
    |       +-- (R) Software Update - Compliance by Device
    |
    +-- (T) Critical Alerts
    |   +-- (R) Site - Alerts
    |
    +-- (T) Last Successful Synchronization Time
        +-- (R) Software Update - Update Point Sync Status
+-- (R) Bitlocker - Compliance by Collection * Bitlocker (MBAM)
+-- (R) Bitlocker - Compliance by Collection * Bitlocker
+-- (R) Operating System - Version Compliance by Collection
+-- (R) Operating System - Windows 11 Readiness by Collection

## Legend
'()'  - 'to' or 'from' navigation element
'(D)' - Dashboard
'(R)' - Report
'(C)' - Chart
'(T)' - Text

Preview

Notes

This preview is not up to date, it represents version 2.0.0.

SHARE

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 · 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