Clean up your WSUS database for better performance and SCCM software update compliance

Introduction:

With the recent Current Branch updates starting from 1806 , Microsoft is making good improvement on Software updates maintenance but there is lot to come in the near future. Read the Software updates maintenance tasks available in SCCM https://docs.microsoft.com/en-us/sccm/sum/deploy-use/software-updates-maintenance

Many SCCM Admins think that ,installing WSUS ,doing initial configuration and configuring SUP role is enough for software update patching but that's not true. When you finish initial WSUS configuration ,you go SUP properties and start selecting classification and products. Based on this selection criteria , updates get synced with Microsoft . These synced updates include itanium and many other junk updates. Once the updates are synced successfully ,you will see them in SCCM console under software update section . With this ,you can start patching your clients but over a period of time ,if you don't maintain your metadata/update catalog with the help of maintenance job (custom scripts/tools) ,you will hit into lot of issues. These issues could be like high CPU usage (IIS worker process) ,WSUS application pool in IIS stops automatically ,clients software update scan performance issues and many more.

Top reasons to have site performance issues ,client update scan ,WSUS application pool etc, is due to large number of updates in your WSUS database which includes superseded ,Itanium and other unneeded updates. If you decline all unused,itanium, superseded updates etc. at regular intervals then your site server will be happy with better performance and also your clients to perform quick update scan which will help to achieve better compliance rate.

There are N number of questions asked in various forums around WSUS and software update scan issues and there are several posts available with maintenance solution.

After going through lot of forums ,blog posts ,suggestions from Microsoft, I have come up with standard document that i have used all the times in every SCCM infra that i setup as part of SUP maintenance.

This solution consists of PowerShell scripts and also SQL reports to perform the cleanup/decline the junk updates that will help to improve the site server performance and also client update scanning.

How is it different from the built in SUP maintenance tasks ? when am running current branch 1806 and above ,do i still need this solution ?

Yes, the software update maintenance solution that is built in does very basic things like expiring the superseded updates ,cleaning the unused updates etc but what am going to describe in this blog post is more of advanced to decline the unneeded updates ,

configure WSUS in IIS as per best practices and further more.

If you have CAS, primary,secondary sites then you should perform these steps from bottom to top (secondary ,primary and CAS ).

Steps at glance:

A) Check the status of WSUS database with count of updates. These count of updates decides the catalog size

B) Decline itanium and other junk updates that you don't use in your infra.

C) Decline superseded updates.

D) Perform SQL indexing

E) Invoke WSUS configuration (best practice)

F) Troubleshooting.

A) Check the status of WSUS database with count of updates:

We will first use some SQL queries to fetch the current status of WSUS with count of updates before we decline them.

1. Use the appropriate method to back up the WSUS database (SUSDB). For related information, please see Create a Full Database Backup (SQL Server) .

2.Once the database is being backed up ,run the following SQL code against your WSUS database to see the count of updates (superseded ,declined ,total updates, live updates etc). It is always good validate the results before and after cleanup task.

3.I assume your WSUS DB is running on SQL but not on windows internal database .If your wsus database is running windows internal database (WID) ,then follow this guide and run the following SQL command.

4. Open SQL server management studio ,connect to your secondary site database (incase you have ,else primary then CAS) and run the following SQL code:

--get the count of total updates, superseded ,declined updates.

use SUSDB;
select
(Select count (*) 'Total Updates' from vwMinimalUpdate ) 'Total Updates',
(Select count (*) 'Live updates'  from vwMinimalUpdate where declined=0) as 'Live Updates',
(Select count (*) 'Superseded'  from vwMinimalUpdate where IsSuperseded =1) as 'Superseded',
(Select count (*) 'Superseded But NoDeclined'  from vwMinimalUpdate where IsSuperseded =1 and declined=0) as 'Superseded but not declined',
(Select count (*) 'Declined'  from vwMinimalUpdate where declined=1) as 'Declined',
(Select count (*) 'Superseded & Declined' from vwMinimalUpdate where IsSuperseded =1 and declined=1) 'Superseded & Declined'

Total Updates: count of all updates which includes superseded ,decline .This basically include all updates in your wsus db.

Live updates: Count of updates without declined .This includes all updates with superseded/without superseded but not declined. These updates are considered to generate the update catalog file.

Superseded: Count of all superseded updates

Superseded but not declined: Count of all superseded updates but they are not declined yet.

Declined:Count of updates that are declined. Declined updates never goes into update catalog file .

Superseded & declined: Count of updates that are superseded and declined.

As you see above,the total live updates that are considered to generate update catalog is 18000+ .This usually be larger update catalog file and with huge amount of updates, it also impact the CPU,memory on your WSUS because clients always talk to WSUS to download update catalog.

B) Decline itanium and other junk updates that you don't need in your infra.

Now ,download and extract the PowerShell scripts and SQL files that are available here.

Following are the files you get from the download link

Following are 2 powershell scripts (customized) that am going to use to decline the unused /superseded/itanium updates.

b.1)Decline-OtherUpdates.ps1

b.2)Decline-supersededUpdates.ps1 /Decline-SupersededUpdatesWithExclusionPeriod.ps1

Decline-OtherUpdates script have the following titles to decline because i dont use them in my infra.

Itanium
ia64
ARM64-based Systems
Windows 10 (consumer editions)
Windows 10 Education
Windows 10 Team
Windows 10 Insider Preview

Please review the tiles and make changes as you need .

Run the PowerShell script with command line:  .\Decline-OtherUpdates.ps1 -UpdateServer YourWSUSServerName -Port 8530 –DeclineItanium

As you see ,i have 2402 updates declined. This includes all the titles listed above.

C) Decline superseded updates.

Now we will run decline superseded updates script.

There are 2 scripts here for you Decline-supersededUpdates.ps1 and other Decline-SupersededUpdatesWithExclusionPeriod.ps1 . The only difference with these 2 scripts are ,added with exclusion period as per your SUP settings.

Login to your secondary site (if you have any) ,launch powershell in admin and change directory to the script that you placed.

To decline superseded updates ,we can make use of ExclusionPeriod as criteria that will help to decline updates that are in sync with our software update component properties .

In your configuration manager SUP properties ,if you have set supersedence behavior to expire immediately then you don't need to use above ExclusionPeriod period in the PowerShell however ,if you have configured supersedence behavior with X months then i would recommend to use same period in the script.

The following command lines show different ways in which PS scripts can be run (if the script is run on a WSUS server, you can use LOCALHOST instead of the actual SERVERNAME).

Based on your SUP settings ,if you want to decline all superseded updates ,then run the following command:

Decline-supersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530

If you want to decline the superseded updates with some exclusion period ,use the following command:

Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530 -ExclusionPeriod 60

ExclusionPeriod 60 to gather information about updates on the WSUS server from current date that you run the script and check the number of updates that can be rejected.

P.S: Don't look at SQL query count (18182) vs PowerShell count (18175) as they are not same servers when i execute the code  

Once the updates are declined ,go back to your SQL and run the query against your WSUS DB to see the status.

This time ,you should see different count compared to last time run .

After declining the updates in WSUS , these declined updates still appear in SCCM  until you run software update sync.

Once the software update sync happens on SCCM server ,the changes you made on WSUS will appear in SCCM console.

After SUP sync ,you will see all declined updates from WSUS will disappear from SCCM console.

D) Perform SQL indexing

After you decline the updates , SUSDB needs to be re-indexed for optimal performance. See the section on Re-Indexing the WSUS Database above for related information .

Please wait until the execution of script completed.

E) WSUS configuration (Best practice)

You will find script Invoke-WSUSConfiguration.ps1 which i got from Johan which i always use in all SCCM infra as per best practice .

Take a look at the script to see what it does before you execute in your infra.

F) Troubleshooting.

In some cases ,if your WSUS database never cleaned up before and is the first time you are doing it on some secondary /primary sites ,execution of scripts may fail with following error and is because of too many updates .

In my case ,I had 23k plus total number of updates in WSUS database, hence script was always failing to fetch the data.

Connecting to WSUS server localhost on Port 8530... Connected.
Getting a list of all updates... Failed to get updates.
Error: The operation has timed out
If this operation timed out, please decline the superseded updates from the WSUS Console manually.

I tried few times running the script but I could not get through even though i restarted the IIS service and WSUS service.

If you are unable to decline the updates using script ,what other possibilities do we have ? open the WSUS console and do manual update,that takes lot of time.

Microsoft support engineer posted SQL code to decline the updates in SUSDB. 

1. If you have not backed up your SUSDB database, back up your SUSDB database before continuing .
2. Connect to SUSDB using SQL Management Studio.
3. Execute the following query: The number 60 for the number of rows containing " DECLARE @thresholdDays INT = 60" corresponds to the number of rows before # 1 and the number of days that match the number of months configured in the Supersedence rule. If the expiration date is set to expire immediately, you must set the SQL query value @thresholdDays to zero.
4. The SQL code that was posted in support article needs some syntax corrections to get it work OR it could be the reason that ,the support article in in Japanese language so while translating ,syntax got changed.

--Decline superseded updates in SUSDB; alternative to Decline-SupersededUpdatesWithExclusionPeriod.ps1
DECLARE @thresholdDays INT = 60--Specify the number of days between today and the release date for which the superseded updates must not be declined (ie, updates older than 90 days). This should match configuration of supersedence rules in SUP component properties, if ConfigMgr is being used with WSUS.
DECLARE @testRun BIT = 0--Set this to 1 without test excluding anything.
--There shouldn't be any need to modify anything after this line.




DECLARE @uid UNIQUEIDENTIFIER
DECLARE @title NVARCHAR (500)
DECLARE @date DATETIME
DECLARE @userName NVARCHAR (100) = SYSTEM_USER




DECLARE @count INT = 0




DECLARE DU CURSOR FOR
     SELECT MU.UpdateID, U.DefaultTitle, U.CreationDate FROM vwMinimalUpdate MU
     JOIN PUBLIC_VIEWS.vUpdate U ON MU.UpdateID = U.UpdateId
WHERE MU.IsSuperseded = 1 AND MU.Declined = 0 AND MU.IsLatestRevision = 1
     AND MU.CreationDate <DATEADD (dd,-@thresholdDays, GETDATE ())
ORDER BY MU.CreationDate




PRINT 'Declining superseded updates older than' + CONVERT (NVARCHAR (5), @thresholdDays) + 'days.' + CHAR (10)




OPEN DU
FETCH NEXT FROM DU INTO @uid, @title, @date
WHILE (@@FETCH_STATUS>-1)
BEGIN
     SET @count = @count + 1
     PRINT 'Declining update' + CONVERT (NVARCHAR (50), @uid) + '(Creation Date' + CONVERT (NVARCHAR (50), @date) + ')-' + @title + '...'
     IF @testRun = 0
         EXEC spDeclineUpdate @updateID = @uid, @adminName = @userName, @failIfReplica = 1
     FETCH NEXT FROM DU INTO @uid, @title, @date
END
CLOSE DU
DEALLOCATE DU




PRINT CHAR (10) + 'Attempted to decline' + CONVERT (NVARCHAR (10), @count) + 'updates.'

To check progress, monitor the Messages tab in the Results pane.

Depending on the number of updates ,it may take longer time. In my case ,it took ~15 min to decline around 10K updates.

Once the superseded updates are declined using SQL ,we can now go back to PowerShell script and run other script (decline other updates.).

Hope you find this post useful.

Following are some of the References that would help to go through the WSUS maintenance solution.

https://support.microsoft.com/en-sg/help/4490644/complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maint

https://deploymentresearch.com/Research/Post/665/Fixing-WSUS-When-the-Best-Defense-is-a-Good-Offense

https://mnscug.org/blogs/sherry-kissinger/512-wsus-administration-wsuspool-web-config-settings-enforcement-via-configuration-items

https://home.configmgrftw.com/wsus-cleanup-for-configmgr/

https://damgoodadmin.com/2017/11/30/software-update-maintenance-its-a-thing-that-you-should-do/