The Deployment Bunny

OS Deployment, Virtualization, Microsoft based Infrastructure…

  • about.me

    about.me/mikaelnystrom

  • Archives

  • Meta

Working in the Datacenter – Keeping WSUS Happy using PowerShell

Posted by Mikael Nystrom on February 3, 2016

We use WSUS in our own datacenter as well as customer sites, for many client based scenarios this is done using WSUS and ConfigMgr, but in the fabric it is either WSUS or WSUS and SCVMM. When WSUS is used for content and distribution (Nativly or with SCVMM) it needs a helping hand…

  • Someone need to deny all patches that are superseeded, this does not happen automatically.
  • Someone needs to cleanup old content, computers, patches and such, this does not happen automatically.
  • Someone needs to care for the database, this does not happen automatically.

So, over the years poeople around the globe has been providing scripts for this, and here is what we currently are using.

https://github.com/DeploymentBunny/Files/tree/master/Tools/Invoke-WSUSMaint

The script will do the following

image

Connect to a database

you might need to change this in the script.

#For Windows Internal Database, use $WSUSDB = ‘\\.\pipe\MICROSOFT##WID\tsql\query’
#For SQL Express, use $WSUSDB = ‘\\.\pipe\MSSQL$SQLEXPRESS\sql\query’

Get the Superseeded Updates

Here is the Posh that fixes that:

$SuperSeededUpdates = Get-WsusUpdate -Approval AnyExceptDeclined -Classification All -Status Any | Where-Object -Property UpdatesSupersedingThisUpdate -NE -Value ‘None’ -Verbose
$SuperSeededUpdates | Deny-WsusUpdate –Verbose

Cleanup WSUS

We run each step sepratly, however, you can change that and run everything in one line…

Cleanup the DB

Last part runs sqlcmd using a .SQL file from MSFT Gallery, and yes, you can download and install the PowerShell tools for SQL and use that instead. Most of your customers dont have thoose tools installed, so sqlcmd.exe it is.

16 Responses to “Working in the Datacenter – Keeping WSUS Happy using PowerShell”

  1. Baard said

    Get-WsusUpdate is only valid on Server 2012, right?
    We’re still (and probably many with us) on 2008 R2.

    • Correct, upgrade is an option, takes less then an hour to build a new server, resync. We use PowerShell for that, in Deployment Fundamentals vol. 5 All scripts needed to build a complete infrastructure is included, so if you want it, just do it…

      • Baard said

        Thanks, but need time to upgrade to 2012. But at least it’ll happen on this side of summer :)

  2. Tuur said

    A M A Z I N G ! I have spend the last month googling what caused the problem of my clients needing 25+ cycles of [wuauclt /detectnow] (getting 0x80244010 Exceeded max server round trips) to get in sync with my freshly build 2012 R2 WSUS server.
    I had 1100+ Win 7 systems, all moaning after a reset of their client. WSUS server only had 5000 updates for Win7 and office. Before I ran this script I had 4822 of 5005 in the All Updates section. After running the script 1807 of 5005. Clients now only need one cycle to get their updates.
    Please let me know where I can send a big thank you cake, as this really has caused me a lot of headache and time!

  3. cmiscloni said

    I used this script but it declined all Updates and not only supersedded.
    Any idea ?

    • The powershell script looks for anything that has been superseded and declines them. But it also looks for unused, not needed and a bunch of other things as well. So, the only reason it would remove updates is that if they are not needed.

  4. NoFloorPlans said

    Hi Mikael,

    This was an interesting script.
    I have 2 questions on this because I cannot get this script to work for me:

    1. Line 28 gives me this error?
    $RunningFromFolder = $MyInvocation.MyCommand.Path | Split-Path -Parent
    Split-Path : Cannot bind argument to parameter ‘Path’ because it is null.
    At line:2 char:53
    + $RunningFromFolder = $MyInvocation.MyCommand.Path | Split-Path -Parent
    + ~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [Split-Path], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.SplitPathCommand

    2. Iam on an 2012R2 server with the native WSUS database engine, where can I find the correct sqlcmd.exe to use for this, I have not this Exe located on this server?

    Ideas?

    • Yes, so you need to run this from the PowerShell prompt, not in ISE, since MyInvocation does not work there by default. As far as I remember SQLKCMD is installed when you add the database if not, just install any SQL basic management tool, like Microsoft® SQL Server® 2012 SP1 Feature Pack

      • NoFloorPlans said

        Hi Mikael,

        Thanks for your fast reply.
        You were spot-on about me using ISE to parse errors.
        I did not know that PowerShell and PowerShell ISE had different ways of working, thought it was the same…

        After the first run of the script the script cleaned about 60GB of data, from 80GB to 20GB.

        Now I run the same scripts again and again, it takes about 40 minutes every run to complete, is this normal that it takes this long for every run?
        I can see that “SQL Server Windows NT – 64 Bit” and “Windows Powershell” works at under 10% CPU during this time.

        I know this is relevant to the hardware and load of cource, but i am using a HP G7 Server and not much load at all so this feels pretty strange or?

        Also sqlcmd.exe version 2005.90.4035.0 gives me an error (APPCRASH) at the end (after 40 minutes) saying “T-SQL execution command line utility has stopped working”

        Any ideas forward?

      • NoFloorPlans said

        Here are som extra info:
        Also tried with sqlcmd.exe version 2009.100.4042.0 but still received the same APPCRASH.

      • NoFloorPlans said

        The same happens also with version 2009.100.2500.0.

        Any ideas further from anyone on this APPCRASH?
        Any comment on the time 40 minutes to run this script before it comes to the APPCRACH part in the end (seems very slwo to me)?

  5. […] https://deploymentbunny.com/2016/02/03/working-in-the-datacenter-keeping-wsus-happy-using-powershell/ […]

  6. Rob S. said

    I see a lot of processes for WSUS that either ignore superseded or, such as in this case, get rid of superseded updates. However, this can be extremely dangerous, especially in scenarios where updates have been superseded by service packs that cannot be deployed into production right away…or really in any case where the replacement isn’t production ready.

    Assume a security update is superseded on patch Tuesday, you run this process that Wednesday, but you take two weeks to deploy patches to your production systems. What happens when you build a system in that window?

    • In that case nothing, the new patch will be installed since I run Windows update as a part of OSD, so it will have the new patch instead of the old one. But it does depends, in this case we automatically approve the new patch at the same time as we remove the superseded, you could also extend the script to check each superseded patch and before decline it, you check if the new patch has been approved or not.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 7,427 other followers

%d bloggers like this: