Skip to main content

Written by Jeff Carreon.

What's SUP???

What's SUP???

(Updated: 12/17/2021) NOTE: I have made SEVERAL improvements and fixes since this was blogged. So see below for all of the changes, and enjoy! LATEST version is 5.4.3, and can be downloaded from the link below.

What is up with SUPs??? (SUPs – Software Update Point servers… You know, the only antiquated server role in your CM hierarchy. :))

We’ve had so many SUP storms in our organization I have seriously lost count. This is where WSUS pools just get hammered constantly on the SUPs. CPU and RAM go thru the roof, clients are constantly generating errors or timing out, and network consumption on our low bandwidth sites flood to capacity. A recent storm literally halted our users from working at the branch sites due to high network consumption from all of the scanning and rescanning that were coming from the clients. Maybe storm is not the word, it was a hurricane!

We thought running the default WSUS maintenance on our SUPs periodically thru POSH cmdlets was enough. But something was clearly off. This left us scratching our heads trying to figure out exactly what was going on with our SUPs. We had been constantly searching for answers on how to tame our SUPs and constantly adjusting the pool and IIS settings. (Which I believe we’ve got it right this time. Check out my peer’s blog Sherry Kissinger regarding WSUSPool, web.config, and CI settings). But this time around, it seemed the clients were just not completely downloading all of the metadata.

We reached out to our dedicated MS support folks. We wanted to know if there was a way to identify and measure the metadata that the clients download. They gave us the TSQL below to run against the SUSDBs. It tells us which articles are deployable and their size. The recommendation was to go straight to WSUS console and decline the updates with large metadata that we’re not using. Hmm, we thought that could be a lot! Since we had never ever gone in WSUS console for anything! Who does, right? Since that’s always been the rule, never mess with WSUS console. NOT this time.

Run this SQL (from MS support) against your SUSDB to view all of the deployable updates you have. (This was separated into two queries, but Sherry put it together).

;with cte as (SELECT dbo.tbXml.RevisionID, ISNULL(datalength(dbo.tbXml.RootElementXmlCompressed), 0) as LENGTH FROM dbo.tbXml

INNER JOIN dbo.tbProperty ON dbo.tbXml.RevisionID = dbo.tbProperty.RevisionID

)--order by Length desc

select

u.UpdateID,

cte.LENGTH,

r.RevisionNumber,

r.RevisionID,

lp.Title,

pr.ExplicitlyDeployable as ED,

pr.UpdateType,

pr.CreationDate

from

tbUpdate u

inner join tbRevision r on u.LocalUpdateID = r.LocalUpdateID

inner join tbProperty pr on pr.RevisionID = r.RevisionID

inner join cte on cte.revisionid = r.revisionid

inner join tbLocalizedPropertyForRevision lpr on r.RevisionID = lpr.RevisionID

inner join tbLocalizedProperty lp on lpr.LocalizedPropertyID = lp.LocalizedPropertyID

where

lpr.LanguageID = 1033

and r.RevisionID in (

select

t1.RevisionID

from

tbBundleAll t1

inner join tbBundleAtLeastOne t2 on t1.BundledID=t2.BundledID

where

ishidden=0 and pr.ExplicitlyDeployable=1)

order by cte.length desc

Once we got the number of the articles that we had as deployable, we noticed that there were tons that updates that we were not using or have never really used. So clients were clearly downloading/scanning for all of these unnecessary articles, hence why we were seeing a lot of timeouts. Thus, cleaning up is what we needed to do by declining all of these updates in WSUS in attempt to make the metadata lean.

Meghan Stewart from MS has a really great guide for maintaining WSUS/Software Update Points. I grabbed the script from her post and enhanced it a little by adding functions for declining Itanium, Windows XP updates, IE, Embedded, etc... And more optional functions added recently (see below for details). For not only did we need to decline superseded ones, but we also needed to decline unused and unnecessary updates that are lingering around for no reason other than consuming space and network bandwidth during client scanning. And we had to find a way to automate this process so we could include it in our maintenance plan. Lastly, I added email reporting (new) along with event logging since we need SCOM to be able to pick up those errors/events so we can be alerted upon failures.

Prior to actually declining all of these unnecessary updates, we had over 14k articles that were marked as deployable. After running the script, we now have about less than 5k. A HUGE chunk was taken off and this obviously made the scanning times MUCH faster, timeouts when away, and network bandwidth consumption dropped significantly in no time. Script download link below.

LATEST UPDATE: ver. 5.4.3 (12/17/2021) Added the following (NEW!!!)

  • Added Metadata size comparison report (Requires Importing SQLPS or SQLServer Module for running Invoke-SQLCMD)
  • Example report from the automated email report. It captures the Metadata sizes of your SUSDB before and after the decline script is executed on your WSUS/SUP servers (see requirements below for enabling and using $MetaDataReportingOn.

ver. 5.4.2 (12/15/2021) Added the following (NEW!!!)

  • Added a function to decline and cleanup all Windows 10 Feature Updates for Enablement package, except for the ones specified you want to use for Win10 Upgrades. HUGE savings here, if you're using Enablement package thru Windows 10 Servicing node. By default when you enable this, it gives you over 3k updates, and you will only use maybe 2% of it? Probably not even. But this bloats your metadata, for sure! So you may want to trim that down, just adjust the criteria in the script.
    • -Current Criteria - ($_.UpdateClassificationTitle -eq "Upgrades") -and !(($_.Title -match "x64-based") -and ($_.Title -match "Enablement Package"))
  • Added -forcesync. For forcing SUP synchronization from the Top SUP, down to lower tier in CM Hierarchies, after the Decline script is done.
  • Fixed the CleanUpdateList folder/file function

ver. 5.4.1 (3/9/2021) Added the following (NEW!!!)

  • Updated the report to not show categories with 0 results. Though it will list the ones with 0 below the table.

ver. 5.4 (3/2/2021 Added the following (NEW!!!)

  • Added the OneOff Manual Decline function. For declining single patches or multiple depending on the -kb input
  • example usage: .\Run-DeclineUpdate-Cleanup.ps1 -trialrun -OneOffCleanup -kb "*KB2768005*"

ver. 5.3 (12/20/2020) Added the following (NEW!!!)

  • Added a function for declining Windows 10 versions 1507/1511/1607/1703/1803/1903/2004 (Legacy Windows 10 updates)
    • - Current Criteria
      ($_.Title -match "Windows 10 Version 1507" -and $_.Title -notmatch "Server") -or
      ($_.Title -match "Windows 10 Version 1511" -and $_.Title -notmatch "Server") -or
      ($_.Title -match "Windows 10 Version 1607" -and $_.Title -notmatch "Server") -or
      ($_.Title -match "Windows 10 Version 1703" -and $_.Title -notmatch "Server") -or
      ($_.Title -match "Windows 10 Version 1803" -and $_.Title -notmatch "Server") -or
      ($_.Title -match "Windows 10 Version 1903" -and $_.Title -notmatch "Server") -or
      ($_.Title -match "Windows 10 Version 2004" -and $_.Title -notmatch "Server")
  • Added a function for declining legacy Office and M365
    • - Current Criteria
      ($_.Title -match "Office 365" -and $_.Title -match "x86 based Edition") -or
      ($_.Title -match "Microsoft 365 Apps Update" -and $_.Title -match "x86 based Edition")

ver. 5.2 (10/14/2020 Added the following (NEW!!!)

  • Added functions for the following;
    • Invoke-WsusServerCleanup -CompressUpdates
    • Invoke-WsusServerCleanup -CleanupObsoleteComputers
    • Invoke-WsusServerCleanup -CleanupUnneededContentFiles

WARNING!!!: Running "Invoke-WsusServerCleanup -CleanupObsoleteComputers" could fill-up your SUSDB_Log file to it's maximum size. If this happens, simply shrink the log file, and you may want to adjust and increase the max log size of your SUSDB to avoid issues. Then run "Invoke-WsusServerCleanup -CleanupObsoleteComputers" again. So monitor your SUSDB_Log to make sure this doesn't happen. And a little bit of some guidance below to help alleviate this issue.

To simply monitor using SSMS, run the SQL statement below on your SUSDB.

USE SUSDB;
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files where name ='SUSDB_log';
GO

To shrink the SUSDB_Log file:

1. In SSMS, right-click on SUSDB database, select Task / Shrink / Files

2. Under "File Type" dropdown menu, select log. Then hit OK.

OR, use and run the statement below in SSMS against your SUSDB, and it would do the the same thing.

USE SUSDB;
GO
DBCC SHRINKFILE (N'SUSDB_log' , 0, TRUNCATEONLY)
GO

ver. 5.0: (8/20/2018) Added the following

  • Fixed the missing comma in one of the paramaters (Thanks Johan for pointing that out!)
  • Improved/Updates OS filtering, to only allow decline of targetted OS/Updates.
  • Added Decline updates for the following: Windows 7, Windows 8, Windows 8.1, Windows Server 2003, Windows Server 08, Windows Server 08 R2, Windows Server 12, and Windows Server 12 R2.

UPDATE: (5/10/2018) Added the following

  • ARM64 based
  • Internet Explorer 10
  • Added Maintenance option for the UpdateList folder to prevent buildup. ($CleanUpdatelist = $true by default. Will delete files older than 90 days, which can be specified in the param section, $CleanULNumber).
  • Applied a fix where the script would continue to try to decline updates even after failure when querying for updates on target WSUS/SUP server. The script will now stop when that occurs. Of course, you'll be alerted if/when this happens.
  • More performance improvements

UPDATE: (4/25/2018) Added the following

  • Windows 10 Next
  • Server Next
  • Email Report option (Set to $false by default)
  • Performance improvements

UPDATE: (4/13/2018) On top of being able to decline superseded, Itanium, and XP updates, you can now also decline the following updates:

  • Preview
  • Beta
  • Internet Explorer 7, 8, and/or 9
  • Embedded

DOWNLOAD HERE FROM GITHUB <-- Download Link

Here’s what the script does:

  1. Decline superseded updates. (# of days can be specified by using the –ExclusionPeriod)
  2. Decline Itanium updates. (can be omitted by using the –SkipItanium switch)
  3. Decline Windows XP updates. (can be omitted by using –SkipXP switch)
  4. Decline Preview updates. (can be omitted by using –SkipPrev switch)
  5. Decline Beta updates. (can be omitted by using –SkipBeta switch)
  6. Decline Windows 10 Next Updates. (can be omitted by using –SkipWin10Next switch)
  7. Decline Server Next Updates. (can be omitted by using –SkipServerNext switch)
  8. Decline ARM64 based Updates. (can be omitted by using –SkipServerNext switch)
  9. Decline Windows 7 Next Updates. (can be omitted by using –SkipWin7 switch, $true by default)
  10. Decline Windows 8 Next Updates. (can be omitted by using –SkipWin8 switch, $true by default)
  11. Decline Windows 8.1 Next Updates. (can be omitted by using –SkipWin81 switch, $true by default)
  12. Decline Windows Server 2003 Next Updates. (can be omitted by using –SkipWin2k3 switch, $true by default)
  13. Decline Windows Server 2008 Next Updates. (can be omitted by using –SkipWin2k8 switch, $true by default)
  14. Decline Windows Server 2008 R2. (can be omitted by using –SkipWin2k8R2, $true by default) switch)
  15. Decline Windows Server 2012. (can be omitted by using –SkipWin12 switch, $true by default)
  16. Decline Windows Server 2012 R2 (can be omitted by using –SkipWin12R2 switch, $true by default)
  17. Decline IE 7 updates. (can be omitted by using –SkipIE7 switch)
  18. Decline IE 8 updates. (can be omitted by using –SkipIE8 switch, $true by default)
  19. Decline IE 9 updates. (can be omitted by using –SkipIE9 switch, $true by default)
  20. Decline IE 10 updates. (can be omitted by using –SkipIE10 switch, $true by default)
  21. Decline Embedded updates. (can be omitted by using –SkipEmbedded switch)
  22. Can be run with –TrialRun which only records what you can decline (I highly recommend running this first. And examine the data in the “UpdatesList” folder it creates)
  23. It creates event logs for success/failure of the script or failure during decline process.
  24. Cleans UpdateList folder. It deletes files/folders that are older than x days. (can be turned off/on by -CleanUpdateList switch. It is set to $true by default, and set to 90 by default. Check $CleanULNumber in param)
  25. Sends Email Report (Optional, see below for screenshot)
  26. NEW!!! Decline LegacyWin10 (See active criteria being used in the script, within the description section)
  27. NEW!!! Decline Legacy M365 updates (See active criteria being used in the script, within the description section)
  28. NEW!!! Decline Windows Servicing/Feature updates / Enablement packages. (See active criteria being used in the script, within the description section)
  29. NEW!!! Invoke CompressUpdates
  30. NEW!!! Invoke CleanupObsoleteComputers ($true by default)
  31. NEW!!! Invoke CleanupUnneededContentFiles
  32. NEW!!! Cleanup Oneoff or Single patches (example: .\Run-DeclineUpdate-Cleanup.ps1 -trialrun -OneOffCleanup -kb "*KB2768005*") Strongly recommend staring with -trialrun, then review the logs and html created in UpdateList folder, to see exactly the patches you're declining. NOTEs: - The -OneOffCleanup depends on -kb being populated. NOTE: The -kb uses the "like" operator. So you may want to include wildcards (*) in your search criteria.
  33. NEW!!! Force a SUP synch after the decline of all updates are done, by adding -forcesync or by setting this to $true.
  34. NEW!!! Report on Metadata sizes, before and after, by adding -MetaDataReportingOn or by setting this to $true.

NOTE: I strongly recommend running this with -TrialRun switch first, and evaluate what it would decline by reviewing the htm and csv files it creates under "UpdateList" folder. See the comment section in the Script for more details.

Requirements:

  1. Must have WSUS Console where the script is being executed on. If CAS is in place, downlevel servers MUST run the script first, then the upstream or Top SUP server for declining updates. No worries, the script would stop, record the error, and then notify you if it encounter errors in the middle of the script.
  2. If you're using the recently added function to report the metadata sizes ($MetaDataReportingOn), the machine you're running the script on must have SQLPS or SQLServer module imported. Simply because this uses "Invoke-SQLCMD".
  3. If -forcesync is being used, the machine you're running this script on MUST have access to the provider to kick the WSUS sync off.

This script can be run against individual WSUS/SUP server, or a line of WSUS servers. For running against individual server, just use the –Servers <WSUSServer>. If you have a CAS, this script must be run on the lower tier SUPs first, then run on the top. This can also be automated!

For automating it with CAS and Child sites (Using task Scheduler)

1. Modify the script and adjust the $Servers parameter (lower tier SUPs to run first, then the top SUP server). NOTE: If SUSDB is shared, it only needs to run on one SUP.

$Servers = @("<lowerSUPServer1>","<lowerSUPServer2>","<CASLevelSUPServer>")

2. Pick a server with WSUS Console installed to run this on (we run this on our Top SUP, since WSUS console is already on it.)

3. Add this server to all WSUS/SUP servers’ local\admins group.

4. Give the server the appropriate access to the SUSDB

5. On this server, make a Task Schedule, and define the schedule accordingly to fit your need (Recommendation is to run it monthly to keep the size of your metadata lean)

6. Add a program using the following settings:

Program/Script: %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe

Add arguments: C:\APPSFOLDER\Run-DeclineUpdate-Cleanup.ps1

Start in: C:\APPSFOLDER

Voila! Automated. And all you need to is review the results periodically, if necessary.

Sample Email Report (NEW!!!)

Again, follow the basic WSUS maintenance from Meghan's post, look at your WSUSpool/web.config settings and consider the settings in Sherry's blog (working great for us), and decline superseded updates and updates on WSUS servers that are no longer being used.

That is what's SUP!!!

  • Created on .