about WSUS reporting

Before we start, may I introduce the official Terminology for Update Status
As you can see, the status “Needed” and “Installed/Not Applicable”, don’t mean the same thing when it applies either to a single computer or a computer target group. Don’t let the GUI fool you!

This article from the WSUS team in 2008 said that:

Usually there are two kinds of reports people run: The default WSUS reports accessed from the WSUS MMC console and the reporting tools located in the API Samples and Tools:
WSUS3: http://download.microsoft.com/download/5/d/c/5dc98401-bb01-44e7-8533-3e79ae0e0f97/Update%20Services%203.0%20API%20Samples%20and%20Tools.EXE

I’ve a Windows Server 2012 R2 core edition where I don’t want to install the Report Viewer.

I’m stuck with the second option and almost forced into using the API samples and tools πŸ˜‰

What are these tools ?

Before I go through the above list, you should know that things have changed since 2008.
A common best practice and approach in PowerShell is to return objects and let you choose the format you want to export: CSV, XML,…

I’ll now focus on showing how to achieve what the API samples and tools do with PowerShell.

  • ApprovedUpdatesToXML
    # ApprovedUpdatestoXML
    # \Update Services 3.0 API Samples and Tools\ApprovedUpdatesToXML
    
    $updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
    $updateScope.ApprovedStates = (
    ([Microsoft.UpdateServices.Administration.ApprovedStates]::HasStaleUpdateApprovals.value__+
    [Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved.value__)
    )
    
    # get the list of updates that are approved, or have an older revision that is approved
    $Updates = (Get-WsusServer).GetUpdates($updateScope) 
    # calling IUpdate.GetUpdateApprovals once for each update can be expensive, so instead
    # we use call IUpdateServer.GetUpdateApprovals() to get all approvals for all updates at once
    $allUpdateApprovals = (Get-WsusServer).GetUpdateApprovals($updateScope)
    # similarly, construct a table of target group
    $allTargetGroups = (Get-WsusServer).GetComputerTargetGroups()
    $Updates | ForEach-Object {
        $update = $_
        [PSCustomObject]@{
            Id = $update.Id.UpdateId.ToString() ;
            Title = $update.Title ;
            Classification = $update.UpdateClassificationTitle ;
            Approvals = $( 
            if ($update.IsApproved) {
                # This revision (the latest revision) is approved; get the approvals and write them
                $allUpdateApprovals | Where { 
                    $_.UpdateID.UpdateID -eq $update.Id.UpdateId
                } | 
                ForEach-Object {
                    $approval = $_
                    [PSCustomObject]@{
                        RevisionNumber = $update.Id.RevisionNumber ;
                        TargetGroup = ($allTargetGroups | Where { $_.ID -eq $approval.ComputerTargetGroupId }).Name ;
                        Approval = $approval.Action ;
                        Deadline = $( 
                            if ($_.Deadline -lt ([datetime]::maxvalue)) {
                                $_.Deadline    
                            } else {
                                "None"
                            }
                        ) ;
                        ApprovalDate = $approval.CreationDate ;
                    }
                }
            } elseif ($update.HasStaleUpdateApprovals) {
                # This revision has older revisions that are approved; get their approvals and write them
                $update.GetRelatedUpdates(
                    [Microsoft.UpdateServices.Administration.UpdateRelationship]::AllRevisionsOfThisUpdate
                    ) | Where isApproved | 
                    ForEach-Object {
                    $revision = $_
                    $revision.GetUpdateApprovals() |
                    ForEach-Object {
                        $approval = $_
                        [PSCustomObject]@{
                            RevisionNumber = $update.Id.RevisionNumber ;
                            TargetGroup = ($allTargetGroups | Where { $_.ID -eq $approval.ComputerTargetGroupId }).Name ;
                            Approval = $approval.Action ;
                            Deadline = $( 
                                if ($_.Deadline -lt ([datetime]::maxvalue)) {
                                    $_.Deadline    
                                } else {
                                    "None"
                                }
                            ) ;
                            ApprovalDate = $approval.CreationDate ;
                        }
                    }
                    }
            } else {
            }
        ) ;
        }
    }  | Export-Clixml -Depth 2 -Path $home\Documents\ApprovedUpdatesToXML.xml
    
  • ComputerStatusToXML
  • # ComputerStatusToXML
    # \Update Services 3.0 API Samples and Tools\ComputerStatusToXML
    
    $computerScope = New-object Microsoft.UpdateServices.Administration.ComputerTargetScope
    $computerScope.IncludeDownstreamComputerTargets = $true
    (Get-WsusServer).GetComputerTargets($computerScope) | ForEach-Object {
        $computer = $_
        [PSCustomObject]@{
            Name = $computer.FullDomainName ;
            LastReportedStatus = $computer.LastReportedStatusTime ;
            ParentServer = $(
                if ($computer.ParentServerId -ne [System.Guid]::Empty) {
                    $computer.GetParentServer().FullDomainName
                } else {
                    'localhost'
                }
            );
            UpdateStatus = $(
                $computer.GetUpdateInstallationInfoPerUpdate() | 
                Where {
                    $_.UpdateInstallationState -ne 'NotApplicable'
                } |
                ForEach-Object {
                    [PSCustomObject]@{
                        Title = $_.GetUpdate().Title ;
                        Status = $_.UpdateInstallationState ;
                    }
                }
            );
            }
    } | 
    Export-Clixml -Depth 2 -Path $home\Documents\ComputerStatusToXML.xml
    
  • ListApprovedUpdates
  • # \Update Services 3.0 API Samples and Tools\ListApprovedUpdates
    
    $updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
    $updateScope.ApprovedStates = (
    ([Microsoft.UpdateServices.Administration.ApprovedStates]::HasStaleUpdateApprovals.value__+
    [Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved.value__)
    )
    
    (Get-WsusServer).GetUpdates($updateScope) | ForEach-Object {
        $update = $_ 
        if ($_.isApproved) {
            $_.GetUpdateApprovals() | ForEach-Object {
                $approval =  $_
                [PSCustomObject]@{
                    Title = $update.Title ;
                    Classification = $update.UpdateClassificationTitle ;
                    'Applies to' = $update.ProductTitles
                    'Approved on' = $approval.CreationDate
                }
                
            }
        }
    }
    
  • UpdateStatusToCSV
  • # UpdateStatusToCSV    
    # Update Services 3.0 API Samples and Tools\UpdateStatusToCSV
    $updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
    $updateScope.ApprovedStates = (
    ([Microsoft.UpdateServices.Administration.ApprovedStates]::HasStaleUpdateApprovals.value__+
    [Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved.value__)
    )
    
    (Get-WsusServer).GetUpdates($updateScope) | ForEach-Object {
        $update = $_
        $update.GetSummaryPerComputerTargetGroup() | ForEach-Object {
            if ($_.ComputerTargetGroupId.Equals([Microsoft.UpdateServices.Administration.ComputerTargetGroupId]::AllComputers)) {
                [PSCustomObject]@{
                    'Update Name' = $update.Title
                    Classification = $update.UpdateClassificationTitle
                    Installed = $_.InstalledCount
                    'Installed Pending Reboot' = $_.InstalledPendingRebootCount
                    Needed = ($_.DownloadedCount + $_.NotInstalledCount)
                    'Not Needed' = $_.NotApplicableCount
                    Failed = $_.FailedCount
                    Unknown = $_.UnknownCount
                    'Last Updated' = $(
                        switch ($_.LastUpdated) {
                            ([datetime]::MinValue) {
                                "Never" ; break
                            }
                            default {$_.ToShortDateString()}
                        })
                }
            }
        }
    } | Export-Csv -Path $home\Documents\ApprovedUpdateStatus.csv
    
  • UpdateStatustoXML
  • # UpdateStatustoXML
    # Update Services 3.0 API Samples and Tools\UpdateStatusToXML
    
    $Updates = (Get-WsusServer).GetUpdates()
    $AllComputersGroup = (Get-WsusServer).GetComputerTargetGroup(
    [Microsoft.UpdateServices.Administration.ComputerTargetGroupId]::AllComputers
    )
    
    $Updates | Where {-not($_.IsDeclined)} | ForEach-Object -Process {
        [PSCustomObject]@{
            Title = $_.Title
            ComputerStatus = ($AllComputersGroup.GetUpdateInstallationInfoPerComputerTarget($_) | ForEach-Object {
                [pscustomobject]@{
                    ComputerName = $_.GetComputerTarget().FullDomainName ;
                    InstallationStatus = $_.UpdateInstallationState.ToString() ;
                    ApprovalStatus = $_.UpdateApprovalAction.ToString()
    
                }
            })
        }
    } | Export-Clixml -Depth 2 -Path $home\Documents\ApprovedUpdateStatus.xml
    
    
  • UpdatesToXML
  • # UpdatesToXML
    # Update Services 3.0 API Samples and Tools\UpdatesToXML
    
    (Get-WsusServer).GetUpdates(
        [Microsoft.UpdateServices.Administration.ApprovedStates]::Any,
        [datetime]::MinValue,
        [datetime]::MaxValue,
        $null,
        $null
    ) | ForEach-Object -Process {
        $_ | 
        Add-Member -MemberType ScriptProperty -Name UpdateID -Value {
            $this.Id.UpdateID.toString().ToUpper()
        } -Force -PassThru |
        Add-Member -MemberType ScriptProperty -Name BundledUpdates -Value { 
                $_.GetRelatedUpdates(
                    [Microsoft.UpdateServices.Administration.UpdateRelationship]::UpdatesBundledByThisUpdate
                ) | 
                Select -Property @{l='UpdateID';e={$_.Id.UpdateID.ToString().ToUpper()}},
                Title,@{l='Classification';e={$_.UpdateClassificationTitle}}
        } -Force -PassThru | 
        Add-Member -MemberType ScriptProperty -Name BundlingUpdates -Value {
                $_.GetRelatedUpdates(
                    [Microsoft.UpdateServices.Administration.UpdateRelationship]::UpdatesThatBundleThisUpdate
                ) | 
                Select -Property @{l='UpdateID';e={$_.Id.UpdateID.ToString().ToUpper()}},
                Title,@{l='Classification';e={$_.UpdateClassificationTitle}}
        } -Force -PassThru | 
        Add-Member -MemberType ScriptProperty -Name SupersededUpdates -Value {
                $_.GetRelatedUpdates(
                    [Microsoft.UpdateServices.Administration.UpdateRelationship]::UpdatesSupersededByThisUpdate
                ) |
                Select -Property @{l='UpdateID';e={$_.Id.UpdateID.ToString().ToUpper()}},
                Title,@{l='Classification';e={$_.UpdateClassificationTitle}}
        } -Force -PassThru | 
        Add-Member -MemberType ScriptProperty -Name SupersedingUpdates -Value {
                $_.GetRelatedUpdates(
                    [Microsoft.UpdateServices.Administration.UpdateRelationship]::UpdatesThatSupersedeThisUpdate
                ) |
                Select -Property @{l='UpdateID';e={$_.Id.UpdateID.ToString().ToUpper()}},
                Title,@{l='Classification';e={$_.UpdateClassificationTitle}}
        } -Force -PassThru
        
    } | Select -Property UpdateID,Title,@{l='Classification';e={$_.UpdateClassificationTitle}},
    LegacyName,Description,@{l='SyncDate';e={$_.ArrivalDate.ToLocalTime()}},
    @{l='ReleaseDate';e={$_.CreationDate.ToLocalTime()}},MsrcSeverity,SecurityBulletins,
    @{l='KBArticles';e={$_.KnowledgeBaseArticles}},AdditionalInformationUrls,
    BundledUpdates,BundlingUpdates,SupersededUpdates,SupersedingUpdates |
    Export-Clixml -Depth 2 -Path $home\Documents\UpdatesToXML.xml
    

The above samples show that it’s possible to do some reporting under PowerShell but there are some limitations and drawbacks.

For example, UpdateStatustoXML sample is extremely slow as it will go through all updates except those that are declined.
To reduce its scope and increase performance, you should consider filtering on the left.

I was looking at WSUS reporting mainly because I wanted to determine the compliance level of the Windows 7 computers I grouped under a target group named ‘Windows 7 x64’.
How can I determine a compliance ratio?
More precisely, I wanted to know what was installed on my computer against what I approved and what may be missing again against what I previously approved.

I wanted to use the ApprovedComputerTargetGroups of the UpdateScope object to get a better filter but it’s a read-only property.

The trick that let me find my way was the updatescope used to filter both the GetUpdateApprovals and GetUpdateInstallationSummary methods.


$targetgroup = (Get-WsusServer).GetComputerTargetGroups() | Where Name -eq "Windows 7 x64"

$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
$updateScope.UpdateApprovalActions = "Install"
$updateScope.ApprovedStates = (
([Microsoft.UpdateServices.Administration.ApprovedStates]::HasStaleUpdateApprovals.value__+
[Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved.value__)
)
$total = (Get-WsusServer).GetUpdateApprovals($updateScope).Count
$targetgroup.GetComputerTargets($true) | Where FullDomainName -match "^MyComputerNamePattern" | 
ForEach-Object {
    $computer = $_
    $State = $computer.GetUpdateInstallationSummary($updateScope)
    [PSCustomObject]@{
        Name = $_.FullDomainName ;
        IPAddress = $_.IPAddress ;
        # 'Operating System'  = $_.OSDescription ;
        Version = $_.ClientVersion ;
        RebootPending = $(
            if($State.InstalledPendingRebootCount) {
                $true
            } else {
                $false
            }
        );
        Compliant = '{0:P}' -f (($State.InstalledCount)/($total - $State.NotApplicableCount));
        Needed = $State.FailedCount + $State.DownloadedCount + $State.UnknownCount + $_.NotInstalled
    }
}


As you can see above, I’ve got I computer that misses 15 updates. It’s expected as it’s mainboard is out-of-order…

Please note that there’s also a way to do this directly from the database.
The SQL path is available on http://blogs.technet.com/b/wsus/archive/2008/06/20/baseline-compliance-report-using-public-wsus-views.aspx

If you’re looking for the Microsoft.UpdateServices.Administration Namespace documentation it’s available on MSDN.

There are other samples available online. There’s for example: How to Determine All Approved Updates.

I’d like also to reference the great work Boe Prox already did about WSUS reporting:

As we are today the second Tuesday of August, may I wish you happy Patch Tuesday and reboot Wednesday πŸ˜€

Advertisements

4 thoughts on “about WSUS reporting

  1. This is some great stuff!

    You can get around the read-only issue on the $UpdateScope.ApprovedComputerTargetGroups by using the Add() method of the property and supply a Microsoft.UpdateServices.Administration.IComputerTargetGroup object.

    $UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
    $TargetGroup = $wsus.GetComputerTargetGroups() | Select -First 1
    # Needs Microsoft.UpdateServices.Administration.IComputerTargetGroup
    $UpdateScope.ApprovedComputerTargetGroups.Add($TargetGroup)
    # Verify target group added
    $UpdateScope.ApprovedComputerTargetGroups

  2. Very well written and explained. I am after compliance ratio report. I am able to run script successfully but it doesn’t give any output, I did pipe it to go to csv but it is blank.

    • Hi,
      Thx.
      What script did you run successfully? The last piece of code in the page?
      If yes, you’ll need to modify the line #10 because I’m filtering the results based on my computer name’s pattern.
      IOW, you should change the MyComputerNamePattern regular expression to match the computers names in your environment.

      $targetgroup.GetComputerTargets($true) | 
      Where FullDomainName -match "^MyComputerNamePattern" | 
      

      To learn more about regular expressions you read:

      Get-Help about_Regular_Expressions
      

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