SCCM/WSUS – Clean-up WSUS Strategy & Maintenance

( Note: Ensure you take a backup of your WSUS database before you proceed any further)

One of the most overlooked exercises when it comes to maintaining our WSUS environment is having a steady clean-up process.

Over time with schedules such as Patch Tuesdays or any regular patching intervals or aggressive synchronizations of updates, classifications and categories which are not needed, several of these things can happen.

·Huge amount of Obsolete Updates

·Huge WSUS Database

·Slowed down performance of patching

·WSUS Console cannot be accessed

These can cause huge issues within your environments if little to no clean-ups have actually been ran.

I’ve faced this at many customer sites and have created a couple of scripts for an overall solution strategy on how to keep everything clean.

Prerequisite – Using SQL instance rather than WID

I would recommend that you are using a SQL instance rather than a Windows Internal Database (WID). If needing to convert them here is a guide on how to do this

https://docs.microsoft.com/en-us/windows-server/administration/windows-server-update-services/manage/wid-to-sql-migration

Removal of Obsolete Updates

This particular part is what can usually kill your processes and resources on your server as well as prevent you from accessing the WSUS console and causing some horrible logs in your WSUSCTRL.log files.

And if this gets huge to the point that it’s in thousands, then the clean-up wizard will not work at all as it tends to get stuck or come to a halt and internally crashing the application, which then causes a SQL deadlock on the WSUS database which can halt all WSUS processes.

So the first thing we want to do is identify how many obsolete updates exist, so please perform the following;

·Open SQL Management Studios

·Connect to the SQL Database server with the WSUS Database

·Open a new query

·Type execute spGetObsoleteUpdatestocleanup

This should give you a list of every single update which is not used or needed. And if this is in several thousands then this will cause a huge bottleneck.

So what we will want to do is to add the list of these updates and add them into a .txt file for now so that we can format a list of commands to run as a SQL query to get rid of them.

So once we create the text fie we’ll want to output them as commands to remove them via a SQL query so below are some PowerShell commands to perform this.

$CSV = Import-CSV “<Location>\Updates.txt” -Header Updates

$CSV | ForEach-Object {“exec SPDeleteUpdate @localUpdateID=” + $_.Updates} | Out-File <Location>\DeleteUpdates.txt

This will take the text file you had created and concatenate each update with a command to remove them.

Once done copy everything from that text file and paste into a query in the SQL management studios so that it can begin.

You can open an extra query window to run the “execute spGetObsoleteUpdatesToCleanup” to check its progress, may take a couple of tries whilst its busy.

Once this does eventually complete, then run the command again above, once this shows s 0 then do the following;

1.Open the WSUS Console

2.Go to Administration – WSUS Clean-up Wizard

3.Run the Clean Obsolete Updates Option. This should go through quick-ish as well as cleaning up any update revisions.

4.Go through the clean-up wizard and tick each option to run individually. Once this has been done then run the whole clean-up wizard until it completes.

5.Then run the WSUS Re-Indexing script which can be found here - https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61

6.Go back to the SQL Management Studios and connect to the WSUS Database. Then shrink the WSUS database to gain its space back.

Clean-Up Scripts Going Forward

Now that the major clean-up has been completed, we want to prevent this situation from happening again so what we can use is a PowerShell script which I wrote that can go through the clean-up processes.

I would suggest to run this every 2 weeks, or depending how aggressive your schedules are then perhaps every week.

These scripts would be added to a scheduled task which would run on this interval.

So, we have two scripts

Windows Server 2012 & Higher

This script is for Server 2012 & higher and will go through each clean-up step and produce a log file with a location of your choice with appropriate time stamping. You can run this ad-hoc or attach to a Scheduled Task

# Create Log File

$Timestamp = Get-Date -format dd-MM-yyyy-HH-mm-ss

$WeekStamp = Get-Date -UFormat %V

$LogFile = "<Location>\WSUSCleanup$timestamp.txt"

# Get Current WSUS Server

Add-Content $LogFile "Script Started at $Timestamp"

$wsus = Get-WSUSServer

Add-Content $LogFile "Current WSUS Server is $wsus"

# Cleanup Steps

Add-Content $LogFile ""

Add-Content $LogFile "Starting cleanup steps"

Add-Content $LogFile "Cleanup Obsolete Updates"

$WSUS | Invoke-WsusServerCleanup -CleanupObsoleteUpdates | Add-Content $Logfile

Add-Content ""

Add-Content $LogFile "Cleanup Obsolete computers"

$WSUS | Invoke-WsusServerCleanup -CleanupObsoleteComputers | Add-Content $Logfile

Add-Content ""

Add-Content $LogFile "Cleanup Unneeded Content Files"

$WSUS | Invoke-WsusServerCleanup -CleanupUnneededContentFiles | Add-Content $Logfile

Add-Content ""

Add-Content $LogFile "Declined Expired Updates"

$WSUS | Invoke-WsusServerCleanup -DeclineExpiredUpdates | Add-Content $Logfile

Add-Content ""

Add-Content $LogFile "Declined Superseded Updates"

$WSUS | Invoke-WsusServerCleanup -DeclineSupersededUpdates | Add-Content $Logfile

Windows Server 2008

Though the PowerShell modules can be upgraded and installed, by default the WSUS role doesn’t have a PowerShell module specifically for it, so as a workaround I had done another script which is suitable for this version.

Same as the script above which can be run ad-hoc and also be attached to a scheduled task.

# Create Log File

$Timestamp = Get-Date -format dd-MM-yyyy-HH-mm-ss

$WeekStamp = Get-Date -UFormat %V

$LogFile = "<Location>WSUSCleanup$timestamp.txt"

# Get Current WSUS Server

Add-Content $LogFile "Script Started at $Timestamp"

#$wsus = Get-WSUSServer

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")

$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer('FNEGLWSUS01',$False,"8530")

Add-Content $LogFile Current WSUS Server is $wsus.name

# Cleanup Module

$cleanupscope = New-Object Microsoft.UpdateServices.Administration.Cleanupscope

# Cleanup Obsolete Updates

$cleanupscope.CleanupObsoleteUpdates = $true

Add-Content $LogFile ""

Add-Content $LogFile "Starting cleanup steps"

Add-Content $LogFile "Cleanup Obsolete Updates"

$cleanupManager = $wsus.GetCleanupManager()

$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile

# Cleanup Obsolete Computers

$cleanupscope.CleanupObsoleteComputers = $true

$cleanupscope.CleanupObsoleteUpdates = $false

Add-Content $LogFile ""

Add-Content $LogFile "Cleanup Obsolete computers"

$cleanupManager = $wsus.GetCleanupManager()

$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile

# Cleanup Content Files

$cleanupscope.CleanupObsoleteComputers = $false

$cleanupscope.CleanupObsoleteUpdates = $false

$cleanupscope.CompressUpdates = $true

Add-Content $LogFile ""

Add-Content $LogFile "Cleanup Unneeded Content Files"

$cleanupManager = $wsus.GetCleanupManager()

$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile

# Cleanup Declined Updates

$cleanupscope.CleanupObsoleteComputers = $false

$cleanupscope.CleanupObsoleteUpdates = $false

$cleanupscope.CompressUpdates = $false

$cleanupscope.DeclinedExpiredUpdates = $true

Add-Content $LogFile ""

Add-Content $LogFile "Declined Expired Updates"

$cleanupManager = $wsus.GetCleanupManager()

$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile

# Declined Superseded Updates

$cleanupscope.CleanupObsoleteComputers = $false

$cleanupscope.CleanupObsoleteUpdates = $false

$cleanupscope.CompressUpdates = $false

$cleanupscope.DeclinedExpiredUpdates = $false

$cleanupscope.DeclinedSupersededUpdates = $true

Add-Content $LogFile ""

Add-Content $LogFile "Declined Superseded Updates"

$cleanupManager = $wsus.GetCleanupManager()

$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile

$cleanupscope.DeclinedSupersededUpdates = $false

Future Reference

Whilst the scripts or scheduled tasks do run on intervals, you may want to use the Re-Indexing script perhaps once a month afterwards.