Tag Archives: T-SQL

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.

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