WSUS Management

With Windows Server Update Services 3.0 Microsoft got rid of the web management interface to which we were so accustomed. With the objective of providing the update status of our managed computers all the way to our customer service representatives, I have developed a web and email interface for WSUS that provides real-time views of customer equipment from our WSUS server.

WSUS Data Export

Thankfully Microsoft has provided us with an easy to use API for extracting the information that we want from the WSUS server. Below is a simple sample PowerShell script for checking the status of all computers under a specific group.

$search = read-host "Enter a group to export"
new-alias  Out-Clipboard $env:SystemRoot\system32\clip.exe

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()
$groups = $wsus.GetComputerTargetGroups()
$i = 0
foreach ($group in $groups) {
    if($group.Name -like "*$search*") {
        $i++
        write-host "Full Domain Name`t Last Reported Status Time"
        $computers = $wsus.GetComputerTargetGroup($group.ID).GetComputerTargets() | ForEach-Object {
            write-host $_.FullDomainName "`t" $_.LastReportedStatusTime
            $output += $_.FullDomainName + "`t" + $_.LastReportedStatusTime + "`r`n"
        }
    } 
}
if ($i -eq 0) {
    write-host "Company '$search' not found."
    $output += "Company '$search' not found."
}
$output | Out-Clipboard
write-host "Your results have been stored in the clipboard for pasting." -BackgroundColor Red -ForegroundColor White

I really enjoy using PowerShell, so I wrote a script for exporting the status of all clients that are members of the Servers group. The report identifies when the server last checked in, what the result of the last sync was, along with the status for all updates related to the system. This information is exported to an XML file, whose structure is below.

XML Format

<?xml version=`"1.0`"?>
<servers>
<server>
	<servername><![CDATA["<FullDomainName>"]]></servername>
	<serverlastsynctime><![CDATA["<LastSyncTime>"]]></serverlastsynctime>
	<serverlastsyncresult><![CDATA["<LastSyncResult>"]]></serverlastsyncresult>
	<groupname><![CDATA["<groupname>"]]></groupname>
	<patch>
		<patchtitle><![CDATA["<Title>"]]></patchtitle>
		<updateapprovalaction><![CDATA["<UpdateApprovalAction>"]]></updateapprovalaction>
		<updateinstallationstate><![CDATA["<UpdateInstallationState>"]]></updateinstallationstate>
	</patch>
</server>
</servers>

Once the XML file has been generated, it is uploaded to the reporting server. The file is then parsed using the handy simplexml_load_file. The data is then entered into a MySQL database.

Database Schema

+-------------------------+--------------+------+-----+---------+----------------+
| Field                   | Type         | Null | Key | Default | Extra          |
+-------------------------+--------------+------+-----+---------+----------------+
| updateid                | int(30)      | NO   | PRI | NULL    | auto_increment |
| updateserverid          | int(9)       | NO   |     | NULL    |                |
| updatetitle             | varchar(256) | NO   |     | NULL    |                |
| updateapprovalaction    | varchar(25)  | NO   |     | NULL    |                |
| updateinstallationstate | varchar(25)  | NO   |     | NULL    |                |
+-------------------------+--------------+------+-----+---------+----------------+

Report

Lastly, the data is extracted from the database and presented in a friendly view (below). Delinquent updates are updates that have been approved but are not yet installed. This could be because there is a restart pending, the installation failed, or it has downloaded but not yet installed. This report is also emailed daily.