Tag Archives: wsus

Powershell Scripting: Get-ECSWSUSComputerUpdatesStatusReport

Introduction:

I hate the WSUS reports built into the console.  They’re slow, and when it comes to doing something useful with the data, it’s basically impossible.  That’s why I wrote this function.

I wanted an ability to gather data on a given WSUS computer(s), and work with it in Powershell.  This function gives me the ability to write scripts for bulk reports, automate my patching process (checking that all updates are done), and in general, gives me the same data the standard WSUS report does, but at a MUCH faster rate.

You can find the function here.

Dependencies:

You’ll need my Invoke-ECSSQLQuery function located here.  This is going to mean a few things before you get going.

  • You need to make sure the account you’re running these functions under has access to the WSUS database.
  • You need to make sure the database server is setup so that you can make remote connections to it.
  • If you’re in need of SQL auth instead of windows auth, you’ll need to adjust the Get-ECSWSUSComputer and Get-ECSWSUSComputersInTargetGroup so that the embedded calls to my invoke-ecssqlquery use SQL auth instead of windows.

Secondly, this function doesn’t work without the “object” result of Get-ECSWSUSComputer or Get-ECSWSUSComputersInTargetGroup.  That means you need to run one of these functions first to get a list of computer(s) that you want to run a report against.  Store the results in an array.  Like $AllWSUSComputers = …..

Syntax examples:

if you’re reading this in Feedly or some other RSS reader, it’s not going to look right, you’ll need to hit my site if it looks like a bunch of garble.


$AllWSUSComputers =  Get-ECSWSUSComputer -WSUSDataBaseServerName "Database Server Name" -WSUSDataBaseName "SUSDB or whatever you called it" -WSUSComputerName "ComputerName or Computer Name pattern" -SQLQueryTimeoutSeconds "Optional, enter time in seconds"

Foreach ($WSUSComputer in $AllWSUSComputers)
     {
     Get-ECSWSUSComputerUpdatesStatusReport -WSUSDataBaseServerName "Database Server Name" -WSUSDataBaseName "SUSDB or whatever you called it" -WSUSComputerObject $WSUSComputer -SQLQueryTimeoutSeconds "Optional, enter time in seconds"
     }

Let me restate, you’re pointing at a SQL server.  Sometimes that’s the same server as the WSUS server, or sometimes that’s an external DB.  If you’re using an instanced SQL server, then for the database server name, you’d put “DatabaseServername\InstanceName”

if you actually want to capture the results of the report command,  my suggestion is to create an arraylist and add the results of the command into that array, or dump it to a JSON / XML file.  If you’re only running it against one computer, there’s probably no need for a foreach loop.

Output:

The output is the same not matter which function you run, with the one small exception being that I capture the computer target group name in the computer target group function.The

Name : pc-2158.asinetwork.local
AllPossiableUpdatesInstalled : True
AllApprovedUpdatesInstalled : True
AllPossiableUpdatesNotInstalledCount : 0
AllApprovedUpdatesNotInstalledCount : 0
LastSyncResult : Succeeded
LastSyncTime : 09/30/2017 16:11:33
LastReportedStatusTime : 09/30/2017 16:20:16
LastReportedInventoryTime :

Again, this output is really designed to feed my next function,but you might find it useful to do things like confirm that all WSUS computers are registered that should be, or to simply check the last time they synced.


$WSUSComputer | Select-Object -ExpandProperty UpdateStatusDetailed | Where-Object {$_.Action -eq "Install" -and $_.FriendlyState -ne "Installed"} | Select-Object DefaultTitle

That little snippet will show you all approved updates, that are not installed.  The friendlystate is whether the update is installed or not.  The action is whether the update is approved for install.

If we slightly modify the above command, we can show all updates that are not installed, but applicable by doing the following.


$WSUSComputer | Select-Object -ExpandProperty UpdateStatusDetailed | Where-Object {$_.FriendlyState -ne "Installed"} | Select-Object DefaultTitle

***NOTE1: This report is only as good as the updates that you allow via WSUS. Meaning, if you don’t download SQL updates, SQL updates are not going to show up in this report.

***NOTE2: This report only show non-declined updates. If you declined an update, it won’t show up here.

Closing:

I hope you find this useful. I alway found the default WSUS reporting to be underwhelming and slow. It’s not that it doesn’t work, but it’s really only good for singular computers. These functions can easily be used to get the status of a large swath of systems. Best of all, with it being a Powershell object, you can now also export it in any number of formats, my preference being JSON if I want a full report, or CSV if I just want the summary.

You can also find out how I did all my SQL calls by reviewing the embedded SQL Query in my function if you prefer the raw SQL code.

Problem Solving: WSUS failing for Windows 10 with error 8024401c

Hi Folks,

After updating WSUS to support Windows 10 newer update format, we noticed that our Windows 10 client weren’t working. The error they were getting was 8024401c whenever we checked for updates (post WSUS upgrade).  Initially we thought it was related to the WSUS upgrade, but found out that most of our systems hadn’t been updating for a while.  So we moved on to troubleshooting the client further.  We found that the following GPO “Do not connect to any Windows Update Internet locations”  was not configured.  After doing some digging we determined that this was put in place to prevent our clients from downloading updates from MS directly, which was originally happening.  The weird thing to me was why were our clients going to MS anyway?  We have WSUS, that is the point of WSUS?  Disabling the setting resulted in us getting updates, but now they were coming from MS directly and not WSUS.  Enabling or setting it to “not configured” resulted in the lovely error.

Example snippet of log file below.

2017/06/13 10:08:31.2836183 676 10488 WebServices WS error: There was an error communicating with the endpoint at ‘http://%ServerName%/ClientWebService/client.asmx’.
2017/06/13 10:08:31.2836186 676 10488 WebServices WS error: There was an error receiving the HTTP reply.
2017/06/13 10:08:31.2836189 676 10488 WebServices WS error: The operation did not complete within the time allotted.
2017/06/13 10:08:31.2836280 676 10488 WebServices WS error: The operation timed out
2017/06/13 10:08:31.2836379 676 10488 WebServices Web service call failed with hr = 8024401c.

After a ton of Google Fu, I stumbled on to this article https://blogs.technet.microsoft.com/windowsserver/2017/01/09/why-wsus-and-sccm-managed-clients-are-reaching-out-to-microsoft-online/  Before you start reading, make sure you’re relaxed and read through it carefully, because the answer is there, but you have make sure you’re not just skimming.

Here is the main section and highlighted points that you need to glean from that article.


Ensure that the registry HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate doesn’t reflect any of these values.

  • DeferFeatureUpdate
  • DeferFeatureUpdatePeriodInDays
  • DeferQualityUpdate
  • DeferQualityUpdatePeriodInDays
  • PauseFeatureUpdate
  • PauseQualityUpdate
  • DeferUpgrade
  • ExcludeWUDriversInQualityUpdate

What just happened here? Aren’t these update or upgrade deferral policies?

Not in a managed environment. These policies are meant for Windows Update for Business (WUfB). Learn more about Windows Update for Business.

Windows Update for Business aka WUfB enables information technology administrators to keep the Windows 10 devices in their organization always up to date with the latest security defenses and Windows features by directly connecting these systems to Windows Update service.

We also recommend that you do not use these new settings with WSUS/SCCM.

If you are already using an on-prem solution to manage Windows updates/upgrades, using the new WUfB settings will enable your clients to also reach out to Microsoft Update online to fetch update bypassing your WSUS/SCCM end-point.

To manage updates, you have two solutions:

  • Use WSUS (or SCCM) and manage how and when you want to deploy updates and upgrades to Windows 10 computers in your environment (in your intranet).
  • Use the new WUfB settings to manage how and when you want to deploy updates and upgrades to Windows 10 computers in your environment directly connecting to Windows Update.

So, the moment any one of these policies are configured, even if these are set to be “disabled”, a new behavior known as Dual Scan is invoked in the Windows Update agent.

When Dual Scan is engaged, the following change in client behavior occur:

  • Whenever Automatic Updates scans for updates against the WSUS or SCCM server, it also scans against Windows Update, or against Microsoft Update if the machine is configured to use Microsoft Update instead of Windows Update. It processes any updates it finds, subject to the deferral/pausing policies mentioned above.

Some Windows Update GPOs that can be configured to better manage the Windows Update agent. I recommend you test them in your environment


After reading that, I went back in our GPO and did some more digging, since all our WSUS client settings are defined in GPO, turns out we have the “Do not include drivers with…” setting enabled.  So ultimately it was this setting that led to the whole “Dual Scan” mode being enabled, which led to us downloading MS updates (needed to happen anyway), which led to us disabling that, which led to WSUS not being used at all. So after setting both settings to not configured and doing a lot of GPUpdates / restarting of the windows update services, eventually I went from getting that error, to everything being back to normal.  That is, my client connecting to WSUS and downloading updates the right way.

Lessons learned besides not just randomly enabling WSUS settings, is that Microsoft in my not so humble opinion, needs to do a better job with the entire WSUS client control.  This is just stupid behaviour to be blunt.    What I would suggest that MS do is as follows.

  1. For Pete’s sake, have a damn setting that controls whether we want updates via WSUS, WUFB, or neither.  I mean it seems like such an obvious thing.  Clearly implied settings conflict.  If you have to write a damn article explaining all the gotcha’s you failed at building an user friendly solution.
  2. Group settings that are Windows update for business specific in their own damn GPO folder and their own damn reg key.  This way there’s no question these are for WUfB only.  Similar to WSUS.
  3. If WSUS is enabled, ignore WUfB settings and vice versa.

Anyway, hope that helps any other poor souls out there.

SQL Query: Microsoft – WSUS – Computers Update Status

Sometimes the WSUS console, just doesn’t give you the info you need, or it doesn’t provide it in a format you want.  This query is for one of those examples.  This query can be used in multiple ways to show the update status of a computer, computers or computer in a computer target.  For me, I wanted to see the update status, without worrying about what non-applicable updates were installed.  I also, didn’t care about updates that I didn’t approve, which was another reason I wrote this query.

First off, the query is located here on my GitHub page.  As time allows, I plan to update the read me on that section with more filters as I confirm how accurate they are and what value they may have.

All of the magic in this query is in the “where statement”. That will determine which updates you’re concerned about, which computers, which computer target groups, etc.

To begin with, even with lots of specifics in the “where statement”, this is a heavy query. I would suggest starting with a report about your PC or a specific PC, before using this to run a full report. It can easily take in excess of 30 minutes to an hour to run this report if you do NOT use any filters, and you have a reasonably large WSUS environment.   It’s also worth noting, in my own messing around, I’ve easily run out of memory / temp db space (over 25GB of tempdb).  It has the potential to beat the crap out of your SQL DB server, so proceed with caution.  My WSUS DB is on a fairly fast shared SQL server, so your mileage may vary.

Let’s go over a few way’s to filter data. First, the computer name column would be best served by using a wildcard (“%” not “*”) at the end of the computer name. Unless you’re going to use the FQDN of the computer name.  In other words, use ‘computername%” or ‘computername.domain.com’

Right now, I’m only showing updates that are approved to be installed. That is accomplished by the Where Action = ‘Install’ statement.

The “state” column is one that can quickly let you get down to the the update status you care about. In the case of the one below, we’re showing the update status for a computer called “computername”, but not showing non-applicable updates.


Where Action = ‘Install’ and [SUSDB].[PUBLIC_VIEWS].[vComputerTarget].[Name] like ‘computername%’ and state != 1


if we only wanted to see which updates were not installed, all we’d need to do is the following. By adding “state !=4” we’re saying only show updates that are applicable, and not currently installed.


Where Action = ‘Install’ and [SUSDB].[PUBLIC_VIEWS].[vComputerTarget].[Name] like ‘computername%’ and state != 1 and state != 4


If you want to see the complete update status of a computer, excluding only the non-applicable updates, this will do the trick.  That said, its a BIG query and take a long time.  As in, go get some coffee, chat with your buds and maybe play a round of golf.  You might run out of memory too with this query depending on your SQL server.  In case you didn’t notice, I took out the “where Action = ‘Install'”.  As in show me any update that’s applicable, with any status, and any approval setting.


Where ‘Install’ and [SUSDB].[PUBLIC_VIEWS].[vComputerTarget].[Name] like ‘pc-2158%’ and state != 1


Play around your self and I think you’ll see it’s pretty amazing all the different reports your can create.  I would love to say the WSUS DB was easy to read / figure out, but IMO, its probably one of the more challenging DB’s I’ve figured out.  There are sometimes multiple joins needed in order to link together data that you’d think would have been in a flat table.  I suspect that, combined with missing indexes is part of the reason the DB is so slow.  I wish MS would simplify this DB, but I’m sure there’as a reason its designed the way it is.

SQL Query: Microsoft – WSUS – Computers to Computer Target

This is a simple query you can use map your computers to their various targets in a nice easy to export table.  I’d love to say the script is sexier than that, but its really not.  You can find the sql query here.

There is only one section worth mentioning because it can change the way a computer is mapped to a computer target.


Where [SUSDB].[dbo].[tbExpandedTargetInTargetGroup].[IsExplicitMember] = 1