SCCM Configmgr How to get clients maintenance window with custom dates (Past and Future )

Introduction:

Maintenance windows in Configmgr help to ensure that client configuration changes occur during periods that do not affect the productivity of the organization.

Following Operations can be performed during the Maintenance window:

• Software update deployments
• Compliance settings deployment and evaluation
• Operating system deployments
• Task sequence deployments

More about MW https://technet.microsoft.com/en-us/library/hh508762.aspx?f=255&MSPPError=-2147217396

Problem:

So ,If you want to know the client maintenance window for next few days (20 days or recently occurred in the last 20 days ) ,you have no built in report and for that, you must go with custom report to find out the clients that are undergoing the changes that are deployed by your SCCM team.

I had a requirement to create report to get client maintenance window with prompt to choose past (occurred) and future days (going to happen) or simply enter the client name to list available maintenance Windows.

Solution:

This blog post will help you to identify the clients have maintenance window setup for the next few days or recently occurred with some custom dates.

What you get with this report : list clients with their collection name, Maintenance Window Name, Description (effective date),Days ahead (If –(minus) ,it was past date ,else future date )

After you run this report, it will prompt you to choose Past (-10) and Future (10) .Past and Future is considered from Current date.

For Ex: Today is 24-Jan-2017 ,I have client PC001 in collection ABC with MW set 21-Jan-2017 and PC002 in collection CDE with MW set 28-Jan-2017 .So I choose Past as 3 days and future 10 days ,I should get all clients from ABC and CDE collections as their MW fall under past (2) and future date (10) .

If I choose past as 1 day and future 3 days ,I won’t get anything in the report as there is no client that has MW between 1 <--->3 from current date.

The logic used in the SQL is: Maintenance Window date should be <future date and >past date from the current Date as shown in below snippet.

How does the report look like ?

As usual, download the RDL file from TechNet Gallery here ,upload to your SSRS folder ,change the data source and run the report.