While being in the process of migrating Windows Software Update Services 3.x to Configuration Manager 2012 you have only one really supported solution, namely adding all approved software updates to Software Update Group manually. This is a lot of work when being in an environment that was operational for years and years with lots of products and approved updates. Just adding all updates isn’t an option either since all approved and declined updates are tested throughout the years. When looking on the Internet for a workaround I came across this workaround for Configuration Manager 2007.
Basically what is described is connecting to the WSUS SQL database (WID or SQL), export all ArticleIDs of the approved updates from the SUSDB database, match those exported ArticleID’s with the synced Software Updates in Configuration Manager and export the UpdateID’s and add the updates with the exported UpdateID’s to a Software Update List in Configuration Manager 2007 or in this case Update Group in Configuration Manager 2012. For the last step I wrote an import script that can be used to create Update Groups with the in WSUS approved updates, using the Native Configuration Manager 2012 SP1/R2 cmdlets.
Again, below is based on the workaround for Configuration Manager 2007, so many thanks to DT.
The first step is exporting all of the ArticleID’s of the approved updates from the WSUS database, this is done by executing a query against the database. If you installed the SUSDB on a full SQL version you should easily able to do this, if you are using the Windows Internal Database (WID) you need to use a commandline tool called OSQL.
Connecting to the WID can be done as follows:
osql -E -S \.pipeMSSQL$MICROSOFT##SSEEsqlquery -o d:export.txt
After being connected to the WID, we need to connect to the database, execute the following commands, the -o parameter will export all results to the defined file:
select distinct KnowledgebaseArticle from PUBLIC_VIEWS.vUpdate UPD
join PUBLIC_VIEWS.vUpdateApproval APP on upd.UpdateId = app.UpdateId
order by KnowledgebaseArticle
Now that we have a list of ArticleID’s we need to clean up all rubbish and lines with Null or six zeros. Next we need to create a CSV file of this list so that we are able to use the CSV format in a SQL query in the VBScript as explained here. Below you see the codesnipped you need to change in red, see for the complete script the blog explaining the process.
Query1 = "Select * from SMS_SoftwareUpdate where Articleid in ('940060','940357',’960123’)"
Paste the CSV formatted list of articleID’s in this query and execute the VB Script and be sure to save the output to a file that we need to use with the new PowerShell script. Running the VB script like shown below will result in a list of Software Update ID’s which we are able to use when we want to add updates to s Software Update Group with the native Configuration Manager 2012 R2 PowerShell cmdlets.
The PowerShell script can be downloaded here and works as follows:
- Be sure that the Software Updates of the enabled products and categories are synchronized and available in Configuration Manager
- Enable only the products that you still want to support with Configuration Manager 2012. Only updates that are available will be added to a Software Update Group.
- Export the ArticleID’s and match them with UpdateIDs like described above.
What does it?
Based on the approved WSUS updates in an updates.txt file, the script creates for every 999 approved updates a Software Update Group which you are able to deploy to your collections. You cannot have more than 1000 Software Updates in one deployment so that’s why I limited the script to not allow more than 999 updates in a Software Update Group. The script will only add updates to the Software Update Group that are available within Configuration Manager 2012, so if you leave out old operating systems like Windows XP or Windows Server 2003 those updates will not be imported. The script will save a logfile with the imported and not imported software updates.
After the Software Update Groups are created you are able to clean them up by removing the membeship of updates that you do not want to install anymore.
How to use it:
In the PowerShell Script you need to set a couple of parameters and you are good to go. Place the approved.txt file in the same folder as the script.
$sitecode = "PS1:"
$installdrive = "D:"
$loglocation = "D:Logfiles"
Tested in the following scenarios:
This script is tested in the following environments:
- WSUS 3.2 + WID with +2900 approvals
- Configuration Manager 2012 SP1 CU3
- Configuration Manager 2012 R2
Please let me know what you think!
###Update: Just released the blog and script and I find another solution in the Gallery from Saud Al-Mishari which you can find here: