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://docs.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?
