Computer Tabular Status for Approved Updates report in WSUS in Powershell

Computer Tabular Status for Approved Updates report in WSUS in Powershell

When you have many WSUS-servers with central server in your organization, it might be very difficult of getting reports from computers.

Once I needed a script to show me, at which computers I don't have approved updates installed. There is a report in WSUS console that can give such info (it's called Computer Tabular Status for Approved Updates), but I was not able to go to every WSUS-server and run this report. Its a very weird task when you have 5-10-15 of WSUS-servers.

Solution

So I developed a Powershell script that will automate the task of getting reports from WSUS.

The best way to use it - is to run script on the central WSUS-server as it's getting all information about all computers from branch WSUS-server in organization. But of course you can run it on any server. In this case you will get report only about computers that connected to the specific server you're logged on.

Script

There are three variables, that you must take a look at. At line #2, where you set the domain name. Also, you must specify FQDN of your WSUS-server on line #7.

And - after scanning, script will export report to csv-file to the path which you can customize on line #4.

$datetime = Get-Date -Format "yyyy.MM.dd_HH-mm-ss";
$domain = "domain.local";
$serverName = "wsus10.domain.local";
$file_name = "wsus_audit_result_" + $domain + "_" + $datetime + ".csv";
$xl_filename = "c:\audit\" + $file_name;

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($serverName, $false)

$computerscope = New-Object Microsoft.UpdateServices.Administration.ComputerTargetScope
$computerscope.IncludeSubgroups = $true;
$computerscope.IncludeDownstreamComputerTargets = $true;
$computerscope.IncludedInstallationStates = [Microsoft.UpdateServices.Administration.UpdateInstallationStates] "Failed, NotInstalled, Downloaded";

$updates = $wsus.GetUpdates() | where {$_.IsApproved -eq $true};

$array = @{};
foreach ($update in $updates) {
	$temp = $update.GetUpdateInstallationInfoPerComputerTarget($ComputerScope) | ?{$_.UpdateApprovalAction -eq "Install"}
		
	if ($temp -ne $null)
    {
		foreach ($item in $temp)
        {
			$array.($wsus.GetComputerTarget([guid]$item.ComputerTargetId).FulldomainName)++;
		}
	}
}

$export_array = @();
$export_array += ,@("");$export_array += ,@("");

$i = 1;
foreach ($key in $array.Keys)
{
	if ($key.split(".")[1] -eq $domain.split(".")[0])
    {
		$export_array += ,@($key.Split(".")[0], $key.Split(".")[1], $array.$key);
	}
}

Write-Output "Saving report ...";
foreach($item1 in $export_array)  
{  
	$csv_string = "";
	foreach($item in $item1)
	{
		$csv_string = $csv_string + $item + ";";
	}
	Add-Content $xl_filename $csv_string;
}

 

Script running a little bit slowly, because poor optimization of protocol. But sure it depends on your networking.

And last thing - remember that it will not work on any PC, but only on those which have WSUS console installed.

script (en), wsus, powershell (en)

  • Hits: 36218
Add comment

Comments  
Issues
Hi,

I receive the following error, are you able to point me to a resolution?

Exception calling "GetUpdates" with "0" argument(s): "The operation has timed out"
At C:\Users\Userx\Desktop\WSUSS.ps1:16 char:1
+ $updates = $wsus.GetUpdates() | where {$_.IsApproved -eq $true};
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : WebException
RE: Issues
Hi!

The most possible reason for this issue is very large amount of updates objects in the WSUS-server.

I already had this issue, and to solve is, I had to decline a number of "old" update and clean database (it's from WSUS console).
Erro
Recebi este erro:
Exception calling "GetUpdateServer" with "2" argument(s): "Invalid URI: Invalid port specified."
At C:\Users\diones.ferreira\Documents\extracaowsus.ps1:8 char:1
+ $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpda ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : UriFormatException

You cannot call a method on a null-valued expression.
At C:\Users\diones.ferreira\Documents\extracaowsus.ps1:15 char:1
+ $updates = $wsus.GetUpdates() | where {$_.IsApproved -eq $true};
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
RE: Erro
Quoting Diones:
Recebi este erro:
Exception calling "GetUpdateServer" with "2" argument(s): "Invalid URI: Invalid port specified."
At C:\Users\diones.ferreira\Documents\extracaowsus.ps1:8 char:1
+ $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpda ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : UriFormatException

You cannot call a method on a null-valued expression.
At C:\Users\diones.ferreira\Documents\extracaowsus.ps1:15 char:1
+ $updates = $wsus.GetUpdates() | where {$_.IsApproved -eq $true};
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull


Hi!

Is your WSUS server use some specific port?
https://docs.microsoft.com/en-us/previous-versions/windows/desktop/aa349325(v=vs.85)

Try to update 8th string with something like:
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer('wsus10.domain.local',$False, 80)

where 80 - port number. Put yours there.
RE: Computer Tabular Status for Approved Updates report in WSUS in Powershell
why are comments are hidden?
RE: Computer Tabular Status for Approved Updates report in WSUS in Powershell
Im receiving the pollow error message and the report is empty.

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

The variable '$wsus' cannot be retrieved because it has not been set.
At line:15 char:12
+ $updates = $wsus.GetUpdates() | where {$_.IsApproved -eq $true};
+ ~~~~~
+ CategoryInfo : InvalidOperation: (wsus:String) [], RuntimeException
+ FullyQualifiedErrorId : VariableIsUndefined
RE: Computer Tabular Status for Approved Updates report in WSUS in Powershell
Im receiving the follow error message and the report is empty.


The variable '$updates' cannot be retrieved because it has not been set.
At line:18 char:21
+ foreach ($update in $updates) {
+ ~~~~~~~~
+ CategoryInfo : InvalidOperation: (updates:String) [], RuntimeException
+ FullyQualifiedErrorId : VariableIsUndefined
RE: Computer Tabular Status for Approved Updates report in WSUS in Powershell
May I know, How can I edit this file for "Computer Tabular Status" only, not on the approved status, thank you.

Related Articles