PowerShell: WSUS Audit-Compliance Report and Automatic Cleanup

Its been long since I wrote anything on WSUS. As Windows world evolved, many of the troubleshooting steps might be bit out-dated now but WSUS functioning and basics still remain more or less same.

Today I wish to take another aspect of WSUS and talk about WSUS Reporting part and regular clean-up.

WSUS Reporting:

WSUS has a comprehensive reporting system built in and if Report Viewer installed over the server, one can variety of reports, but the same remains limited to a pre-set and also one server only. Let’s think of a scenario, where one might have more than dozens of WSUS servers catering thousands of machines and rather logging over each one or attaching them into one console, we want to have a consolidated report to see if patches getting installed well and also whether team is meeting compliance by install each single patch of (M-1) month.

Now what my PowerShell code does? It bring a report like below in csv format:

This would include how many patches are needed on which machines, when those machines last contacted their respective WSUS servers, what IP and OS do they have and which particular updates are pending on them and those pending updates were released when. Also it tells count of those updates which have been downloaded or pending download or pending reboot of machines to complete the installation.

I believe this pretty much covers what a system admin might need to get an overview of issues and to address them. Apart from this, what the script does is, it creates some more CSV files, one against each WSUS server mentioning that which particular updates were released between the period of 1st day of (M-2) month (if today is March, then we talking about January 1st) and Second Monday of (M-1) month means just one day before Patch Tuesday. Based on this data, one might check if any of the last month patches are missing on any of the servers. I have written a Excel Macro code around that as well but that still needs updating and make compatible for general usage so would post that at some later time.

Also notice, in code, there are two arrays of WSUS servers. Essential difference between the two are the connection lines. One connects over 8530 port while other connects on port 80, there might be SSL and non-SSL options as well, which can get handled by the second attribute passed depending on your scenario.

# Author : Nitish Kumar
# Performs an audit of WSUS
# Outputs the results to a text file.
# version 1.2
# 21 May 2017

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")

$firstdayofmonth = [datetime] ([string](get-date).AddMonths(-1).month + "/1/" + [string](get-date).year)
$DomainName = "."+ $env:USERDNSDOMAIN

# Create empty arrays to contain collected data.
$UpdateStatus = @()
$SummaryStatus = @()

# For WSUS servers catering servers
$WSUSServers = ("XYZ","ABC")

$a0 = ($WSUSServers | measure).count
$b0 = 0

$thisDir = Split-Path -Parent $MyInvocation.MyCommand.Path
$logFile = "WSUSAuditReports_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).txt"
Start-Transcript -Path $thisDir\$logFile

ForEach ($WS1 in $WSUSServers) {
write-host "Working on $WS1 ..."	-foregroundcolor Green
$b0 = $b0+1

try {

Try {
$WSUS = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WS1,$false,8530)
}
Catch {
$WSUS = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WS1,$false,80)
}

$ComputerScope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
$UpdateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope
$UpdateScope.ApprovedStates = [Microsoft.UpdateServices.Administration.ApprovedStates]::LatestRevisionApproved
$updatescope.IncludedInstallationStates = [Microsoft.UpdateServices.Administration.UpdateInstallationStates]::All

$ComputerTargetGroups = $WSUS.GetComputerTargetGroups() | Where {$_.Name -eq 'All Computers'}
$MemberOfGroup = $WSUS.getComputerTargetGroup($ComputerTargetGroups.Id).GetComputerTargets()

write-host "Connected and Fetching the data from $WS1 for all computers connecting to it..."
$Alldata = $WSUS.GetSummariesPerComputerTarget($updatescope, $computerscope)
$a = ($Alldata | measure).count
$b = 0

write-host "Data recieved from $WS1 for all computers connecting to it..."
Foreach ($Object in $Alldata) {
$b = $b+1
write-host "Getting data from number $b of all $a computers connecting to $WS1 ($b0 of $a0)..."	-foregroundcolor Yellow
Foreach ($object1 in $MemberOfGroup) {
If ($object.computertargetid -match $object1.id) {

$ComputerTargetToUpdate = $WSUS.GetComputerTargetByName($object1.FullDomainName)
$NeededUpdate = $ComputerTargetToUpdate.GetUpdateInstallationInfoPerUpdate() | where {($_.UpdateApprovalAction -eq "install") -and (($_.UpdateInstallationState -eq "Downloaded") -or ($_.UpdateInstallationState -eq "Notinstalled") -or ($_.UpdateInstallationState -eq "Failed"))	}

$FailedUpdateReport = @()
$NeededUpdateReport = @()
$NeededUpdateDateReport = @()

if ($NeededUpdate -ne $null) {
foreach ($Update in $NeededUpdate) {
$NeededUpdateReport += ($WSUS.GetUpdate([Guid]$Update.updateid)).KnowledgebaseArticles
$NeededUpdateDateReport += ($WSUS.GetUpdate([Guid]$Update.updateid)).ArrivalDate.ToString("dd/MM/yyyy ")
}
}

$object1 | select -ExpandProperty FullDomainName
$myObject1 = New-Object -TypeName PSObject
$myObject1 | add-member -type Noteproperty -Name Server -Value (($object1 | select -ExpandProperty FullDomainName) -replace $DomainName, "")
$myObject1 | add-member -type Noteproperty -Name NotInstalledCount -Value $object.NotInstalledCount
$myObject1 | add-member -type Noteproperty -Name NotApplicable -Value $object.NotApplicableCount
$myObject1 | add-member -type Noteproperty -Name DownloadedCount -Value $object.DownloadedCount
$myObject1 | add-member -type Noteproperty -Name InstalledCount -Value $object.InstalledCount
$myObject1 | add-member -type Noteproperty -Name InstalledPendingRebootCount -Value $object.InstalledPendingRebootCount
$myObject1 | add-member -type Noteproperty -Name FailedCount -Value $object.FailedCount
$myObject1 | add-member -type Noteproperty -Name NeededCount -Value ($NeededUpdate | measure).count
$myObject1 | add-member -type Noteproperty -Name Needed -Value $NeededUpdateReport
$myObject1 | add-member -type Noteproperty -Name LastSyncTime -Value $object1.LastSyncTime
$myObject1 | add-member -type Noteproperty -Name IPAddress -Value $object1.IPAddress
$myObject1 | add-member -type Noteproperty -Name OS -Value $object1.OSDescription
$myObject1 | add-member -type Noteproperty -Name NeededDate -Value $NeededUpdateDateReport
$SummaryStatus += $myObject1
}
}
}

$SummaryStatus | select-object server,NeededCount,LastSyncTime,InstalledPendingRebootCount,NotInstalledCount,DownloadedCount,InstalledCount,FailedCount,@{Name="KB Numbers"; Expression = {$_.Needed}},@{Name="Arrival Date"; Expression = {$_.NeededDate}},NotApplicable,IPAddress,OS|export-csv -notype $Env:Userprofile\desktop\AllServersStatus.csv

write-host "Connected with $WS1 and finding patches for last month schedule .."
# Find patches from 1st day of (M-2) month to 2nd Monday of (M-1) month
$updatescope.FromArrivalDate = [datetime](get-date).Addmonths(-2).AddDays(-((Get-date).day-1))

$updatescope.ToArrivalDate = [datetime](0..31 | % {$firstdayofmonth.adddays($_) } | ? {$_.dayofweek -like "Mon*"})[1]
#[datetime](0..31 | % {$firstdayofmonth.adddays($_) } | ? {$_.dayofweek -like "Mon*"})[1]

$file1 = "$env:userprofile\desktop\Currentmonthupdates_"+$WS1+".csv"
$WSUS.GetSummariesPerUpdate($updatescope,$computerscope) |select-object @{L='UpdateTitle';E={($WSUS.GetUpdate([guid]$_.UpdateId)).Title}},@{L='Arrival Date';E={($WSUS.GetUpdate([guid]$_.UpdateId)).ArrivalDate}},@{L='KB Article';E={($WSUS.GetUpdate([guid]$_.UpdateId)).KnowledgebaseArticles}},@{L='NeededCount';E={($_.DownloadedCount+$_.NotInstalledCount)}},DownloadedCount,NotApplicableCount,NotInstalledCount,InstalledCount,FailedCount | Export-csv -Notype $file1

}
catch [Exception] {
write-host $_.Exception.GetType().FullName -foregroundcolor Red
write-host $_.Exception.Message -foregroundcolor Red
continue;
}
}

Stop-Transcript
notepad $logFile

Once the report task is done, let’s talk about one smaller one. Cleaning of WSUS not relevant files and Computers. WSUS itself provides a one click option for this but again, it doesn’t cater if you want to handle multiple servers at once nor gives you a text report of what happened in one pass. So, here goes the another script to solve that part.

Now this script considers that we are in a SCOM monitored (or may be some other tool) environment means we would get a few tickets when we run the clean-up as clean-up restarts the update service on each WSUS twice and also may trigger high CPU etc. The script stops before each WSUS server and takes a manual input from you while informing you that its going to work on which server and that server should be put into maintenance mode in SCOM before proceeding further. It logs everything into the same directory where the script is. The path can be changed in case needed.

Also if one wants to make it completely automated then read-host lines can be removed and also at the end of code, we can add some lines to ensure that the status of entire activity gets passed over email to relevant personnel.

# Author : Nitish Kumar
# Performs a cleanup of WSUS.
# Outputs the results to a text file.
# version 1.0
# 07 March 2017

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")

# For all WSUS servers in Environment
$WSUSNon2k3 = ("abc001","xyz001")
$WSUS2k3 = ("cba001","zyx001")

# In case need to check for some individual server or few particular servers
#$WSUSNon2k3 = ("abc001")
#$WSUS2k3 = ("")

$AllWSUSCount = ($WSUSNon2k3 | measure).count + ($WSUS2k3 | measure).count
$WorkingonWSUS = 0

$thisDir = Split-Path -Parent $MyInvocation.MyCommand.Path
$logFile = "WSUSCleanupResults_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).txt"
Start-Transcript -Path $thisDir\$logFile

$Reply = "@"

ForEach ($WS1 in $WSUSNon2k3) {
$WorkingonWSUS = $WorkingonWSUS + 1
$WS1

Read-Host "Put $WS1 into maintenance mode. Once done,press Enter to continue ..." | Out-Null

write-host "Working on $WS1 ($WorkingonWSUS of $AllWSUSCount) ..."	-foregroundcolor Green
try {

$WSUS = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WS1,$false,8530)

write-host "Connected with $WS1 ($WorkingonWSUS of $AllWSUSCount)and proceeding for cleanup ..."	-foregroundcolor Yellow
write-host "Connected with $WS1 ($WorkingonWSUS of $AllWSUSCount)and proceeding for cleanup ..."

$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;

$cleanupScope.CleanupObsoleteComputers = $true
$cleanupScope.DeclineSupersededUpdates = $true
$cleanupScope.DeclineExpiredUpdates         = $true
$cleanupScope.CleanupObsoleteUpdates     = $true
$cleanupScope.CleanupUnneededContentFiles = $true
$cleanupScope.CompressUpdates                  = $true

$cleanupManager = $WSUS.GetCleanupManager();
$cleanupManager.PerformCleanup($cleanupScope);

write-host "Cleaning done for $WS1 ($WorkingonWSUS of $AllWSUSCount) ..."	-foregroundcolor Yellow
}
catch [Exception] {

write-host $_.Exception.GetType().FullName -foregroundcolor Red
write-host $_.Exception.Message -foregroundcolor Red

continue;
}
}

ForEach ($WS1 in $WSUS2k3) {
$WorkingonWSUS = $WorkingonWSUS + 1
$WS1

Read-Host "Put $WS1 into maintenance mode. Once done,press Enter to continue ..." | Out-Null

write-host "Working on $WS1 ($WorkingonWSUS of $AllWSUSCount) ..."	-foregroundcolor Green

try {

$WSUS = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($WS1,$false,80)

write-host "Connected with $WS1 ($WorkingonWSUS of $AllWSUSCount)and proceeding for cleanup ..."	-foregroundcolor Yellow

$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;

$cleanupScope.CleanupObsoleteComputers = $true
$cleanupScope.DeclineSupersededUpdates = $true
$cleanupScope.DeclineExpiredUpdates         = $true
$cleanupScope.CleanupObsoleteUpdates     = $true
$cleanupScope.CleanupUnneededContentFiles = $true
$cleanupScope.CompressUpdates                  = $true

$cleanupManager = $WSUS.GetCleanupManager();
$cleanupManager.PerformCleanup($cleanupScope);
write-host "Cleaning done for $WS1 ($WorkingonWSUS of $AllWSUSCount) ..."	-foregroundcolor Yellow

}
catch [Exception] {

write-host $_.Exception.GetType().FullName -foregroundcolor Red
write-host $_.Exception.Message -foregroundcolor Red

continue;
}
}
Stop-Transcript
notepad $logFile

So these were my two cents. These scripts are still a work-in-progress and would improve based on feedbacks.