PoshWSUS - how to get last patch date per server?

Question

so I have this PoshWSUS module and a script which runws on WSUS and makes a CSV file. I am trying to update it so it also contains last patch date column but have trouble figuring it out

Import-Module -name poshwsus -force
Import-module -name SysDBApi -force
Import-module -name EncryptedCredential -force -ErrorAction Stop

#get credentials
$cred = Import-Clixml 'E:\WCO\transit.xml'
$SysDBcred = Import-Clixml 'E:\WCO\SysDBCred.xml'
$UseSSL = $True
$Port = 8530
$Date = get-date -Format dd-MM-yyyy
$Time = get-date -format HHmmss
$Filename = "$($env:COMPUTERNAME)_$($Date)_$($Time)"


[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null

Connect-PSWSUSServer -WsusServer $env:COMPUTERNAME -Port $Port
$ClientInfoHash = Get-PSWSUSClient | 
    Group-Object -Property Id -AsHashTable -AsString 
$UpdateScope = New-PSWSUSUpdateScope -UpdateApprovalActions Install -ExcludedInstallationStates NotApplicable 
$Classifications = Get-PSWSUSClassification | Where-Object {$_.Title -eq "Security Updates" }
$UpdateScope.Classifications.AddRange($Classifications)
$WsusData = Get-PSWSUSUpdateSummaryPerClient -UpdateScope $UpdateScope
$WsusData | 
    Select-Object @{name="Computer name";Expression={($ClientInfoHash[$_.ComputerTargetID].FullDomainName).split(".",2) | select -first 1}},
                  @{name="Domain name";Expression={
                      if ( ($ClientInfoHash[$_.ComputerTargetID].FullDomainName).split(".",2)[1])
                      {
                        ($ClientInfoHash[$_.ComputerTargetID].FullDomainName).split(".",2) | select -last 1
                      }
                      else
                      {
                        "WORKGROUP"
                      }
                  }},

                  @{name="Customer";Expression={(Get-SysDBHost -Credentials $SysDBcred -ComputerName ( ($ClientInfoHash[$_.ComputerTargetID].FullDomainName).split(".",2) | select -first 1)).Customer}},
                  @{name="Last reported status";Expression={$ClientInfoHash[$_.ComputerTargetID].LastReportedStatusTime}},
                  @{name="Required Updates";expression={$_.needed}}, 
                  @{name="Total Updates";expression={($_.Installed+$_.Needed+$_.PendingReboot+$_.Failed)}}, 
                  @{name="% Of Compliance";expression={"$([math]::Round((($_.Installed / ($_.Installed+$_.Needed+$_.PendingReboot+$_.Failed)) * 100),0))%"}}, 
                  @{name="Operating System";Expression={$ClientInfoHash[$_.ComputerTargetID].OSDescription}}|
    Export-Csv -Path "E:\Reports\$($Filename)_UpdatesStatus.csv" -NoTypeInformation -Encoding "utf8" -Delimiter ";"

On another WSUS server we use sqlcmd and SQL queries to get this data and the part which gets this info looks like this:

SELECT LIP.FullDomainName as 'Server Name',LIP.GroupName as 'Group Name',
LIP.CreationDate as 'Most recent patchinstall date', CASE WHEN NUP.NumUPatches is NULL THEN 0 ELSE NUP.NumUPatches END as 'Number of needed patches' FROM LATESTINSTPATCH LIP LEFT JOIN NumOfUNINSTPATCH NUP ON LIP.ComputerID=NUP.ComputerID
ORDER BY NUP.NumUPatches

According to https://learn.microsoft.com/en-us/previous-versions/windows/desktop/bb410149(v=vs.85) it is in PUBLIC_VIEWS.vUpdate, but how to work with this in PoshWSUS?

EDIT: I want to also add WSUSgroupname field, but that should be easier...

score 0 · Answer 1 · edited Jul 03 '21 at 00:10

Option 1 use Get-PSWSUSUpdate

The options for the command are listed here on Github

Run Get-PSWSUSUpdate with the option then pipe grep the output for date.

Option 2 native command

is simply gwmi win32_quickfixengineering |sort installedon -desc

Option 3 PS shell command

Get-HotFix |?{$_.InstalledOn -gt ((Get-Date).AddDays(-30))}

Now your script can use something like @{name="Dates";Expression=... one of the above options..

PoshWSUS - how to get last patch date per server?

1 Answers1