Check when servers were last patched with Windows Update via COM or WSUS

From Svendsen Tech PowerShell Wiki
Jump to: navigation, search

You might find yourself wanting a report of when servers or workstations in a certain OU, or the entire Active Directory, were last patched. Here you will find a script using WSUS and one querying online servers or workstations with COM. Both remote COM and local COM via PowerShell remoting are supported, and there's an asynchronous version using PowerShell runspaces.

I should also mention the WMI class Win32_QuickFixEngineering, but I experienced various bugs with it, so I decided on these other approaches.

See this article for how to get computer names from an OU in AD or the entire AD.




Win32_QuickFixEngineering (broken?)

I'll throw in my 5 cents about Win32_QuickFixEngineering right now:

My first (and so far last) attempt with Win32_QuickFixEngineering looked like this:

PS C:\PowerShell> $servers | % { gwmi win32_quickfixengineering -computer $_ |
>> ?{ $_.installedon } | sort @{e={[datetime]$_.InstalledOn}} | select -last 1 }
>>

There, "$servers" is an array of strings containing server names; you can use "gc servers.txt" or Get-QADComputer, Get-ADComputer or similar in its place, of course. You might want to silence or redirect errors if running it against servers where WMI might fail. To silence, you can use -ErrorAction SilentlyContinue, but then you won't see the ones that fail. If you use "-ErrorVariable WmiErr" (remember not to use a "$" there), the errors will be captured in the variable $WmiErr, but using this one-liner, it won't be terribly useful, since it won't contain information about which server it failed on. You can write something more elaborate, use try/catch, etc., but I personally won't bother since it's not useful to me right now.

Anyway, the results I got were all wrong. The dates were incorrect compared to what my COM script reported, which I knew to be correct. Four out of 24 test servers reported correct dates; all the others had the wrong date. Some had dates going years back, some a few months back. Maybe I'm doing something wrong, but it doesn't seem too obvious at first glance. This is a mixed environment with Server 2003 R2, 2008 and 2008 R2. Errors cannot consistently be tied to one specific of the OSs mentioned.

Find Last Installed Update via Windows Update Using WSUS

This script finds the ArrivalDate of the last update which has been reported as installed to the WSUS server you're querying. There appears to be no property to retrieve the date when it was actually installed - from WSUS - only when it arrived, combined with the fact that it indeed is installed. Together they serve as a decent indicator in most cases, I would think. I'm not entirely satisfied with this solution, but it will have to do for now.

I might have overlooked some approach, but I couldn't find a property anywhere in the WSUS objects for when it was actually installed, so I ended up writing this based on the arrival date and the fact that it is installed. I have verified that the date for arrival and installation indeed can be different for the last update.

Another thing to consider is that you will not receive data about computers that have never reported a single update as installed. WSUS seems sort of clunky to deal with. It's also a major flaw that you can't get reports of which computers are missing a certain update from the GUI utility. Maybe a bunch of wrapper functions / cmdlets would be a good idea. I didn't find too much WSUS stuff at the Microsoft script center the last time I looked.

This Microsoft article describes the various update installation states used by Microsoft.UpdateServices.Administration.ComputerTargetScope and Microsoft.UpdateServices.Administration.UpdateScope.

The Data Looking Pretty in Excel

Here's a screenshot of some data from a test environment imported in Excel. The data is sorted on arrival date with the oldest first, then on computer/host name if the date is the same.

Get-Last-Update-WSUS-Excel-CSV.png

Download

This script will only work from an elevated PowerShell console/host as it requires administrative privileges.

Source Code

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | Out-Null

# This requires an elevated console/host.
$Wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()

# Create a computer scope that includes all computers that have at least one
# update reported to the WSUS server as in the installed state.
$ComputerScope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
$ComputerScope.IncludedInstallationStates = [Microsoft.UpdateServices.Administration.UpdateInstallationStates]::Installed

# Create an update scope that includes updates that are installed.
$UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
$UpdateScope.IncludedInstallationStates = [Microsoft.UpdateServices.Administration.UpdateInstallationStates]::Installed

$Computers = $Wsus.GetComputerTargets($ComputerScope)

# Data hashtable, indexed by the computers' "FullDomainName" property.
$LastUpdateData = @{}

foreach ($Computer in $Computers) {
    
    #$ComputerName = $Computer.FullDomainName # -replace '\..+' # strip off domain, if present
    $LastUpdateData.($Computer.FullDomainName) = New-Object PSObject    
    
    $InstalledUpdates = $Computer.GetUpdateInstallationInfoPerUpdate($UpdateScope)
    
    # The updates apparently are sorted in the order they were installed,
    # so here I rely on this logic and store the arrival date and title
    # of the last installed update in the hashtable object.
    $InstalledUpdates | Select-Object -Last 1 | Foreach-Object {
        
        $Update = $_.GetUpdate()
        
        Add-Member -MemberType NoteProperty -Name 'ArrivalDate' -Value $Update.ArrivalDate -InputObject $LastUpdateData.($Computer.FullDomainName)
        Add-Member -MemberType NoteProperty -Name 'Title' -Value $Update.Title -InputObject $LastUpdateData.($Computer.FullDomainName)
        
    }
    
}

$LastUpdateData.GetEnumerator() | Sort-Object -Property @{Ascending=$true;e={$_.Value.ArrivalDate}},@{Ascending=$true;e={$_.Name}} |
    Select-Object -Property @{n='Host';e={$_.Name}},@{n='Arrival Date';e={$_.Value.ArrivalDate}},@{n='Title';e={$_.Value.Title}} | 
    ConvertTo-Csv -NoTypeInformation | Set-Content Last-WSUS-Updates.csv

@"

Total computers found with at least one update installed: $($LastUpdateData.Keys.Count)
Output file: Last-WSUS-Updates.csv
"@

notepad Last-WSUS-Updates.csv

Find Last Installed Update via Windows Update Using Remote COM or PSRemoting

The script supports both remote COM (default) and local COM via PowerShell remoting. There is an asynchronous version as well as a "regular", serial one. It produces both HTML and CSV reports. The CSV reports can easily be imported in Excel. It will overwrite existing report files without prompting. See this article for how to get computer names from an OU in AD or the entire AD.

Remote COM can be tricky to make work through a firewall. Among other things, you need the "Windows Firewall Remote Administration Exception" - which is set via a GPO or with gpedit.msc - and RPC. I searched the web for a while and found various information, but something has happened in the lab so I am currently unable to make it work there although I got the SCOM agent push to work. If I disable the Windows firewall, it starts working immediately.

The error I'm seeing with remote COM is:

Terminating error: Exception calling "CreateInstance" with "1" argument(s):
"Retrieving the COM class factory for remote component with CLSID {4CB43D7F-7EEE-4906-8698-60DA1C38F2FE}
from machine winxpssd failed due to the following error: 800706ba."

It starts working immediately when I disable the firewall. PowerShell remoting, however, works in the lab.

Script Parameters

-ComputerName Required. A list of target computer names. Use "(gc computers.txt)", without the quotes, to use a file. See this article for various ways of retrieving computers from specific OUs or the entire Active Directory.
-PSRemoting Optional. Use PowerShell remoting and local COM via this remoting rather than remote COM. Easier to get working through firewalls, but requires that you have set up PowerShell remoting in the domain via GPO, or have enabled PSRemoting manually on target servers.
-NumJobs Optional. Only in the asynchronous version. Number of concurrent jobs (actually runspaces).
-CloseRunspacePool Optional. Only in the asynchronous version. Try to properly close the runspace pool. Sometimes this operation can hang, so I made the behaviour optional and non-default.

CSV Output Screenshot and HTML Report Screenshot

Here are some reports from a test environment. Dates are sorted in descending order, unlike the WSUS script. I guess I couldn't decide what's more useful, so it's inconsistent. Add the -PSRemoting flag to use PowerShell remoting and local COM via the PS remoting. The default is remote COM.

Get-Last-Update-Console-Csv-smaller.png Get-Last-Update-IE-HTML.png

Download

2013-09-03: Uploaded an asynchronous version using PowerShell runspaces for concurrency. You can expect about 1-2 minutes processing time per 100 servers, depending on hardware, phase of the moon, etc. It has a -NumJobs parameter that's set to 5 by default. About 10 or 20 jobs seem to be good numbers also. It's a lot faster than the completely serial version

2013-01-20: Important bug fixes. Some errors were incorrectly reported as successful, and rendered the date sorting useless. This should be fixed. Should only have been in effect with the -PSRemoting parameter

Asynchronous Version Screenshot

Get-Last-Update-Async-Example.png

Source Code

param([Parameter(Mandatory=$true)][string[]] $ComputerName,
      [switch] $PSRemoting
     )

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

$StartTime = Get-Date

function ql { $args }

$LastUpdates = @{}
$Errors      = @{}

foreach ($Computer in $ComputerName | Where { $_ -match '\S' }) {
    
    Write-Host -NoNewline -Fore Green "`rProcessing ${Computer}...                          "
    
    $script:ContinueFlag = $false
    
    if ( -not (Test-Connection -Quiet -Count 1 -ComputerName $Computer) ) {
        
        $Errors.$Computer = 'Error: No ping reply'
        continue
        
    }
    
    # Use "local COM" (well, local, but remote via PS) and Invoke-Command if PSRemoting is specified.
    if ($PSRemoting) {
        
        try {
            
            $Result = Invoke-Command -ComputerName $Computer -ErrorAction Stop -ScriptBlock {
                
                [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Update.Session') | Out-Null
                $Session = New-Object -ComObject Microsoft.Update.Session
                
                try {
                    
                    $UpdateSearcher   = $Session.CreateUpdateSearcher()
                    $NumUpdates       = $UpdateSearcher.GetTotalHistoryCount()
                    $InstalledUpdates = $UpdateSearcher.QueryHistory(1, $NumUpdates)
                    
                    if ($?) {
                        
                        $LastInstalledUpdate = $InstalledUpdates | Select-Object Title, Date | Sort-Object -Property Date -Descending | Select-Object -first 1
                        # Return a collection/array. Later it is assumed that an array type indicates success.
                        # Errors are of the class [System.String]. -- Well, that didn't work so well in retrospect.
                        $LastInstalledUpdate.Title, $LastInstalledUpdate.Date
                        
                    }
                    
                    else {
                        
                        "Error. Win update search query failed: $($Error[0] -replace '[\r\n]+')"
                        
                    }
                    
                } # end of inner try block
                
                catch {
                    
                    $Errors.$Computer = "Error (terminating): $($Error[0] -replace '[\r\n]+')"
                    continue
                    
                }
                
            } # end of Invoke-Command
            
        } # end of outer try block
        
        # Catch the Invoke-Command errors here
        catch {
            
            $Errors.$Computer = "Error with Invoke-Command: $($Error[0] -replace '[\r\n]+')"
            continue
            
        }
        
        # $Result here is what's returned from the invoke-command call.
        # I can't populate the data hashes inside the Invoke-Command due to variable scoping.
        if (-not $Result -is [array]) {
            
            $Errors.$Computer = $Result
            
        }
        
        else {
            
            $Title, $Date = $Result[0,1]
            
            $LastUpdates.$Computer = New-Object PSObject -Property @{
                
                'Title' = $Title
                'Date'  = $Date
                
            }
            
        }
        
    }
    
    # If -PSRemoting isn't provided as an argument, try remote COM.
    else {
        
        try {
            
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Update.Session')
            $Session = [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session", $Computer))
        
            $UpdateSearcher   = $Session.CreateUpdateSearcher()
            $NumUpdates       = $UpdateSearcher.GetTotalHistoryCount()
            $InstalledUpdates = $UpdateSearcher.QueryHistory(1, $NumUpdates)
            
            if ($?) {
                
                $LastInstalledUpdate   = $InstalledUpdates | Select-Object Title, Date | Sort-Object -Property Date -Descending | Select-Object -first 1
                $LastUpdates.$Computer = New-Object PSObject -Property @{
                    
                    'Title' = $LastInstalledUpdate.Title
                    'Date'  = $LastInstalledUpdate.Date
                    
                }
                
            }
            
            else {
                
                $Errors.$Computer = "Error. Win update search query failed: $($Error[0].ToString())"
                
            }
            
        }
        
        catch {
            
            $Errors.$Computer = "Terminating error: $($Error[0].ToString())"
            
        }
        
    }
    
}

# Define properties for use with Select-Object
$Properties = 
    @{n='Server'; e={$_.Name}},
    @{n='Date';   e={$_.Value.Date}},
    @{n='Title';  e={$_.Value.Title}}

# Create HTML header used for both HTML reports.
$HtmlHead = @"
<title>Last Update Report ($(Get-Date -uformat %Y-%m-%d))</title>
<style type='text/css'>

    table        { width: 100%; border-collapse: collapse }
    td, th       { color: black; padding: 2px; }
    /* tr:nth-child(odd)  { background-color: #CCC }
    tr:nth-child(even) { background-color: #FFF } */
    th           { background-color: #C7C7C7; text-align: left }
    td           { background-color: #F7F7F7; text-align: left }
</style>
"@

## Create HTML data
# Create HTML body for updates report (successfully processed hosts)
$HtmlBody = $LastUpdates.GetEnumerator() | Sort -Property @{Expression={$_.Value.Date}; Ascending=$false},@{Expression={$_.Name}; Ascending=$true} |
    Select-Object -Property $Properties | ConvertTo-Html -Fragment
ConvertTo-Html -Head $HtmlHead -Body $HtmlBody | Set-Content last-updates.html

# Creating new HTML Body for hosts with errors.
$HtmlBody = $null
$HtmlBody = $Errors.GetEnumerator() | Sort -Property Name | Select-Object Name,Value | ConvertTo-Html -Fragment
ConvertTo-Html -Head $HtmlHead -Body $HtmlBody | Set-Content last-updates-errors.html

## Create CSV data
# Create last update CSV file
$LastUpdates.GetEnumerator() | Sort -Property @{Expression={$_.Value.Date}; Ascending=$false},@{Expression={$_.Name}; Ascending=$true} |
    Select-Object -Property $Properties | ConvertTo-Csv | Set-Content last-updates.csv

# Create error CSV file
$Errors.GetEnumerator() | Sort -Property Name | Select-Object Name,Value |
    ConvertTo-Csv | Set-Content last-updates-errors.csv


@"

Error count:   $($Errors.Values.Count)
Success count: $($LastUpdates.Values.Count)
Total count:   $([int] $Errors.Values.Count + $LastUpdates.Values.Count)

Script start time: $StartTime
Script end time::  $(Get-Date)
HTML Output files: last-updates.html, last-updates-errors.html
CSV Output files:  last-updates.csv, last-updates-errors.csv

"@