WSUS Maintenance Automation

Update: 06.26.2017 - Created one for Server 2012 R2 here.

While I use SUP via SCCM 2012 to manage my work fleet, I still play with and help others with their WSUS installs. WSUS is great for small and even large organizations to update their fleet systems. I directly support a WSUS instance (as well as MDT) at my dads, my playground really. He has everything from 98 SE to 8.1 running around on site and is the "computer guy" for his neighborhood and family/friends so he fixes many different Windows OS'. WSUS saves him a great deal of time and effort when he fixes a system or refreshes it with MDT.

With that said, no matter the environment, WSUS needs regular care and feeding and I handle this need automatically with scripts others have wrote and shared. Over the years I've found many ways to do it and have settled on the following process monthly which keeps WSUS happy with little break/fix issue and even keeps the WSUS MMC snappy.

• Decline Itanium Updates
• Cleanup Wizard
• Re-Index Database

The first two are handled by Powershell scripts written by Mark and the third is by the Scripting Guys.  All three can be found over on the Microsoft Gallery Script Center. Very handy site.

Since WSUS does not distinguish between Wintel or Itanium you can get unneeded Itanium patches taking up space and being processed, especially true with any auto-approve rules. Both of these scripts are ran against the parent server only. I run the Itanium at 7PM and the Cleanup at 10PM on the first of each month. You can find the Decline Itanium Updates here and the Cleanup Wizard Script here. Be sure to modify them for your environment (as shown in the links) and the email function is pretty handy as shown below so enable it if you want it.

For the scheduled task it took a little bit to get working the way I like due to PS security. I run them as a Domain user that is in the local admin group. So whatever works in your environment.

 powershell.exe -ExecutionPolicy Unrestricted -nologo -NoProfile -File "PATHTOSCRIPT\WSUS_Decline_Itanium_Updates.ps1"  

 powershell.exe -ExecutionPolicy Unrestricted -nologo -NoProfile -File "PATHTOSCRIPT\WSUS_Cleanup_All_Servers.ps1"  

You can find the WsusDBMaintenance script here, however doing a Re-Index is a bit more complex and needs to be ran on each WSUS server from the parent on down. If you use Windows Internal Database (default for WSUS) then this applies. If you use another flavor of MS SQL, then I vote to let the DBA deal with it... :)

Firstly, you have to install some prereqs as the sqlcmd called in the re-index script needs to be present. Microsoft provides it separately so you do not have to install a full edition of MS SQL to get it. Install MS SQL Server Native Client and then install the sqlcmd tool to your server. Below are links for 32-Bit and 64-Bit and there are Itanium ones out there as well. Install with defaults.

32-Bit MS SQL Server Native Client
32-Bit MS 2005 SQLCMD

64-Bit MS SQL Server Native Client
64-Bit MS 2005 SQLCMD

I wanted it to also email results to me so I grabbed blat. Great tool. Put blat and the WsusDBMaintenance script in the same folder along with the batch file below. Blat will attach the sqlcmd output as a file. Modify the setup shop variables to your environment. The scheduled task calls the batch file and runs as local administrator. Be sure to check the 'run with highest privileges' on the general tab of the scheduled task otherwise you will experience an access denied error. This is not needed on the above two powershells. I run it at 7 PM on the 2nd day of each month.

 @ECHO OFF
 :: This script will run the WSUSDBMaintenance and email its output
 :: Written by Kevin Fason
 :: 09.12.2012
 :: v1.0
 
 
 :: Setup Shop
 SET LOGFILE=%TEMP%\WSUS_DB_reindex.txt
 SET EXEPATH=C:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd.exe
 SET SCRIPTPATH=PATHTOYOURSCRIPT
 SET WSUSSERVER=WSUSSERVERNAME
 SET [email protected]
 SET [email protected]
 SET SMTPSMARTHOST=SMTPHOST.DOMAIN.COM
 
 :: Delete previous log
 ECHO.
 ECHO Deleting previous log if it exists
 IF EXIST %LOGFILE% DEL %LOGFILE%
 
 :: Run WSUSDBMaintenence SQL Script
 ECHO.
 ECHO Starting WSUS DB Maintenence Script
 "%EXEPATH%" -I -i "%SCRIPTPATH%\wsusdbmaintenance.sql" -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query > %LOGFILE%
 
 
 :: Email the SQL Script output
 ECHO.
 ECHO Emailing results
 "%SCRIPTPATH%\blat.exe" -body "WSUS Database Maintenance Results attached. Please review. see you next time!" -to %SMTPTO% -f %SMTPFROM% -s "WSUS: DB Maintenence Results for %WSUSServer%" -server %SMTPSMARTHOST% -attach %LOGFILE%

I suppose you could have a script call all three parts sequentially but I do it separately to work around backup windows, and really as long as you do it regularly its very beneficial. I've also thought of doing it closer to Patch Tuesday or maybe first Monday and Tuesday of the month for example. So do you do something more?

Updated (02.25.2016)

Marks scripts do not work on more recent releases of WSUS within 2012 and 2012 R2. I found a couple great ones to use however.

First is SMSAgent wrote a PS1 that performs the same Cleanup Wizard as above and emails it to you.

Second is Jason's configmgrftw has Decline-OtherUpdates and expands ALOT on the process as a whole. His focuses on declining Itanium and other miscellaneous updates.

Additionally Microsoft has a TechNet article on it.

-Kevin Fason