WSUS report approvals for a group

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.

-
Which version of windows is your WSUS running on? –  Nate Jan 9 '13 at 20:49
@Nate, it's WSUS 3.2.7600.226 running on a Server 2008 R2 Standard 64 bit machine. –  John Gardeniers Jan 9 '13 at 22:21
I think I have a solution for you, give me a few and I'll confirm –  Nate Jan 9 '13 at 22:50
add comment

2 Answers

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"

-
It works! Do you know how to stop the output from being truncated? BTW, I can't award the bounty for another 9 hours. –  John Gardeniers Jan 10 '13 at 1:05
The truncation is a function of how powershell formats for the screen. I updated the answer with an example of outputting to CSV file so you can have the complete values. –  Nate Jan 10 '13 at 13:57
Excellent, as CSV is very suitable for my needs. From here I can feed it to Perl, where at least I know what I'm doing. Much appreciated. –  John Gardeniers Jan 10 '13 at 20:25
add comment

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.

-
It's certainly something to investigate. Maybe there's a Perl module to interact with MS SQL Server. –  John Gardeniers Jan 9 '13 at 20:25
@JohnGardeniers: I added a PowerShell script that performs the query. Sadly, my Perl knowledge is even worse :) –  Oliver Salzburg Jan 9 '13 at 23:03
That's a good starting point but I'll have to find out how to stop the output being truncated. –  John Gardeniers Jan 9 '13 at 23:36
@JohnGardeniers: That's just how PowerShell displays the returned objects by default. You can run the script like myscript.ps1 | fl` to get a different (non-truncated) output. –  Oliver Salzburg Jan 10 '13 at 10:24
I've decided to reward you for your effort but you'll have to wait 24 hours. The system won't allow me to award a bounty immediately. –  John Gardeniers Jan 11 '13 at 10:20
show 1 more comment