While we know that the WSUS API can be used to perform a multitude of WSUS tasks from approving patches, removing clients to creating automatic approval rules. Diving deeper into the API reveals that we can also find out the name of the server (if using a remote SQL database server) that the SUSDB database is residing on. Beyond that, we can actually perform queries to the database (using TSQL) or perform tasks against the database itself.

Lets get started on this! First we need to load up the assemblies for the WSUS API and make our connection to the WSUS server.

If running Windows Server 2008R2 and below and have the WSUS Administrator Console installed:

Add-Type -Path "$Env:ProgramFiles\Update Services\Api\Microsoft.UpdateServices.Administration.dll"    
$Wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer("dc1",$False,80)

If running Windows Server 2012 and above with the UpdateServices Module loaded:

$wsus = Get-WSUSServer -Name DC1 -Port 80

Viewing the methods of the $Wsus variable, the method that we are looking for is GetDatabaseConfiguration().

$wsus | Get-Member -Name GetDatabaseConfiguration

We can use this method to show the Database configuration interface (not actually connected to the database just yet) that shows the database name, SQL server as well as other properties that may or may not interest you. Lets check it out!

$db = $wsus.GetDatabaseConfiguration().CreateConnection()

One thing I noticed were the number of methods between the WSUS server on Server 2003 compared to Server 2012. There are differences in the number of methods in Server 2003 (53) and Server 2012 (46).

=> is what on Server 2003 but not on Windows 2012

<= is what is on Server 2012 but not on Server 2003

Seeing these differences makes me what to do some more investigations on other changes between the latest version of the WSUS API and the older version. But that is for another time

Fortunately, the Connect() method is still available to make the database connection, so lets go ahead and make the database connection. Note: I did some instances where an error would be thrown stating “The DBConnection object belongs to other thread.”. After a couple of attempts, I was able to make the connection.

$db.connect()
$db

With the database connection made, we can use the GetDataSet() method to make queries against the SUSDB database.

One thing to note is that you are constrained to just the SUSDB database. I admit  that I didn’t spend a lot of time trying to break free of the SUSDB connection to see if I can get to the Master and other databases, but from what I can tell, it doesn’t appear to be an easy thing, if possible at all.

Looking at the required parameters for this method, we can see that it takes a string value which is a query and a System.Data.CommandType.

The possible values in the System.Data.CommandType are:

Text
StoredProcedure
TableDirect

In this case, I only care about the Text command type.

$result = $db.GetDataSet('select * from INFORMATION_SCHEMA.TABLES',
[System.Data.CommandType]::Text)

This probably doesn’t really mean much to anyone, but digging into the Tables property will show more useful data, such as the actual results of the query.

What you are looking at is the default format view of the System.Data.DataTable object. There is actually more here behind the scenes that what you are initially seeing. If you try to export this to a CSV file, you will see that it doesn’t work out that well. Digging deeper, we can see more of what is hidden here.

So what do you do if all we need is what was queried? The answer to that is to use the Rows property in this object.

$result.tables.rows

Now you have something that can be used with Export-Csv to send to a CSV file.

This is just one of the cool things that you can do with the existing database connection to the SUSDB database for WSUS. I have another article lined up that will take this another step forward to show another useful thing that can be accomplished using this technique.