Automatic Deployment Rules (ADR) are great! They simplify so much of the patching process. That helps us save time, and also helps us in avoiding human errors. But they have a few minor inconveniences like:
- The SUG being generated does not have the best naming convention.
- We do not have a sophisticated deployment configuration as part of the ADR
- Although we can preview the updates being deployed, we do not get a good idea about what exactly are we missing, or not deploying
The last point is one of the major concerns for many administrators. They feel like ADR is like this black box and they don’t have the control and the visibility about what exactly they are deploying. The visibility that comes with hand-picking the updates manually. So that’s what we’re going to solve today.
The script is going to extract all the new updates that Microsoft released after patch Tuesday, extract all the updates in the different SUGs, export them into an Excel file, do some additional customizations on them in order to improve visibility, and mail it to the team.
Before that, just want to give a shoutout to Travis Roberts and Doug Finke. Their contributions to the community in the form of Get-PatchTuesday and ImportExcel helped a lot in building this solution.
You can find the script on my Github. Keep scrolling to find out how exactly it is working.
Pre-requisites
- You need to have the ImportExcel module installed on the device you are trying to run the script from. You can do that through PowerShell Gallery by writing the below command. Just accept a few prompts that you get after running the command and you should be good to go.
Install-Module -Name ImportExcel- You need to run it on a device where the Configuration Manager console is installed.
- The account running the script should have appropriate permissions in Configuration Manager.
Importing the ConfigMan Module
The script starts with the code to import the Configuration Manager module. You’ll need to change the highlighted values as per your own environment configuration.
Get Current Month
As mentioned in the comment, this is an optional section. This will come into the picture if your SUG name format contains the year and/or name of the month. Something like this:
Year – Month – Type of Patch
In this case, we are just getting the date and converting the month number to its equivalent name.
Get Patch Tuesday
This section is also fairly easy to figure out. As mentioned previously, this code is picked up from Travis’ Github. We just created another variable and called the Get-PatchTuesday function to store the value of Patch Tuesday into that.
Excel Crayons
This next section may be a little tough to understand at first, but it’s really easy once I give you some context. This is the “additional customization” that I talked about in the beginning. All we’re doing is getting the text that we want to highlight in Excel and deciding on the color. So the Cumulative updates are going to be colored red and Security Only updates are going to be colored Yellow. This way, it will be easier to spot them while viewing the file.
We are also storing the name pattern of our SUGs in variables along with the path to the output file. You may need to modify it as per your environment standards.
These cmdlets are part of the ImportExcel module that we installed as part of the pre-requisites.
Update Extraction + Excel Export
The final section deals with the actual extraction of updates from the different SUGs, and what Microsoft pushed. I have highlighted a few key areas:
- $cat variable stores data about all the Products that we have subscribed to in the SUP configuration.
- $patchTuesdayValue is being used as a parameter to only check for updates pushed after the current patch Tuesday while trying to extract all updates that Microsoft pushed.
- WorkSheetName is pretty straightforward.
- You can also view the customizations done in the previous section as part of the command ($MQRNaming1, $MQRNaming2, $MQRNaming3, $SecNaming, $MQRNaming4).
Output
Once everything is configured as it’s supposed to, the output file that’s emailed to the team should look something like this (without the blurring, of course):
- We have different sheets for different SUGs
- The Security Only updates are highlighted in yellow
- Cumulative updates are in red
Thus, the MEMCM admin is going to get a little more visibility into what exactly is getting deployed through ADR and how it compares with everything else that Microsoft pushed for the current month.
