使用PoshWSUS获取服务器最新补丁日期并补充WSUS组名字段的方案

实现方案 #

核心逻辑说明

1. WSUS组名获取：PoshWSUS返回的客户端对象自带所属组查询方法，直接调用即可实现，单客户端多组的话会自动用逗号拼接。
2. last patch date（最近补丁安装日期）获取：不需要直接查询WSUS数据库，通过WSUS原生API即可拉取所有符合你原有筛选规则（仅安全更新、已批准安装、排除不适用）的补丁安装记录，按客户端ID分组后取最新的安装日期即可。

修改后完整脚本

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

# 新增：预取所有已安装补丁的最新日期，按客户端ID分组做哈希表
$LastPatchDateHash = @{}
Get-PSWSUSUpdate -UpdateScope $UpdateScope | ForEach-Object {
    $_.GetUpdateInstallationInfoPerComputerTarget() | Where-Object { $_.UpdateInstallationState -eq 'Installed' } | ForEach-Object {
        $compID = $_.ComputerTargetId.ToString()
        $installDate = $_.LastInstallationTime
        if (-not $LastPatchDateHash.ContainsKey($compID) -or $installDate -gt $LastPatchDateHash[$compID]) {
            $LastPatchDateHash[$compID] = $installDate
        }
    }
}

$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"
                      }
                  }},
                  # 新增：WSUS组名字段
                  @{name="WSUS Group Name";Expression={$ClientInfoHash[$_.ComputerTargetID].GetComputerTargetGroups() | Select-Object -ExpandProperty Name -Join ','}},
                  @{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}},
                  # 新增：last patch date字段，无安装记录时显示无记录
                  @{name="last patch date";Expression={if($LastPatchDateHash.ContainsKey($_.ComputerTargetID)){$LastPatchDateHash[$_.ComputerTargetID]}else{"无记录"}}},
                  @{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 ";" 

注意事项

如果WSUS客户端数量较多，首次拉取补丁安装记录耗时会稍长，你可以根据需要给Get-PSWSUSUpdate增加时间范围筛选，比如只查最近180天的补丁，降低性能消耗。

内容的提问来源于stack exchange，提问作者McVitas