Use the WSUS API and PowerShell to query the SUSDB Database

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

image

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()

 

image

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

image

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 Smile

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

 

image

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.

image

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

Text
StoredProcedure
TableDirect

image

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

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

image

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.

image

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.

image

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

image

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.

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, WSUS and tagged , , , , . Bookmark the permalink.

5 Responses to Use the WSUS API and PowerShell to query the SUSDB Database

  1. v-juanm says:

    Great article, you save me for further investigation to know with SQL server was used in a big environment where I need to deal with customer unknown and undocumented data

  2. Javier Sss'ra says:

    Hi Boe,

    First of all, I want to thank you for this post also, since it’s been terribly useful. I know this is an old post but the following info might be of some use

    While working with WSUS on my infrastructure, I found out a solution to the “The DBConnection object belongs to other thread.”. It’s the same it happens when you try to change the culture of the current thread. If you execute “everything in the same line”, you’ll get instantaneously connected. This is what I’ve been using. I hope this is useful for someone – mind the line breaks please!

    Add-Type -Path “$Env:ProgramFiles\Update Services\Api\Microsoft.UpdateServices.Administration.dll”
    $Wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($(hostname),$False,8530)
    $db = $wsus.GetDatabaseConfiguration().CreateConnection();$db.connect()
    $db.DoSomeStuff(…)
    $db.Close()

    It even works when doing ps remoting.

    Best regards,
    Javier.

  3. Pingback: Configure not to require the Range Header? - ServerAB

  4. Pingback: Working with the WSUS API and the SUSDB Database using PowerShell | PowerShell.org

  5. Pingback: Using the WSUS API and PowerShell to Perform Maintenance on the SUSDB Database | Learn Powershell | Achieve More

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s