WSUS report approvals for a group

Question

I'm trying to find a way to create a WSUS report of updates that have been approved for computer group A that have not been approved for one or more other groups. Alternatively, a tabular report that lists the approval status for each update and each group, so that it can be processed to extract what I need. There doesn't appear to be such a report in WSUS itself, or at least not one I can find, so a script to produce such a report would be most welcome.

Answer 1

This powershell script does exactly what your initial request was. Examine one computerGroup and find updates not approved for one or multiple other computer groups.

Note You will need to run this either on a WSUS server or a machine that has the WSUS Admin tools installed.

Configuration

Set $targetComputerGroup to the Computer Group you want to use as a baseline Set $CheckForMissing to the names of the group or groups you want to see if they have been approved for. Note: To do multiples just coma seperate ("Group1,Group2")

$serverName="localhost"
$targetComputerGroup="BaselineGroup"
$checkForMissing="MissingGroup1,MissingGroup2"

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
$wsus=[Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer($serverName,$false)
$computerGroup=$wsus.GetComputerTargetGroups()|ForEach-Object -Process {if ($_.Name -eq $targetComputerGroup) {$_}}
$UpdateScope=New-Object Microsoft.UpdateServices.Administration.UpdateScope
$UpdateScope.ApprovedStates="Any"
$updateScope.ApprovedComputerTargetGroups.Add($computerGroup)
$Approvals = $wsus.GetUpdateApprovals($UpdateScope)

#At this point we have all of the updates assigned to the $targetComputerGroup

$report= @()
write-host "Querying for all Updates approved for $targetComputerGroup"

foreach ($Approval in $approvals) {
   $record=""|Select-Object ComputerGroup,UpdateName, UpdateID
   $record.ComputerGroup=$wsus.GetComputerTargetGroup($Approval.ComputerTargetGroupID).Name
   $record.UpdateName=$wsus.GetUpdate($Approval.UpdateID).Title
   $record.UpdateID=$wsus.GetUpdate($Approval.UpdateID).ID.UpdateID
   $report +=$record
   }

#Now group the results by UpdateName
$GR=$report|group -Property UpdateName

$CheckForMissing=$CheckForMissing.Split(",")

 foreach ($entry in $gr) {
    $groups=@()
    foreach ($g in $entry.Group) {
        $groups += $g.ComputerGroup
        }
    foreach ($missing in $checkForMissing) {
        if ($groups -Contains $missing) {}
        else{
            New-Object PSObject -Property @{
            Name = $entry.Name
            UpdateID = $entry.Group[0].UpdateID
            GroupMissing = $missing
            }
        }
    }
}

When Completed you will have output an output like:

If instead of outputing to the screen you want to export the list to a CSV replace the bottom portion with the following code:

   $CheckForMissing=$CheckForMissing.Split(",")
   $CSVdata=@()
     foreach ($entry in $gr) {
        $groups=@()
        foreach ($g in $entry.Group) {
            $groups += $g.ComputerGroup
            }
        foreach ($missing in $checkForMissing) {
            if ($groups -Contains $missing) {}
            else{
                $CSVdata += New-Object PSObject -Property @{
                Name = $entry.Name
                UpdateID = $entry.Group[0].UpdateID
                GroupMissing = $missing
                }
            }
        }
    }
 $CSVdata|Export-Csv "FILENAME.CSV"

Answer 2

One can "simply" connect to the WSUS database and run queries against it:

1. Start SQL Management Studio with elevated privileges.
2. Connect to \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query using Windows Authentication.

These tables seem to be of interest regarding your question:

• tbUpdate
  Holds information about single updates

• tbTargetGroup
  Holds information about all computer groups

• tbDeployment
  Holds information about what updates have been approved for which computer groups

However, it seems beneficial to utilize the already existing view vUpdateApproval to retrieve most of the information you're after, as this view already translates the ActionID column from tbDeployment among other things.

The vUpdateApproval view, however, does not include any easily readable titles for updates. The titles are usually read from tbLocalizedProperty. To make it easier for us, there's another view: vUpdate.

I don't really have the proper data in our WSUS database to construct the proper query that would fit your first request (and I'm not confident enough to construct it blindly). So here's an approach for your secondary request. If I didn't mess up, it produces a list of all updates and the approval state for all groups.

SELECT
    aUpdate.UpdateId,
    aUpdate.DefaultTitle,
    aGroup.Name as GroupName,
    aApproval.Action as Action
FROM
    PUBLIC_VIEWS.vUpdate AS aUpdate INNER JOIN
    PUBLIC_VIEWS.vUpdateApproval AS aApproval ON aUpdate.UpdateId = aApproval.UpdateId LEFT JOIN
    dbo.tbTargetGroup as aGroup ON aGroup.TargetGroupID = aApproval.ComputerTargetGroupId
;

Which produces this output on our German SBS:

For our SBS with its 5 default groups, this produces 121558 result rows in ~26s. So, if you want to play around with the query, it may be advisable to change the first line to SELECT TOP 1000 while testing.

I also took the time to wrap it all up into a PowerShell script:

# Where to connect to
$dataSource        = "\\.\pipe\MSSQL`$MICROSOFT##SSEE\sql\query"
$connectionTimeout = 30

# The query we want to perform against the WSUS database
$query = @"
    SELECT TOP 10
        aUpdate.UpdateId,
        aUpdate.DefaultTitle,
        aGroup.Name as GroupName,
        aApproval.Action as Action
    FROM
        PUBLIC_VIEWS.vUpdate AS aUpdate INNER JOIN
        PUBLIC_VIEWS.vUpdateApproval AS aApproval ON aUpdate.UpdateId = aApproval.UpdateId LEFT JOIN
        dbo.tbTargetGroup as aGroup ON aGroup.TargetGroupID = aApproval.ComputerTargetGroupId
"@
$queryTimeout = 120

# Construct the connection string
$connectionString = "Data Source={0};Integrated Security=True;Connect Timeout={1};Database=SUSDB" -f $dataSource,$connectionTimeout

# Open the connection to the SQL server
$connection = New-Object System.Data.SqlClient.SQLConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Construct our SQL command
$sqlCommand = New-Object system.Data.SqlClient.SqlCommand( $query, $connection )
$sqlCommand.CommandTimeout = $queryTimeout

# Retrieve the data from the server
$dataSet     = New-Object system.Data.DataSet
$dataAdapter = New-Object system.Data.SqlClient.SqlDataAdapter( $sqlCommand )
[void]$dataAdapter.fill( $dataSet )

# Clean up
$connection.Close()

# Output result
$dataSet.Tables

Please note that this script include the SELECT TOP 10 limitation to avoid flooding your shell during testing.