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.

Posted in powershell, WSUS | Tagged , , , , | 5 Comments

PoshChat Updated to 1.0

After about a year of doing nothing with my project, PoshChat, I decided it was time to push out an update that I have been working on here and there. This is one of those projects that is really fun to work on, but because it hasn’t been that high on my list of priorities, I really haven’t put forth the time and effort to do a lot with it. The updates here are few, but I feel that they were definitely needed.

Save Client-Side Transcripts

This update allows you to save the chat transcripts on the current chat session to a log file. It defaults to a naming context of date_time_chattranscript.txt.

image

Change Font on Windows

This update allows you to change the font size, style, etc… on the multiple windows of PoshChat (User list, Main message window and input window).

image

 

Please keep in mind that this is still a Beta product and there will be bugs. File any bug reports/feature requests on the codeplex Issues page here: https://poshchat.codeplex.com/workitem/list/basic

Thanks for checking this out and as always, give it a download and let me know what you think!

Posted in GUI, Modules, powershell | Tagged , , | 4 Comments

Determine Which Parameters Support Pipeline Input

A question that I had recently was how can a user determine which parameters have support for pipeline input in a cmdlet.

The answer lies within the help system of PowerShell. Using the –Parameter parameter of Get-Help, you can view information about whatever parameters you want to see.

Get-Help Get-Process -Parameter Computername

image

Here you can see that it does allow for pipeline input under the Computername parameter in Get-Process. What you may not know is that this is an object that you can explore.

Get-Help Get-Process -Parameter Computername | Get-Member

image

Note that the pipelineInput property is a string value, not a boolean even though it does return False and True (with caveats). This doesn’t make finding the parameters with pipeline support any more difficult as we can easily filter for the true values.

Get-Help Get-Process -Parameter * | Where {
    $_.pipelineInput -Like 'true*'
} | Select Name, PipelineInput

image

As you can see, we are able to see all of the parameters of Get-Process which has support for pipeline input. Now you can more easily determine which cmdlets have parameters that have pipeline support.

Posted in powershell | Tagged , , , | Leave a comment

New Things in Windows PowerShell 4.0

As most of you already know, PowerShell V4 is coming out soon. The preview version or beta or whatever you would like to call is already available via the Windows 2012 R2 download available to MSDN subscribers while the stand-alone downloads will most likely be available soon to check out.

Check out the following link to see the new features, bug fixes and other goodies that will be available with this new release!

http://technet.microsoft.com/library/hh857339.aspx

Some of the things that I found interesting:

  • Windows PowerShell Desired State Configuration (DSC) is a new management system in Windows PowerShell 4.0 that enables the deployment and management of configuration data for software services, and the environment in which these services run. For more information about DSC, see Get Started with Windows PowerShell Desired State Configuration.
  • The default execution policy setting on Windows Server 2012 R2 Preview is RemoteSigned. On Windows 8.1 Preview, there is no change in default setting.
  • The Get-Process cmdlet has a new switch parameter, IncludeUserName.
  • A new cmdlet, Get-FileHash, that gets information about file hashes, has been added.
  • In Windows PowerShell 4.0, if a module uses the DefaultCommandPrefix key in its manifest, or if the user imports a module with the Prefix parameter, the ExportedCommandsproperty of the module shows the commands in the module with the prefix. When you run the commands by using the module-qualified syntax, ModuleName\CommandName, the command names must include the prefix.
  • You can now throttle Foreach -Parallel activity statements by using the ThrottleLimit property.
  • Support has been added for a new PipelineVariable common parameter in the context of iterative pipelines, such as those used by System Center Orchestrator; that is, pipelines that run commands simply left-to-right, as opposed to interspersed running by using streaming.
  • #Requires statements now let users require Administrator access rights, if needed.
  • Get-Job now returns any completed scheduled jobs, even in new sessions.
  • The Import-Csv cmdlet now ignores blank lines.
  • A problem where Windows PowerShell ISE uses too much memory when you are running an Invoke-WebRequest command has been fixed.
  • Get-Module now displays module versions in a Version column.
  • Remove-Item –Recurse now removes items from subfolders as expected.

Plus much, much more!

Posted in News, powershell, V4 | Tagged , , , | Leave a comment

Scripting Games 2013: Event 6 Notes

We have finally hit the final event of the 2013 Scripting Games! The past 6 weeks have given us many amazing scripts and some that were in need of extra work. Regardless, for those of you who have finished all 6 scripts in your respective, I say Congratulations! You have hit the finish line sprinting hard to the end! Now you can sit back and know that you made it and have learned (hopefully) some great things along the way. Remember, not only have you learned some new techniques, but also the techniques that you have used have taught others how to write better scripts!

Now it is time to check out some of the good and bad things that I have noticed during Event 6.

Good

There were a lot of good things that I saw during this event, but some that stood out to me were the following:

[System.Management.Automation.Credential()]$LocalCred = [System.Management.Automation.PSCredential]::Empty

What is happening here is that a user could provide a PSCredential object into the $LocalCred parameter or just specify a username or even just specify the parameter like a switch. Quite a few options for just one parameter. The important thing here is the use of $LocalCred = [System.Management.Automation.PSCredential]::Empty which prevents a popup from occurring even if the parameter wasn’t used. Pretty important thing to keep in mind when adding a parameter for credentials.

 

Using –Confirm:$False to handle the ‘Are you sure?’ prompts was exactly what we were looking for in the requirement of avoiding these types of prompts and making the code as smooth as possible for someone to use.

 

Using Workflows was awesome as it allowed you to chain together different parts of the process to include adding information to the TrustedHosts, renaming a computer, performing a reboot during multiple parts of the process and ultimately adding the computer to the domain. Using workflows allows a more cleaner approach for areas like this when more than one operation needs to happen before another operation can be started. Good stuff!

 

Bad

The following is not a valid item for comment based help:

.REQUIREMENTS
     Requires PowerShell 3.0

Instead, the individual should have used the following valid way to show that PowerShell V3 is required:

#Requires -Version 3.0

 

Using ‘*’ in the TrustedHosts is a BAD idea! Very big security issue in that it means that the local system with this configuration will trust ANY remote system. Instead, it is better to only add the systems that are absolutely needed to avoid any unnecessary remote systems from accessing the local computer.

 

Lack of proper examples in the comment based help only hurts the overall self discovery of scripts and functions. Please make sure that you use at least 2-3 examples to cover the very basics of the function to something a little more advanced.

 

This one gave me a headache:

if ($WhatIf) {
    function WriteStatus($status) {Write-Host "WhatIf: $status"}
} else {
    function WriteStatus($status) {Write-Verbose $status}
}

This is really disappointing at this stage in the Scripting Games to see someone still trying to add their own –WhatIf/-Verbose/Help parameters in a function. PowerShell does all of this by itself, all you have to do it research a little to find out the proper way to accomplish this. Hint: take a look at [cmdletbinding(SupportsShouldProcess)] and also possibly using $pscmdlet.ShouldProcess() to make this really work for you.

Neutral

I added this for a couple of items that that showed a good practice or looked cool, but at the same time had some things that I didn’t quite like/agree with either. Hence, the neutral part.

I saw quite a few hard coded passwords in the scripts that left me wondering why. I get that the event called for passwords to be used and that it would be hard for others to reviewing to remember those passwords when running the code, so instead of it being in my bad list, it went the neutral. Because in the real world, you wouldn’t hard code a password in a script. I did like the person who attempted obfuscation of the password with RegEx:

(-join ("5040737377307264" -split "(?<=\G.{2})",19 |
ForEach-Object {if ($_) {[char][int]"0x$_"}}) |
ConvertTo-SecureString -AsPlainText -Force)

Still a security issue as the password can still be revealed, but at least it is a minute bit tougher for someone to figure out if they were just glancing at the code and didn’t try to run this snippet. Very cool!

Using New-Object –TypeName System.Management.Automation.PSCredential was something that I really didn’t think of doing, namely because I would whether prompt the user for credentials vs. using this and a hard coded password. Not exactly a bad thing in the submissions here, but I would be hesitant to use this in a real world script.

 

All in all, I have been very impressed with all of the submissions regardless of how good or not so good they were. I can honestly say that I learned some great tricks from the submissions. As I mentioned before, it is safe to say that everyone has learned something during these games, whether it has come from the community judging, the actual submissions themselves or from some of my fellow judges making their commentaries. Great job to everyone and I will see you all again for the next Scripting Games!

Posted in 2013 Scripting Games Judges Notes, powershell, Scripting Games 2013 | Tagged , , , | 1 Comment