Share via

Powershell: Generating WSUS Reports

  • Article

In a large environment the WSUS report can take a while to generate and even then it requires you to flip through what could be thousands of pages just to view which computers are missing what.

Figuring out a way to generate a CSV report that would display all the computers in a given WSUS group along with any updates they were missing. With some help from some Powershell Guru's you can build a script together that generates a report. 

Overview

The script can be run from any directory and will drop a CSV named after the group you queried in the same directory as the script. 

The user is prompted with a menu that has three options.

  • Option 1 List WSUS Groups found on your WSUS server.
  • Option 2 Pulls all the computers and their update information from the group specified. (Ex. Servers)
  • The script will query your WSUS server directly to pull the latest information. (Dependent on your WSUS server and clients communicating properly for accurate information)
  • Option 3 Quit

Examples:

Code:

001.####################################################################

002.#  #

003.# Author: Christopher Cardenas  #

004.# Creation Date: 9/5/2015  #

005.# Last Modified: 06/23/2015  #

006.#  #

007.#  *Modified for release to TechNet Wiki Articles.  #

008.#  #

009.#  #

010.#  #

011.#  #

012.####################################################################

013.#######################################################################################################################################

014.# The following section was taken from http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/09/23/a-self-elevating-#powershell-script.aspx

015.# This section is used to ensure we are running the script "As Administrator" so we don't get access denied errors when we #run

016.# commands that require administrator permissions.

017.# Get the ID and security principal of the current user account

018.$myWindowsID=[System.Security.Principal.WindowsIdentity]::GetCurrent()

019.$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)

020.# Get the security principal for the Administrator role

021.$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator

022.# Check to see if we are currently running "as Administrator"

023.if ($myWindowsPrincipal.IsInRole($adminRole))

024.{

025. # We are running "as Administrator" - so change the title and background color to indicate this

026. $Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + "(Elevated)"

027. $Host.UI.RawUI.BackgroundColor = "DarkBlue"

028. clear-host

029.}

030.else

031.{

032. # We are not running "as Administrator" - so relaunch as administrator

033. # Create a new process object that starts PowerShell

034. $newProcess = new-object System.Diagnostics.ProcessStartInfo "PowerShell";

035. # Specify the current script path and name as a parameter

036. $newProcess.Arguments = $myInvocation.MyCommand.Definition + " -Step $Step";

037. # Indicate that the process should be elevated

038. $newProcess.Verb = "runas";

039. # Start the new process

040. [System.Diagnostics.Process]::Start($newProcess);

041. # Exit from the current, unelevated, process

042. exit

043.}

044.#######################################################################################################################################

045.$MyDir = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)

046.Set-Location $MyDir

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

048.$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer('GSSCCM2',$False,8530) #Change to SCCM server name

049. 

050.$computerscope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope

051.$updatescope = New-Object Microsoft.UpdateServices.Administration.UpdateScope

052.#Exclude the following states from update list

053.$updatescope.ExcludedInstallationStates = 'NotApplicable','Unknown','Installed'

054. 

055.#Create Forward and Reverse lookup

056.$groups = @{}

057.$dataHolder = @()

058.$wsus.GetComputerTargetGroups() | ForEach {$groups[$_.Name]=$_.id;$groups[$_.ID]=$_.name}

059. 

060.Do {

061.$keepRunning = $true

062.Write-Host "

063.----------MENU----------------------------

064.1 = List WSUS Groups

065.2 = Pull Updates For Entire WSUS Group

066.3 = Quit

067.------------------------------------------"

068.$choice1 = read-host -prompt "Select number & press enter:"

069. 

070.Switch ($choice1) {

071."1" {$wsus.GetComputerTargetGroups().Name}

072."2" {

073. $DataHolder = @()

074. $computerscope4 = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope

075. $updatescope4 = New-Object Microsoft.UpdateServices.Administration.UpdateScope

076. #Only list updates that are needed

077. $updatescope4.ExcludedInstallationStates = 'NotApplicable','Unknown','Installed'

078. #Only list updates that are approved

079. $updatescope4.ApprovedStates = [Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved;

080.  

081. $TargetGroup = Read-Host -prompt "Please enter a group name:"

082. 

083. $pcgroup = @($wsus.GetComputerTargets($computerscope4) | Where {$_.ComputerTargetGroupIds -eq $groups[$TargetGroup]}) | Select -expand Id

084. 

085. $pcinfo = $wsus.GetSummariesPerComputerTarget($updatescope4,$computerscope4) | Where {$pcgroup -Contains $_.ComputerTargetID} | ForEach {

086. $computerscope2 = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope

087. $computerscope2.NameIncludes = $wsus.GetComputerTarget(([guid]$_.ComputerTargetId)).FullDomainName

088.  

089. $pcupSum = ($wsus.GetSummariesPerUpdate($updatescope4,$computerscope2) | ForEach-Object {($wsus.GetUpdate($_.UpdateId).Title)})

090.  

091.  

092. $data = New-Object PSObject -Property @{

093. "Client" = $wsus.GetComputerTarget(([guid]$_.ComputerTargetId)).FullDomainName

094. "IP Address" = $wsus.GetComputerTarget(([guid]$_.ComputerTargetId)).IPAddress

095. "Group" = $wsus.GetComputerTarget(([guid]$_.ComputerTargetId)).RequestedTargetGroupName

096. "Updates" = ($_.NotInstalledCount + $_.DownloadedCount)

097. "Failed" = $_.FailedCount

098. "LastSync" = $wsus.GetComputerTarget(([guid]$_.ComputerTargetId)).LastReportedStatusTime

099. "Titles" = $pcupSum | Out-String

100. }

101.  

102. $DataHolder += $data | Select Client,"IP Address",Group,Updates,Failed,LastSync,Titles | Where {$_.Updates -ne "0"}

103. 

104. }

105. #Display table in PS Window. Commented out due to being too large of a table to display properly.

106. #$DataHolder | Select Client,"IP Address",Group,Updates,Failed,LastSync,Titles| Sort LastSync -Descending |

107. #Format-Table -Wrap -Autosize @{L="Last Sync with WSUS";E={$_.LastSync};align='left'},@{L="Host Name";E={$_.Client};align='center'},"IP Address",@{L="WSUS Group";E={$_.Group};align='center'},@{L="# Needed";E={$_.Updates};align='center'},@{L="Failed";E={$_.Failed};align='center'},@{L="Updates Titles";E={$_.Titles};align='left'}

108.  

109. #Create CSV

110. $DataHolder | Select LastSync,Client,"IP Address",Group,Updates,Failed,Titles | Sort LastSync -Descending | Export-Csv "$TargetGroup.csv" -NoTypeInformation

111. #Display GridView Popup

112. $DataHolder | Select LastSync,Client,"IP Address",Group,Updates,Failed,Titles | Sort LastSync -Descending | Out-GridView -Title $TargetGroup

113. 

114. $DataHolder= @()

115. $pcupSum = ""

116. }

117."3" {$keepRunning = $False

118. exit 

119. }

120.}

121.} while ($keepRunning -eq $true)

Requirements:

  • Powershell 2.0 or higher
  • WSUS Server