If you quickly want to find out if a particular update has been installed on all of your machines, the built-in reporting of Windows Server Update Services (WSUS) is not really helpful. However, you can use PowerShell to create a WSUS update report.

Alex Chaika

Alex Chaika is a Microsoft Certified Solution Expert (MCSE) with more than 15 years of experience in IT systems engineering. He currently focuses on PowerShell and VMware PowerCLI.

I wrote the PowerShell script I describe in this post due to the recent unfortunate events when the WannaCry ransomware infected literally hundreds of thousands of computers. As usual, the main reason was that people didn't install security updates on time.

To check the state of all of our computers, I tried to use WSUS reporting. However, I quickly found out that the tool is not very flexible. Thus, it is extremely arduous to get the report for all machines even if you are looking for just one particular KB number. As you can see below, the report choices are pretty poor:

WSUS report console

WSUS report console

You need to create a report for every update of every operating system type. Imagine that you have many different Windows versions starting from Windows XP. It'll take you forever. Fortunately, you can use PowerShell to achieve your goal faster.

To simplify things a bit and enable reusing the same script in the future to produce reports for different KBs, I use a function that accepts the following parameters:

  • An array of strings for the KB numbers
  • A string for the WSUS server name
  • A string for the WSUS port number

To be able to run this function successfully, you need the Windows Update Services MMC snap-in installed. Otherwise you can run it on the WSUS server. Please note that by default, this function connects to the WSUS server using unsecured HTTP. If you're using SSL, you have to change the $False to $True in the line that initializes the $wsus variable.

I prepare the $report variable in advance, to be able to save the results into it later, and then I load the Microsoft.Update services assembly. Next, I initialize the following three variables:

  • $wsus: update services server object
  • $UpdateScope: WSUS update scope (list of updates on the WSUS server)
  • $CompSc: computer objects registered in WSUS

Next, I set up $UpdateScope.UpdateApprovalActions to "Install" because I'm interested only in those updates approved for installation.

I then start a foreach loop against the $kbnumber string array I intend to pass to the function. This provides all update objects that have the particular KB number in the title.

Inside the second loop, I'm using the update object's GetUpdateInstallationInfoPerComputerTarget method to get the status of the update for each computer object stored in $ComSc. To understand better how the GetUpdateInstallationInfoPerComputerTarget method works, take a look at the screenshot below:

WSUS update installation info per computer target

WSUS update installation info per computer target

The update status object contains the ComputerTargetId property, which is a unique GUID associated with each computer object. The GUID is the key that allows me to establish relationships between the update status objects and computer names.

In the third loop (ForEach object) I pass this GUID to the getComputerTarget method of the update service object (that the $wsus variable represents). This reads several properties of the computer object, such as computer name, IP address, and update status information. Below you can see an example of the WSUS computer object:

WSUS computer object information

WSUS computer object information

Next, I create a custom PowerShell object and store it in the $info variable. The properties of the $info object then contain the computer object properties and update the status properties I need for the report. The $report variable then stores an array of my $info objects.

After the loops execute, I filter the $report variable to remove all records where UpdateInstallationStatus is set to Installed, Not Applicable, and Unknown. The remaining records are the computers that require the updates with the KB numbers you specify when you call the function.

In the screenshot below you see a sample report:

WSUS updates report

WSUS updates report

At the end of the script, I added three arrays with the KB numbers that are critical for mitigating the vulnerabilities used for the recent WannaCry ransomware outbreak. I obtained information on the KB numbers from the following official sources:

Join the 4sysops PowerShell group!

7+

Users who have LIKED this post:

  • avatar
Share
22 Comments
  1. Paolo Maffezzoli 2 years ago

    Thanks Alex for this article, it can help to improve the Wsus reporting and investigation if and where is correctly applied a MS patch.

    0

  2. Lisandro da Silva 2 years ago

    Hi Alex

    Nice script! This is exactly what i'm looking for!

    I had some issues running this script.

    Could you provide me an explanation on how to use the script?

    Do you need to run this script on the WSUS server or can you do this remotely

    0

    • Author
      Alex Chaika 2 years ago

      You either need to run it on the WSUS server or on the server which has WSUS management console installed.

      0

  3. luciano 2 years ago

    hi when I run the script I get this error :

    Exception calling "GetUpdateServer" with "3" argument(s): "The underlying connection was closed: An unexpected error occurred on a receive."

    At line:8 char:1

    + $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpda ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : WebException

    You cannot call a method on a null-valued expression.

    At line:13 char:4

    + $updates = $wsus.GetUpdates($updateScope) | ?{$_.Title -match $kb} ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : InvalidOperation: (:) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.

    At line:13 char:4

    + $updates = $wsus.GetUpdates($updateScope) | ?{$_.Title -match $kb} ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : InvalidOperation: (:) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

    any ideas why?

    I'm using https (8531) and I changed this line  :

    getUpdateServer($wsusserver,$true,8531)

    any ideas how can I fix it ?thanks

     

    0

    • Author
      Alex Chaika 2 years ago

      If you are using self-signed certificate for your wsus server SSL please make sure that this certificate is added to the "Trusted Certificate Authorities" in the Certificates of the  computer you're running this script from.

      If the certificate you are using is from trusted CA, make sure that CAs certificate(s) are trusted (exist in the Trusted Certificate Authorities of the  computer you're running this script from).

      0

  4. Termid 2 years ago

    Hello Alex,

    first of all thanks for your work!

    I've pulled your code, saved it as .ps1 and ran on the WSUS Upstream Server with the elevated rights.

    at the end of the code you call your function and all looks good sofar, however genereated report file is just empty :-/ there is no single line... even no headers.

    and it doesn't give any errors back. Any Idea what it could be?

    Thanks in Advance!

    0

    • Author
      Alex Chaika 2 years ago

      If report file is empty it means that the loop which gets the updates info doesn't do its job. So' Id check that updates with KB numbers assigned to the arrays in the lines 35, 36, 37 are  downloaded to WSUS and approved.

      0

  5. Olaf 2 years ago

    I would like to fill the $MS17010 = "4012212","4012598","4012215","4012213","4012216","4012214","4012217","4012606","4013198"

    line from a text file

    How would i do this ?

    0

  6. mark menditto 2 years ago

    What if the computers are reporting to a downstream replica WSUS? On an upstream master WSUS, the report only reports computers contacting this server. Thanks.

    0

  7. CHRISTIAN CHAMEYRAT 2 years ago

    Hello Alex,

    Great job, but how can I use it to have the report of my 2 WSUS servers (upstream+downstream)?

    When I ran it from th upstream I got only results about machines linked to it, in my case 2. and when I ran from the downstream server i got only machines linked to it too.

    Thansk for your help

    0

    • Charlie 2 years ago

      Hi Christian,

      I am seeing the same behaviour - did you ever get a reply / figure this out?

      We have around 15 downstream servers so i wold have to run this script for each of them which would be slower than using WSUS itself to report.

      ..Charlie.

      0

      • Tom 2 years ago

        Hi Charlie,

        Looks like this is all being done using the Microsoft.UpdateServices.Administration API (https://msdn.microsoft.com/en-us/library/microsoft.updateservices.administration(v=vs.85).aspx)

        If you dig through the classes, there's options to include downstream computer targets (part of the ComputerTargetScope class). You'd have to create a ComputerTargetScope object and set the IncludeDownstreamComputerTargets. I believe that to get this to work, the downstream replicas would have to be have Reporting Rollup set, but haven't tested without rollup as it's the default.

        $CompSc = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
        $CompSc.IncludeDownstreamComputerTargets = $true

        Adding this 'filtering' to you the original script should include those downstream clients.

        ..Tom

        1+

  8. Kurt 2 years ago

    @olaf,

    Put the values ("4012212","4012598","4012215","4012213","4012216","4012214","4012217","4012606","4013198","4013429") into a text file, no headers, no footers, no quotes, no commas, and one item per line - for instance, c:\data\ms17010.txt

    Change this:

    $MS17010 = "4012212","4012598","4012215","4012213","4012216","4012214","4012217","4012606","4013198","4013429"

    Then do this instead:

    $MS17010 = get-content c:\data\ms17010.txt

    Kurt

    1+

  9. Steven Ahmet 2 years ago

    Hi Alex,

    Great work on this script. i've implemented this recently to look for an Outlook Security update that broke some functionality with Enterprise Vault.

    I'm looking for a way to automate the Synchronization Report that will give the result for the last 30 days. Do you know how to accomplish that?

    Thanks.

    0

  10. Obi Ejiofor 2 years ago

    You can user this script

    too create a compliance report with the csv files generated.

    $Date = (Get-Date -f yyyyMMdd)
    $path = “C:\script\wsus\compliance\convert\*”
    $csvs = Get-ChildItem $path -Include *.csv
    $y=$csvs.Count
    Write-Host “Detected the following CSV files: ($y)”
    foreach ($csv in $csvs)
    {
    Write-Host ” “$csv.Name
    }
    $outputfilename = “c:\script\wsus\compliance\convert\updatestatus-update-$Date.xlsx”
    Write-Host Creating: $outputfilename
    $excelapp = new-object -comobject Excel.Application
    $excelapp.sheetsInNewWorkbook = $csvs.Count
    $xlsx = $excelapp.Workbooks.Add()
    $sheet=1

    foreach ($csv in $csvs)
    {
    $row=1
    $column=1
    $worksheet = $xlsx.Worksheets.Item($sheet)
    $worksheet.Name = $csv.Name
    $file = (Get-Content $csv.PSPath)
    foreach($line in $file)
    {
    $linecontents=$line -split ‘,(?!\s*\w+”)’
    foreach($cell in $linecontents)
    {
    $worksheet.Cells.Item($row,$column) = $cell
    $column++
    }
    $column=1
    $row++
    }
    $sheet++
    }
    $xlsx.SaveAs( $outputfilename)
    $excelapp.quit()
    Start-Sleep -s 300
    del *.csv
    send-MailMessage -SmtpServer {mailserver} -To {mailadres receiver}, {mailadres receiver} -From {mailadres sender} -Subject "rapportage monthly install status" -Body "rapportage monthly install status" -Attachment "c:\script\wsus\compliance\convert\updatestatus-update-$Date.xlsx"
    Move-Item C:\script\wsus\compliance\convert\*.xlsx C:\script\wsus\reports\

    0

  11. Kenji 2 years ago

    hello all

    i am beginner of the poweshell and wsus, i got the error when run the script. since i can success once but after restart the powershell and run again the error occur, please advice, many thanks

    ERROR SCREEN

    PS C:\Users\Administrator> C:\scripts\wsusSearchReport.ps1
    Exception calling "GetUpdates" with "1" argument(s): "The operation has timed out"
    At C:\scripts\wsusSearchReport.ps1:13 char:4
    + $updates = $wsus.GetUpdates($updateScope) | ?{$_.Title -match $kb} ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : WebException

    COMMAND SCRIPT

    Function GetUpdateState {
    param([string[]]$kbnumber,
    [string]$wsusserver,
    [string]$port
    )
    $report = @()
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
    $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($wsusserver,$False,80)
    $CompSc = new-object Microsoft.UpdateServices.Administration.ComputerTargetScope
    $updateScope = new-object Microsoft.UpdateServices.Administration.UpdateScope;
    $updateScope.UpdateApprovalActions = [Microsoft.UpdateServices.Administration.UpdateApprovalActions]::Install
    foreach ($kb in $kbnumber){ #Loop against each KB number passed to the GetUpdateState function
    $updates = $wsus.GetUpdates($updateScope) | ?{$_.Title -match $kb} #Getting every update where the title matches the $kbnumber
    foreach($update in $updates){ #Loop against the list of updates I stored in $updates in the previous step
    $update.GetUpdateInstallationInfoPerComputerTarget($CompSc) | ?{$_.UpdateApprovalAction -eq "Install"} | % { #for the current update
    #Getting the list of computer object IDs where this update is supposed to be installed ($_.UpdateApprovalAction -eq "Install")
    $Comp = $wsus.GetComputerTarget($_.ComputerTargetId)# using #Computer object ID to retrieve the computer object properties (Name, #IP address)

    $info = "" | select UpdateTitle, LegacyName, SecurityBulletins, Computername, OS ,IpAddress, UpdateInstallationStatus, UpdateApprovalAction #Creating a custom PowerShell object to store the information
    $info.UpdateTitle = $update.Title
    $info.LegacyName = $update.LegacyName
    $info.SecurityBulletins = ($update.SecurityBulletins -join ';')
    $info.Computername = $Comp.FullDomainName
    $info.OS = $Comp.OSDescription
    $info.IpAddress = $Comp.IPAddress
    $info.UpdateInstallationStatus = $_.UpdateInstallationState
    $info.UpdateApprovalAction = $_.UpdateApprovalAction
    $report+=$info # Storing the information into the $report variable
    }
    }
    }
    #$report | ?{$_.UpdateInstallationStatus -ne 'NotApplicable' -and $_.UpdateInstallationStatus -ne 'Unknown' -and $_.UpdateInstallationStatus -ne 'Installed' } | Export-Csv -Path c:\temp\rep_wsus.csv -Append -NoTypeInformation
    $report | ?{$_.UpdateInstallationStatus -ne 'NotApplicable' -and $_.UpdateInstallationStatus -ne 'Unknown' } | Export-Csv -Path c:\temp\rep_wsus.csv -Append -NoTypeInformation
    } #Filtering the report to list only computers where the updates are not installed

    #$MS17010 = "4012212","4012598","4012215","4012213","4012216","4012214","4012217","4012606","4013198","4013429"
    $MS17010 = "4041693"
    $CVE20170162 = "4015221","4015219","4015217","4015583","4015550","4015547"
    $CVE20170261 = "3118310","3172458","3114375"

    GetUpdateState -kbnumber $MS17010 -wsusserver wsus02 -port 80

    0

  12. Anuj Jain 1 year ago

    Hi All,

    Thanks for the script,

    "Installed and No status computers  details are not exporting, Could you tell us where can we made  the change in the script.

    Thank You

     

    0

  13. Obi Ejiofor 8 months ago

    With psexcel you can easily create a readable excel report with the csvs generated

    {$Date = (Get-Date -f yyyyMMddHHmm)
    Import-Module PSExcel
    Import-Csv C:\script\wsus\compliance\convert\updates_rollup.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates_rollup
    Import-Csv C:\script\wsus\compliance\convert\updates_office_2013.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates_office_2013
    Import-Csv C:\script\wsus\compliance\convert\updates_office_2010.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates_office_2010
    Import-Csv C:\script\wsus\compliance\convert\updates_office_2016.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates_office_2016
    Import-Csv C:\script\wsus\compliance\convert\updates-Windows-2016.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates-Windows-2016
    Import-Csv C:\script\wsus\compliance\convert\updates-server-software.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates-server-software
    Import-Csv C:\script\wsus\compliance\convert\updates_ie11_flashplayer.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName updates_ie11_flashplayer
    Import-Csv C:\script\wsus\compliance\convert\Dot_Net.csv | Export-XLSX -Path C:\script\wsus\compliance\convert\$Date-Updates.xlsx -WorksheetName Dot_Net
    Start-Sleep -s 60
    New-Item -Path C:\script\wsus\reports\$Date-csv-files -ItemType directory
    New-Item -Path C:\script\wsus\reports\$Date-Compliance-Report -ItemType directory
    send-MailMessage -SmtpServer {mail server} -To {receiver adress}  -From {sender address} -Subject "rapportage monthly install status" -Body "rapportage monthly install status" -Attachment "c:\script\wsus\compliance\convert\$Date-Updates.xlsx"
    Move-Item -Force "C:\script\wsus\compliance\convert\*.xlsx" "C:\script\wsus\reports\$Date-Compliance-Report\"
    Move-Item -Force "C:\script\wsus\compliance\convert\*.csv" "C:\script\wsus\reports\$Date-csv-files\"}

    Then its simply a matter of putting the powershell scripts together under one script like this

    {.\dotnet.ps1
    .\updates-ie11-Flashplayer.ps1
    .\updates-office-2010.ps1
    .\updates-office-2013.ps1
    .\updates-office-2016.ps1
    .\updates-rollup
    .\updates-Windows-2008.ps1
    .\updates-Windows-2016.ps1
    .\updates-server-software.ps1
    Start-Sleep -s 120
    .\convert.ps1}

    1+

  14. MarcinP 6 months ago

    Hi

    In order to include computers reporting to replica servers you need to add the line:

    $CompSc.IncludeDownstreamComputerTargets = $true

    below $CompSc declaration

    it is also required to configure WSUS to roll up update and computer status from replica servers. Last but not least - don't forget to sync WSUS replica status as otherwise report may not be valid and some computer/patch status may be missing.

     

    1+

    Users who have LIKED this comment:

    • avatar
  15. Mark M. 6 months ago

    Thanks for sharing this, it's the best WSUS report tool I've run into. For a compliance request I needed to prove a hotfix was installed. A small change to the report line saved me countless time.

    0

  16. Denis 4 months ago

    hello,

     

    would it be possible to get such report for all available KBs on WSUS server ?

     

    Thanks !

    0

  17. Pete 6 days ago

    Where is the script?

    0

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2019

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account