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!

 

Silently Install LAPS Management Tools

I was recently asked how to install the LAPS Management Tools from the command line. As I normally just double click the MSI and click through the wizard, I wasn’t sure how to accomplish this relatively simple task.

The default installation only installs GPO Client Side Extensions and can be completed using one of the following command lines:

msiexec.exe /i LAPS.x64.msi /quiet
msiexec.exe /i LAPS.x86.msi /quiet

If you want to install the full set of features on your management workstation, you can use one of the following command lines:

msiexec.exe /i LAPS.x64.msi ADDLOCAL=CSE,Management,Management.UI,Management.PS,Management.ADMX /quiet
msiexec.exe /i LAPS.x86.msi ADDLOCAL=CSE,Management,Management.UI,Management.PS,Management.ADMX /quiet

Or more simply one of the following command lines:

msiexec.exe /i LAPS.x64.msi ADDLOCAL=ALL /quiet
msiexec.exe /i LAPS.x86.msi ADDLOCAL=ALL /quiet

Hope this helps!

Unspecified Error When Copying A Large File Over a Virtual Machine Connection

From time to time I find I need to copy a large file to a Virtual Machine running on Hyper-V. Normally copying files is easy when using Enhanced Session Mode — a simple copy and paste is all you need… except when dealing with really large files. This is what you get when you try that:

2017-04-17 11_21_11

I ran into this error today when trying to copy a 5.47 GB ISO file, so I thought I’d share a quick tip on copying files using PowerShell Direct, a new feature of Hyper-V available in Windows 10 and Windows Server 2016.

$PSSession = New-PSSession -VMName SERVER-VM-01 -Credential (Get-Credential)
Copy-Item -ToSession $PSSession -Path C:\Local\Path\Image.iso -Destination C:\Remote\Path

That’s all there is to it!

Source: TechNet Blog Post by Ben Armstrong

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