WSUS Maintenance Automation

I have a three-for-one deal for you today! Setting up WSUS can be pretty easy if your needs are modest. But there are a few little things you might want to consider putting in place. Here are some simple tips that can help automate some of those tasks.

Decline Itanium Updates

First, does anyone really have Itanium servers they need to patch? Nope, didn’t think so. Run this PowerShell some time during the evening of the second Tuesday of each month and it will decline all those pesky updates for you.

Get-WsusUpdate -Approval AnyExceptDeclined | ? { $_.Update.Title -imatch "ia64|itanium" } | Deny-WsusUpdate

You can wrap it in a batch file pretty easily too, just watch the quotes.

@ECHO OFF
PowerShell.exe -Command "Get-WsusUpdate -Approval AnyExceptDeclined | ? { $_.Update.Title -imatch 'ia64|itanium' } | Deny-WsusUpdate"

Periodically Run the Server Cleanup Wizard

You mean you don’t go in every once in a while and run the Server Cleanup Wizard? You probably ought to, and with the following PowerShell, you won’t have to do it manually anymore! (Adjust the parameters to your liking, but I recommend including them all. Details can be found here.)

Get-WsusServer | Invoke-WsusServerCleanup -CleanupObsoleteComputers -CleanupObsoleteUpdates -CleanupUnneededContentFiles -CompressUpdates -DeclineExpiredUpdates -DeclineSupersededUpdates

You can put that in a batch file too.

@ECHO OFF
PowerShell.exe -Command "Get-WsusServer | Invoke-WsusServerCleanup -CleanupObsoleteComputers -CleanupObsoleteUpdates -CleanupUnneededContentFiles -CompressUpdates -DeclineExpiredUpdates -DeclineSupersededUpdates"

You’ll want to schedule this before the database maintenance I describe below. Once a week should be sufficient, but adjust for your environment.

Perform Database Maintenance

For this, you’ll need the sqlcmd utility, the SQL script, and have an understanding of the type of database backing WSUS.

If you’re using the Windows Internal Database on Windows Server 2012 or newer, go with this command:

sqlcmd -I -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i D:\Scripts\WsusDatabaseMaintenance.sql

If you’re using the Windows Internal Database on Windows Server 2008 R2 or older, go with this one instead:

sqlcmd -I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i D:\Scripts\WsusDatabaseMaintenance.sql

If you’re using SQL Server, talk to your SQL Server administrator for advice on how best to proceed. Hopefully you can take advantage of Windows Authentication. I wouldn’t recommend storing credentials as clear text in a batch file.

You might be asking why I didn’t use PowerShell. There is an Invoke-Sqlcmd command after all. It does not, however, have an option to enable quoted identifiers like the sqlcmd executable. Don’t fret. It’s an easy enough fix. Simply modify the first part of the SQL script by adding an additional SET statement like this:

USE SUSDB;
GO
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

Now you can use the following PowerShell to accomplish the same thing as the sqlcmd commands above.

Invoke-Sqlcmd -ServerInstance "np:\\.\pipe\MICROSOFT##WID\tsql\query" -InputFile D:\Scripts\WsusDatabaseMaintenance.sql

And you can of course wrap that in a batch file as well. Mind the quotes on this one too.

@ECHO OFF
PowerShell.exe -Command "Invoke-Sqlcmd -ServerInstance 'np:\\.\pipe\MICROSOFT##WID\tsql\query' -InputFile D:\Scripts\WsusDatabaseMaintenance.sql"

Conclusion

There are other things we can do to help maintain and operate WSUS (like taking backups and performance tuning). Perhaps I’ll cover some of those topics in a future post. For now, I hope you found these tips useful!

 

New Home Lab

The Quest Begins

I trolled the homelab subreddit for a while, discussed options with friends and colleagues, and did a ton of research. I wanted something quiet, reasonably powerful, and expandable. While cost was a factor, it didn’t drive my decisions.

Many folks advocated purchasing older servers from eBay, where the Dell PowerEdge R710 is among the most popular options. It’s true there are really good deals out there for fairly powerful servers at very reasonable prices, but I chose a different route.

Unfortunately, commercial servers are generally pretty loud. I have to keep the server in my home office so I need something that won’t be distractingly loud. I also intend to migrate eight 3.5-inch drives and those older servers won’t accommodate that need.

Continue reading