PowerShell Custom Class for Querying a SQL Server

Here is a handy custom class I created for PowerShell 5+ that can query an SQL database. The class creates a SQL connection using the System.Data.SqlClient.SqlConnection class in .Net, and gives you full access to this object for information or to manipulate parameters. The class can also query the list of tables and views available in the database to help you prepare your SQL query.

Here’s a quick how-to:

Instantiate the Class

Create an instance of the object. You can create an empty instance like this:


$SQLQuery = [SQLQuery]::new()

You can then specify the SQL Server name and Database name:


$SQLQuery.SQLServer = "SQL-01\Inst_SCCM"
$SQLQuery.Database = "CM_ABC"

And then add a query:


$SQLQuery.Query = "Select top 5 * from v_R_System"

Or you can pass these parameters when you create the object, like:


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC")

Or..


$SQLQuery = [SQLQuery]::new("SQL-01\Inst_SCCM","CM_ABC","Select top 5 * from v_R_System")

Execute the Query

Now execute the query to return the results:


$SQLQuery.Execute()

The result will be displayed in the console window, or you can pipe to GridView for easier viewing:


$SQLQuery.Execute() | Out-GridView

Grid1

The query results will be stored to the object in the Result parameter so you can retrieve them later.


$SQLQuery.Result

The results are stored in a DataTable format with rows and columns which is an ideal format for SQL query results.

Load a Query from a File

You can load in a SQL query from a “.sql” file like so, passing the location of the file to the method:


$SQLQuery.LoadQueryFromFile("C:\Scripts\SQLScripts\OSD_info.sql")

Then execute the query as before.

Change the Connection String

By default, the connection string will be created for you, but you can add your own custom connection string using the ConnectionString parameter:


$SQLQuery.ConnectionString = "Server=SQL-01\inst_sccm;Database=CM_ABC;Integrated Security=SSPI;Connection Timeout=5"

Timeout Values

There are two timeout parameters which can be set:


$SQLQuery.ConnectionTimeout
$SQLQuery.CommandTimeout

The ConnectionTimeout parameter is the maximum time in seconds PowerShell will try to open a connection to the SQL server.

The CommandTimeout parameter is the maximum time in seconds PowerShell will wait for the SQL query to execute.

Get a List of Views or Tables in the Database

The following two methods can be used to retrieve a list of views or tables in the database:


$SQLQuery.ListViews()
$SQLQuery.ListTables()

The views or tables will be stored to the parameter of the same name in the object, so you can retrieve them again later.

You can filter the list to search for a particular view or table, or group of. On the command line you could do:


$SQLQuery.ListViews()
$SQLQuery.Views.Rows | where {$_.Name -match "v_Client"}

Or as a one-liner:


$SQLQuery.ListViews().Where({$_.Name -match "v_Client"})

You could also output to GridView for filtering:


$SQLQuery.ListViews() | Out-GridView

Grid2

Hide the Results

By default, the Execute(), ListViews() and ListTables() methods will return the results to the console after execution. You can turn this off by setting the DisplayResults parameter to $False. This scenario may be useful for scripting where you may not wish to display the results right away but simply have them available in a variable.


$SQLQuery.DisplayResults = $False

Access the SQL Objects

You can access the SQLConnection object, or the SQLCommand object to view or set parameters, or execute any of the methods contained in those objects. These objects are only available once the SQL query has been executed.

SQLConnection

SQLCommand

You can re-use the same SQLQuery object to run other queries; only the results for the most recent query will be stored in the object itself.

SQLQuery Custom Class

New Free Tool: ConfigMgr Remote Compliance

Remote Compliance

Today I released a new free tool for ConfigMgr administrators and support staff.

ConfigMgr Remote Compliance can be used to view, evaluate and report on System Center Configuration Manager Compliance Baselines on a remote computer. It provides similar functionality to the Configurations tab of the Configuration Manager Control Panel, but for remote computers. It is a useful troubleshooting tool for remotely viewing client compliance, evaluating baselines, viewing the evaluation report or opening DCM log files from the client, without needing to access the client computer directly.

ConfigMgr Remote Compliance can be downloaded from here.

Source code for this application is available on GitHub and code contributions are welcome.

Deploying Custom Microsoft Office Templates with System Center Configuration Manager

Some time ago a wrote a blog describing a way to deploy custom templates for Microsoft Office applications using SCCM Compliance Settings. Since then, I have re-written the solution into something much more manageable as the previous incarnation was not very clearly defined in how to update templates, and involved some considerable admin overhead. This updated solution is much improved and better manages the lifecycle of your custom templates, including updating, adding and retiring templates. Much of this process is now automated using PowerShell, and I have removed the need to manually specify all the template file names in the scripts so it is also much easier to set up and deploy.

It is relatively detailed, so instead of writing a blog I put this into a free PDF guide which you can download from here:

Deploying Custom Microsoft Office Templates with System Center Configuration Manager

pdfimg

Export / Backup Compliance Setting Scripts with PowerShell

In my SCCM environment I have a number of Compliance Settings that use custom scripts for discovery and remediation, and recently it dawned on me that a lot of time has been spent on these and it would be good to create a backup of those scripts. It would also be useful to be able to export the scripts so they could be edited and tested before being updated in the Configuration Item itself. So I put to together this PowerShell script which does just that!

The Configuration Item scripts are stored in an XML definition, and this can be read from the SCCM database directly and parsed with PowerShell, so that’s what this script does. It will load all the Configuration Items into a datatable from a SQL query, then go through each one looking for any settings that have scripts defined. These scripts will be exported in their native file format.

You could then edit those scripts, or add the export location to your file/folder backup for an extra level of protection for your hard work!

Here you can see an example of the output for my “Java Settings” Configuration item. A subdirectory is created for the current package version, then subdirectories under that for each Configuration setting, then the discovery and remediation scripts for that setting.

cis
Exported Configuration Item Scripts

Note that the script will only process Compliance Items with a CIType_ID of 3, which equates to the Operating System type you will see in the SCCM console for the Configuration Item, which is the type that may use a script as the discovery source.

Export-CMConfigurationItemScripts.ps1

PowerShell Tip! Create Background Jobs with a Custom Class

PowerShell 5 introduced the capability to create custom classes. Classes are a convenient way to create an object with your own custom definition, and can include properties and methods. There could be many potential use cases for such a custom class, but here’s one that is handy: background jobs. PowerShell comes with some cmdlets for running jobs in the background, such as Start-Job, or the -AsJob parameter, but if you’ve ever worked directly with runspaces in PowerShell, you know they perform better than jobs in a multi-threading scenario.

I put together a quick custom class that can be used in a similar way to the PowerShell job cmdlets, but by creating and managing its own PowerShell instance and runspace.

First off, here’s the class (PowerShell 5 or greater required)


class BackgroundJob
{
    # Properties
    hidden $PowerShell = [powershell]::Create()
    hidden $Handle = $null
    hidden $Runspace = $null
    $Result = $null
    $RunspaceID = $This.PowerShell.Runspace.ID
    $PSInstance = $This.PowerShell

    # Constructor (just code block)
    BackgroundJob ([scriptblock]$Code)
    {
        $This.PowerShell.AddScript($Code)
    }

    # Constructor (code block + arguments)
    BackgroundJob ([scriptblock]$Code,$Arguments)
    {
        $This.PowerShell.AddScript($Code)
        foreach ($Argument in $Arguments)
        {
            $This.PowerShell.AddArgument($Argument)
        }
    }

    # Constructor (code block + arguments + functions)
    BackgroundJob ([scriptblock]$Code,$Arguments,$Functions)
    {
        $InitialSessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()
        $Scope = [System.Management.Automation.ScopedItemOptions]::AllScope
        foreach ($Function in $Functions)
        {
            $FunctionName = $Function.Split('\')[1]
            $FunctionDefinition = Get-Content $Function -ErrorAction Stop
            $SessionStateFunction = New-Object -TypeName System.Management.Automation.Runspaces.SessionStateFunctionEntry -ArgumentList $FunctionName, $FunctionDefinition, $Scope, $null
            $InitialSessionState.Commands.Add($SessionStateFunction)
        }
        $This.Runspace = [runspacefactory]::CreateRunspace($InitialSessionState)
        $This.PowerShell.Runspace = $This.Runspace
        $This.Runspace.Open()
        $This.PowerShell.AddScript($Code)
        foreach ($Argument in $Arguments)
        {
            $This.PowerShell.AddArgument($Argument)
        }
    }

    # Start Method
    Start()
    {
        $THis.Handle = $This.PowerShell.BeginInvoke()
    }

    # Stop Method
    Stop()
    {
        $This.PowerShell.Stop()
    }

    # Receive Method
    [object]Receive()
    {
        $This.Result = $This.PowerShell.EndInvoke($This.Handle)
        return $This.Result
    }

    # Remove Method
    Remove()
    {
        $This.PowerShell.Dispose()
        If ($This.Runspace)
        {
            $This.Runspace.Dispose()
        }
    }

    # Get Status Method
    [object]GetStatus()
    {
        return $This.PowerShell.InvocationStateInfo
    }
}

To create an object with this class definition, we need to instantiate it using a constructor. There are a couple of constructor overloads to give us some different options when we create the object.

The simplest way it to just pass a block of code you want to run in the background job:


$Code = {
    Test-Connection 10.25.24.27 -Count 4
}

$Job = [BackgroundJob]::New($Code)

To start the job, simply call the Start() method:


$Job.Start()

To check the status of the job, we can call the GetStatus() method:


$Job.GetStatus()

pic
Check the job status

When the job has completed, we can receive any results outputted by the code:


$Job.Receive()

receive
Receive the job results

Then we close off the job. This is important to properly dispose of the PowerShell instance.


$Job.Remove()

If the job is running too long, we can close the pipeline to stop the job:


$Job.Stop()

The PowerShell instance that we created gets added to the object as the PSInstance property, so we can explore it further if we want. Here I am viewing the command that was run in the job:


$Job.PSInstance.Commands.Commands.CommandText

job
Browse the background PowerShell Instance

Another constructor allows us to pass arguments for the code when we create the object. Since the background job is running in a separate thread, it does not have access to the variables in our main thread, so we need to pass them. Remember to add a Param() block to your code when passing variables.


$ComputerName = "PC001"
$Code = {
    Param($ComputerName)
    Test-Connection $ComputerName -Count 4
}

$Job = [BackgroundJob]::New($Code,$ComputerName)

We can also pass multiple variables in an array like this, but in the Param() block remember to keep them in the same order you pass them.


$ComputerName = "PC001"
$Count = 10
$Code = {
    Param($ComputerName,$Count)
    Test-Connection $ComputerName -Count $Count
}

$Job = [BackgroundJob]::New($Code,@($ComputerName,$Count))

Finally, we can also pass custom functions to the background job:


Function Ping-Computers {
    Param([String[]]$ComputerName, $Count)
    $ComputerName | foreach {
        Test-Connection -ComputerName $_ -Count $Count
    }
}

$ComputerName = "PC001","PC002","PC003"
$Count = 10

$Code = {
    Param($ComputerName,$Count)
    Ping-Computers -ComputerName $ComputerName -Count $Count
}

$Job = [BackgroundJob]::New($Code,@($ComputerName,$Count),"Function:\Ping-Computers")

There are many possibilities for customising this class further, but using a custom class like this is a convenient way to spin up a job in the background, or to add some multi-threading capability to a script.

Here’s a simple example of multi-threading. First we add jobs to a hash table, then we start each job. Next we wait until each job is completed, and then return the output. The whole process takes much less time than running the commands synchronously (one after another).


# Create multiple jobs
$Jobs = @{
    Job1 = [BackgroundJob]::New({Test-Connection -ComputerName 10.20.17.129 -Count 5})
    Job2 = [BackgroundJob]::New({Test-Connection -ComputerName 10.1.16.86 -Count 5})
    Job3 = [BackgroundJob]::New({Test-Connection -ComputerName 10.21.17.216 -Count 5})
}

# Start each job
$Jobs.GetEnumerator() | foreach {
    $_.Value.Start()
    }

# Wait for the results
Do {}
Until (($Jobs.GetEnumerator() | foreach {$_.Value.GetStatus().State}) -notcontains "Running")

# Output the results
$Jobs.GetEnumerator() | foreach {$_.Value.Receive()}

multi
Multiple simultaneous jobs

The job result will be saved to the Result property after the Receive() method is called, so they are stored in the variable and can be retrieved later if needed.

You can also create a kind of class library, which you can import each time you run a PowerShell session, so that this custom class is always available to you, which I blogged about here.

Good job, what!

Automatically Set SQL MaxServerMemory on Cluster Failover with PowerShell

On a two-node Windows Failover Cluster, I have two SQL instances installed. Each instance runs on its own node in the cluster, so that it can make maximum use of the available memory on that server. However, when a failover occurs, it is necessary to reduce the maximum server memory setting for both instances, so that they can share the available memory on the one server. Rather than have to do that manually, however, I decided to automate the process using PowerShell and the Windows Task Scheduler, and here’s how.

Note: the script will work for two SQL instances in a cluster. For additional instances, the script will need to be updated accordingly.

  1. Save the PowerShell script (download from Technet Gallery) to each server in the cluster.
  2. Update the PowerShell script setting the required variables in the parameters section, such as the log file location, the SQL instance names, the SQL service names, the path to the SQL SMO dll, the maximum server memory limit you want to set, and the timeout period.
  3. Create a scheduled task on each server, running as an account that has the appropriate permissions on each instance. Add 2 triggers – one for each SQL instance – and use the event trigger. You can use event ID 101, for example, to identify when a SQL instance becomes active on that node. As the source, use the “SQLAgent$<instancename>”.

event

As the task action, use Start a program:

  • Program: Powershell.exe
  • Arguments: -ExecutionPolicy Bypass -WindowStyle Hidden -File “<PathToScript>\Set-SQLClusterMaximumMemory.ps1”

When a SQL instance fails over to the other node, the script will be triggered and will set the maximum server memory limit for both instances on that node.

maxmem

The script will also log the process:

log

Automation. Gotta love it 🙂

PowerShell DeepDive: WPF, Data Binding and INotifyPropertyChanged

PowerShell can be used to create some nice UI front-ends using the Windows Presentation Framework (WPF). You can create anything from a simple popup window to a full-blown, self-contained application. A major concept in WPF is that of data binding. Data binding allows you to create some separation between the design of the UI and data it operates with. Binding UI element attributes to a data source means that when you need to update data in the UI, you don’t need to edit the object property itself, rather you simply update the data source it is bound to. For that to work automatically, you need to bind to a collection type that implements the INotifyPropertyChanged interface. This interface provides a notification mechanism that can notify the bound element when a value in the datasource has changed. The UI can then automatically update to reflect the change.

Without using data binding you need to create some code to update the UI element and tell it what new data to display. In a single-threaded UI you can simply set the object property directly, and in a multi-threaded UI you will invoke the dispatcher on the object, so that the property can be safely updated without contention from another thread.

Maybe this all sound Greek (no offence if you speak Greek!), so lets do a couple of examples to illustrate.

Increment Me!

Here I created a simple function that will display a WPF UI window. Then I created an event handler so that when the button is clicked, the textbox will update. The textbox contains a number that will be incremented every time you click the button.


Add-Type -AssemblyName PresentationFramework

function Create-WPFWindow {
    Param($Hash)

    # Create a window object
    $Window = New-Object System.Windows.Window
    $Window.SizeToContent = [System.Windows.SizeToContent]::WidthAndHeight
    $Window.Title = "WPF Window"
    $window.WindowStartupLocation = [System.Windows.WindowStartupLocation]::CenterScreen
    $Window.ResizeMode = [System.Windows.ResizeMode]::NoResize

    # Create a textbox object
    $TextBox = New-Object System.Windows.Controls.TextBox
    $TextBox.Height = 85
    $TextBox.HorizontalContentAlignment = "Center"
    $TextBox.VerticalContentAlignment = "Center"
    $TextBox.FontSize = 30
    $Hash.TextBox = $TextBox

    # Create a button object
    $Button = New-Object System.Windows.Controls.Button
    $Button.Height = 85
    $Button.HorizontalContentAlignment = "Center"
    $Button.VerticalContentAlignment = "Center"
    $Button.FontSize = 30
    $Button.Content = "Increment Me!"
    $Hash.Button = $Button

    # Assemble the window
    $StackPanel = New-Object System.Windows.Controls.StackPanel
    $StackPanel.Margin = "5,5,5,5"
    $StackPanel.AddChild($TextBox)
    $StackPanel.AddChild($Button)
    $Window.AddChild($StackPanel)
    $Hash.Window = $Window
}

# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Set the value of the textbox
$Hash.TextBox.Text = [int]0

# Add an event for the button click
$Hash.Button.Add_Click{

    # Get the current value
    [int]$CurrentValue = $Hash.TextBox.Text

    # Increment the value
    $CurrentValue ++

    # Update the text property with the new value
    $Hash.TextBox.Text = $CurrentValue
} 

# Show the window
[void]$Hash.Window.Dispatcher.InvokeAsync{$Hash.Window.ShowDialog()}.Wait()

gif

Since the data type for the text property of the textbox is a string, I cannot increment the value directly, I must first cast it into an integer, then return it back to the textbox where it will again be stored as a string. Since the textbox text property is not bound to a data source, I must update the property directly. In this simple example that is not a problem, but it does mean that I cannot work independently of the data.

Creating a data binding

In the following example I have bound the textbox text property to a data source. Data binding in WPF can be done either in XAML or code, but here we are using code to illustrate how that can be done.

First, we will create a datasource object. This will be what we bind to. Since the datasource must notify the UI when the bound value has changed, we must use a collection that implements the INotifyPropertyChanged interface. You can actually create a custom class in C# to do that, as Ryan Ephgrave nicely describes on his blog. This is quite a flexible method, however it does mean you need to create all the properties used in your datasource up front in the class. A ‘native’ way to do that in .Net is to use an observable collection.

In this example, I am going to use an observable collection with a simple System.Object type, so I can add multiple kinds of object to the collection if I want.


# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Create a datacontext for the textbox and set it
$DataContext = New-Object System.Collections.ObjectModel.ObservableCollection[Object]
$Text = [int]0
$DataContext.Add($Text)
$hash.TextBox.DataContext = $DataContext

After creating the observable collection, I then add my initial value for the textbox – 0, and finally set the collection as the datacontext for the WPF window.

When using data binding, one can either declare the binding source directly, or set the datacontext for one of the parent elements. When you set the datacontext at the window level for example, this will be inherited by default by any child elements in the window, such as textboxes, panels etc. For more granular control, you can set the datacontext on the object itself rather than at the top-level. In either case, the bound object will use this datacontext.

Next, I create a binding object and pass to it which object in the datasource I want to bind to. In this case, I only have one item in the collection so I can simply reference it using it’s index number, which is [0]. If the collection contains multiple items, you need to keep a reference of which index your items are using.


# Create and set a binding on the textbox object
$Binding = New-Object System.Windows.Data.Binding -ArgumentList "[0]"

Another way to do that would be to set the path property of the binding object:


# Create and set a binding on the textbox object
$Binding = New-Object System.Windows.Data.Binding
$Binding.Path = "[0]"

If you prefer not to use a datacontext, you can also declare a binding source in the binding object instead:


# Create and set a binding on the textbox object
$Binding = New-Object System.Windows.Data.Binding
$Binding.Path = "[0]"
$Binding.Source = $DataContext

Now I need to set the binding mode, which is oneway in the case:


$Binding.Mode = [System.Windows.Data.BindingMode]::OneWay

And then finally I create the binding on the WPF element property. For the overload of the SetBinding() method, I need to provide the object I am binding to ($Hash.TextBox), then the property I am binding to (Text), then the binding object itself.


[void][System.Windows.Data.BindingOperations]::SetBinding($Hash.TextBox,[System.Windows.Controls.TextBox]::TextProperty, $Binding)

Now I can handle the button click event, but this time instead of updating the “text” property directly, I simply update the datasource it is bound to. In this instance, the datasource is an integer not a string, so I can increment it directly.


# Add an event for the button click
$Hash.Button.Add_Click{
    $DataContext[0] ++
} 

And finally now I run the window and the result is the same: the number is incremented with every click of the button. Only the datasource is being updated, but the text property of the textbox is bound to it and updates automatically when notified by the observable collection.

gif3

Here is the full code (using the same WPF Window function):


# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Create a datacontext for the textbox and set it
$DataContext = New-Object System.Collections.ObjectModel.ObservableCollection[Object]
$Text = [int]0
$DataContext.Add($Text)
$hash.TextBox.DataContext = $DataContext

# Create and set a binding on the textbox object
$Binding = New-Object System.Windows.Data.Binding # -ArgumentList "[0]"
$Binding.Path = "[0]"
$Binding.Mode = [System.Windows.Data.BindingMode]::OneWay
[void][System.Windows.Data.BindingOperations]::SetBinding($Hash.TextBox,[System.Windows.Controls.TextBox]::TextProperty, $Binding)

# Add an event for the button click
$Hash.Button.Add_Click{
    $DataContext[0] ++
} 

# Show the window
[void]$Hash.Window.Dispatcher.InvokeAsync{$Hash.Window.ShowDialog()}.Wait()

Multi-threading using the Dispatcher

Using data binding in this way can simplify the way you design your UI, and can also result in better UI performance, especially when using multiple threads because calling the dispatcher from another thread has a higher overhead. Using background threads is essential whenever you run any code that takes long enough that it will cause the UI to freeze and be unresponsive. Offloading that code to a background thread allows the UI to continue running happily in the main thread.

Does data binding work across threads? Yes it does, although when dealing with additional threads you need to consider the thread safety of the collections you are using as your datasource, as not all collections are inherently thread safe. There are some ways to achieve that though, but that’s for another post. Thread safety is important when you have multiple threads that may wish to access the same shared object at the same time.

For now, to illustrate data binding with a multi-threaded UI, lets first create the same WPF window but we will create and use a background thread to update the UI directly using the dispatcher. Each time the button is clicked, a new thread is created and the UI updated from that thread. The dispatcher allows us to queue work items for the window. This is a safe way to update the UI when using multiple threads as the dispatcher will manage each request in turn ensuring we do not suffer contention.

We are using the same WPF window function as previously. When the button is clicked, we read the current value of the texbox into a variable then increment it. We spin up a background thread and call the dispatcher on the window to update the UI. I’ve also included some code to cleanup completed threads using the Get-Runspace cmdlet (this is only available since PowerShell 5)


# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Set textbox value
$Hash.TextBox.Text = "0"

# Add an event for the button click
$Hash.Button.Add_Click{

    # Cleanup any completed runspaces
    $FinishedRS = (Get-Runspace | where {$_.RunspaceAvailability -ne "Busy"})
    If ($FinishedRS)
    {
        $FinishedRS.Dispose()
    }

    # Get current textbox value and increment
    [int]$CurrentValue = $Hash.TextBox.Text
    $CurrentValue ++

    # Create and invoke a background thread
    $ScriptBlock = {
        Param($Hash,$CurrentValue)
        $Hash.Window.Dispatcher.Invoke({
            $Hash.TextBox.Text = $CurrentValue
        })
    }
     $PowerShell = [PowerShell]::Create()
    [void]$PowerShell.AddScript($ScriptBlock)
    [void]$PowerShell.AddArgument($Hash)
    [void]$PowerShell.AddArgument($CurrentValue)
    $PowerShell.BeginInvoke()
} 

# Show the window
[void]$Hash.Window.Dispatcher.InvokeAsync{$Hash.Window.ShowDialog()}.Wait()

# Cleanup runspaces
(Get-Runspace | where {$_.RunspaceAvailability -ne "Busy"}).Dispose()

The result is that it works the same as before, but the response is somewhat slower. This is due to the additional overhead of creating a background thread and calling the dispatcher.

singlethreaddispatch

Multi-threading using data binding

Now lets do the same thing and use data binding. In this case, I only need to pass the datasource object to the background thread. When I update it in the background thread, the change is also reflected in the main UI thread.


# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Create a datacontext for the textbox and set it
$DataContext = New-Object System.Collections.ObjectModel.ObservableCollection[Object]
$Text = [int]0
$DataContext.Add($Text)
$hash.TextBox.DataContext = $DataContext

# Create and set a binding on the textbox object
$Binding = New-Object System.Windows.Data.Binding -ArgumentList "[0]"
$Binding.Mode = [System.Windows.Data.BindingMode]::OneWay
[void][System.Windows.Data.BindingOperations]::SetBinding($Hash.TextBox,[System.Windows.Controls.TextBox]::TextProperty, $Binding)

# Add an event for the button click
$Hash.Button.Add_Click{

    # Cleanup any completed runspaces
    $FinishedRS = (Get-Runspace | where {$_.RunspaceAvailability -ne "Busy"})
    If ($FinishedRS)
    {
        $FinishedRS.Dispose()
    }

    # Create and invoke a background thread
    $ScriptBlock = {
        Param($DataContext)
        $DataContext[0] ++
    }
     $PowerShell = [PowerShell]::Create()
    [void]$PowerShell.AddScript($ScriptBlock)
    [void]$PowerShell.AddArgument($DataContext)
    $PowerShell.BeginInvoke()
} 

# Show the window
[void]$Hash.Window.Dispatcher.InvokeAsync{$Hash.Window.ShowDialog()}.Wait()

# Cleanup runspaces
(Get-Runspace | where {$_.RunspaceAvailability -ne "Busy"}).Dispose()

The result this time is that even though there remains a small overhead in creating and managing a background thread, we get a much quicker response. Score!

singlethreaddb

Binding to the UI

Data binding can also be used to bind to items in the UI itself. In the following example, we have two textboxes. The one on the left is bound to a datasource that is incremented everytime the button is clicked. The one on the right is bound to the text property of the textbox on the left. The result is that both textboxes update incrementally, even though only one is bound to a datasource.

uibinding

This time, in the code, I have created a simple function to create a binding. When binding to a UI element, it is necessary to set both the source and path properties of the binding.


function Create-WPFWindow {
    Param($Hash)

    # Create a window object
    $Window = New-Object System.Windows.Window
    $Window.SizeToContent = [System.Windows.SizeToContent]::WidthAndHeight
    $Window.Title = "WPF Window"
    $window.WindowStartupLocation = [System.Windows.WindowStartupLocation]::CenterScreen
    $Window.ResizeMode = [System.Windows.ResizeMode]::NoResize

    # Create a button object
    $Button = New-Object System.Windows.Controls.Button
    $Button.Height = 85
    $Button.Width = [System.Double]::NaN # "auto" in XAML
    $Button.HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center
    $Button.VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center
    $Button.FontSize = 30
    $Button.Content = "Increment Me!"
    $Hash.Button = $Button

    # Create a textbox object
    $TextBox1 = New-Object System.Windows.Controls.TextBox
    $TextBox1.Name = "FirstTextBox"
    $TextBox1.Height = 85
    $TextBox1.HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center
    $TextBox1.VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center
    $TextBox1.FontSize = 30
    $TextBox1.BorderThickness = 0
    $Hash.TextBox1 = $TextBox1

    # Create a textbox object
    $TextBox2 = New-Object System.Windows.Controls.TextBox
    $TextBox2.Height = 85
    $TextBox2.HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center
    $TextBox2.VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center
    $TextBox2.FontSize = 30
    $TextBox2.BorderThickness = 0
    $Hash.TextBox2 = $TextBox2

    # Assemble the window
    $StackPanel = New-Object System.Windows.Controls.StackPanel
    $StackPanel.Orientation = [System.Windows.Controls.Orientation]::Horizontal
    $StackPanel.AddChild($TextBox1)
    $StackPanel.AddChild($TextBox2)

    $MainStackPanel = New-Object System.Windows.Controls.StackPanel
    $MainStackPanel.Margin = "5,5,5,5"
    $MainStackPanel.AddChild($StackPanel)
    $MainStackPanel.AddChild($Button)
    $Window.AddChild($MainStackPanel)
    $Hash.Window = $Window
}

Function Set-Binding {
    Param($Target,$Property,$Path,$Source)

    $Binding = New-Object System.Windows.Data.Binding
    $Binding.Path = $Path
    $Binding.Mode = [System.Windows.Data.BindingMode]::OneWay
    If ($Source)
    {
        $Binding.Source = $Source
    }
    [void][System.Windows.Data.BindingOperations]::SetBinding($Target,$Property,$Binding)

    # Another way to do it...
    #[void]$Target.SetBinding($Property,$Binding)
}

# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Create a datasource and set the initial value
$DataSource = New-Object System.Collections.ObjectModel.ObservableCollection[Object]
$DataSource.Add([int]0)
$DataSource.Add([int]0)
$Hash.Window.DataContext = $DataSource

# Bind the first text box to the data source
Set-Binding -Target $Hash.TextBox1 -Property $([System.Windows.Controls.TextBox]::TextProperty) -Path "[0]"

# Bind the second text box to the first textbox, text property
Set-Binding -Target $Hash.TextBox2 -Property $([System.Windows.Controls.TextBox]::TextProperty) -Source $Hash.TextBox1 -Path $([System.Windows.Controls.TextBox]::TextProperty)

# Event: Window Loaded
$Hash.Window.Add_Loaded{

    # Set the textbox widths to half the size of the button
    $Hash.TextBox1, $Hash.TextBox2 | foreach {
        $_.Width = $Hash.Button.ActualWidth / 2
    }

}

# Event: Button Clicked
$Hash.Button.Add_Click{

    # Increment the data source value
    $DataSource[0] ++

}

# Show the window
[void]$Hash.Window.Dispatcher.InvokeAsync{$Hash.Window.ShowDialog()}.Wait()

Learn your times tables!

Finally, my last example is a simple tool to help you learn your multiplication tables (you know you need reminding 😉 ). Here, the 3 text boxes with numbers are all bound to a datasource. When a button is clicked, the value is incremented or decremented in the datasource, and this is reflected in the UI by the binding. The result is then calculated based on the new values, and this is done by handling the TextChanged event on the first 2 text boxes. Again, the UI is never being updated directly, only the datasource is being changed, but the UI also updates because of the bindings.

multipier

The code for this is a bit long because I am creating the entire WPF window the ‘old-school’ way – in code, rather than in XAML. In practice, it is much better to create the UI definition in XAML, and then you will use a combination of XAML and PowerShell code to manage the UI.


function Create-WPFWindow {
    Param($Hash)

    # Create a window object
    $Window = New-Object System.Windows.Window
    $Window.SizeToContent = [System.Windows.SizeToContent]::WidthAndHeight
    $Window.Title = "Multiplication Tables"
    $window.WindowStartupLocation = [System.Windows.WindowStartupLocation]::CenterScreen
    $Window.ResizeMode = [System.Windows.ResizeMode]::NoResize

    # Create the first value textbox
    $TextBoxProperties = @{Height = 85; Width = 60; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center; FontSize = 30; BorderThickness = 0; IsReadOnly = $True}
    $TextBox1 = New-Object System.Windows.Controls.TextBox
    $TextBoxProperties.GetEnumerator() | foreach {
        $TextBox1.$($_.Name) = $_.Value
    }
    $Hash.TextBox1 = $TextBox1

    # Create the "x" textbox
    $TextBoxProperties = @{Height = 85; Width = 40; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center; FontSize = 30; BorderThickness = 0; Text = "x"; IsReadOnly = $True}
    $TextBox2 = New-Object System.Windows.Controls.TextBox
    $TextBoxProperties.GetEnumerator() | foreach {
        $TextBox2.$($_.Name) = $_.Value
    }
    $Hash.TextBox2 = $TextBox2

     # Create the second value textbox
    $TextBoxProperties = @{Height = 85; Width = 60; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center; FontSize = 30; BorderThickness = 0; IsReadOnly = $True }
    $TextBox3 = New-Object System.Windows.Controls.TextBox
    $TextBoxProperties.GetEnumerator() | foreach {
        $TextBox3.$($_.Name) = $_.Value
    }
    $Hash.TextBox3 = $TextBox3

    # Create the "=" textbox
    $TextBoxProperties = @{Height = 85; Width = 40; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center; FontSize = 30; BorderThickness = 0; Text = "="; IsReadOnly = $True }
    $TextBox4 = New-Object System.Windows.Controls.TextBox
    $TextBoxProperties.GetEnumerator() | foreach {
        $TextBox4.$($_.Name) = $_.Value
    }
    $Hash.TextBox4 = $TextBox4

    # Create the calculated value textbox
    $TextBoxProperties = @{Height = 85; Width = 80; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Center; FontSize = 30; BorderThickness = 0; IsReadOnly = $True}
    $TextBox5 = New-Object System.Windows.Controls.TextBox
    $TextBoxProperties.GetEnumerator() | foreach {
        $TextBox5.$($_.Name) = $_.Value
    }
    $Hash.TextBox5 = $TextBox5

    # Create the first "+" button
    $ButtonProperties = @{Height = 30; Width = 30; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Top; FontSize = 20; Content = "+"; Margin = "5,0,0,0"}
    $Button1 = New-Object System.Windows.Controls.Button
    $ButtonProperties.GetEnumerator() | foreach {
        $Button1.$($_.Name) = $_.Value
    }
    $Hash.Button1 = $Button1

    # Create the first "-" button
    $ButtonProperties = @{Height = 30; Width = 30; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Top; FontSize = 20; Content = "-"; Margin = "5,0,0,0"}
    $Button2 = New-Object System.Windows.Controls.Button
    $ButtonProperties.GetEnumerator() | foreach {
        $Button2.$($_.Name) = $_.Value
    }
    $Hash.Button2 = $Button2

    # Create the second "+" button
    $ButtonProperties = @{Height = 30; Width = 30; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Top; FontSize = 20; Content = "+"; Margin = "28,0,0,0"}
    $Button3 = New-Object System.Windows.Controls.Button
    $ButtonProperties.GetEnumerator() | foreach {
        $Button3.$($_.Name) = $_.Value
    }
    $Hash.Button3 = $Button3

    # Create the second "-" button
    $ButtonProperties = @{Height = 30; Width = 30; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Top; FontSize = 20; Content = "-"; Margin = "5,0,0,0"}
    $Button4 = New-Object System.Windows.Controls.Button
    $ButtonProperties.GetEnumerator() | foreach {
        $Button4.$($_.Name) = $_.Value
    }
    $Hash.Button4 = $Button4

     # Create the reset button
    $ButtonProperties = @{Height = 30; Width = 60; HorizontalContentAlignment = [System.Windows.HorizontalAlignment]::Center; VerticalContentAlignment = [System.Windows.VerticalAlignment]::Top; FontSize = 20; Content = "Reset"; Margin = "40,0,0,0"}
    $Button5 = New-Object System.Windows.Controls.Button
    $ButtonProperties.GetEnumerator() | foreach {
        $Button5.$($_.Name) = $_.Value
    }
    $Hash.Button5 = $Button5

    # Assemble the first stackpanel
    $StackPanel = New-Object System.Windows.Controls.StackPanel
    $StackPanel.Orientation = [System.Windows.Controls.Orientation]::Horizontal
    $TextBox1, $TextBox2, $TextBox3, $TextBox4, $TextBox5 | foreach {
        $StackPanel.AddChild($_)
    }

    # Assemble the second stackpanel
    $StackPanel2 = New-Object System.Windows.Controls.StackPanel
    $StackPanel2.Orientation = [System.Windows.Controls.Orientation]::Horizontal
    $Button1, $Button2, $Button3, $Button4, $Button5 | foreach {
        $StackPanel2.AddChild($_)
    }

    # Assemble the window
    $MainStackPanel = New-Object System.Windows.Controls.StackPanel
    $MainStackPanel.Margin = "5,5,5,5"
    $MainStackPanel.AddChild($StackPanel)
    $MainStackPanel.AddChild($StackPanel2)
    $Window.AddChild($MainStackPanel)
    $Hash.Window = $Window
}

Function Set-Binding {
    Param($Target,$Property,$Path,$Source)

    $Binding = New-Object System.Windows.Data.Binding
    $Binding.Path = $Path
    $Binding.Mode = [System.Windows.Data.BindingMode]::OneWay
    If ($Source)
    {
        $Binding.Source = $Source
    }
    [void][System.Windows.Data.BindingOperations]::SetBinding($Target,$Property,$Binding)

    # Another way to do it...
    #[void]$Target.SetBinding($Property,$Binding)
}

# Create a WPF window and add it to a Hash table
$Hash = @{}
Create-WPFWindow $Hash

# Create a datasource and set the initial values
$DataSource = New-Object System.Collections.ObjectModel.ObservableCollection[Object]
$DataSource.Add([int]1)
$DataSource.Add([int]1)
$DataSource.Add([int]1)
$Hash.Window.DataContext = $DataSource

# Bind the value text boxes to the data source
Set-Binding -Target $Hash.TextBox1 -Property $([System.Windows.Controls.TextBox]::TextProperty) -Path "[0]"
Set-Binding -Target $Hash.TextBox3 -Property $([System.Windows.Controls.TextBox]::TextProperty) -Path "[1]"
Set-Binding -Target $Hash.TextBox5 -Property $([System.Windows.Controls.TextBox]::TextProperty) -Path "[2]"

# Events: Button Clicks
$Hash.Button1.Add_Click{
    # Increment
    $DataSource[0] ++
}

$Hash.Button2.Add_Click{
    # Decrement
    $DataSource[0] --
}

$Hash.Button3.Add_Click{
    # Increment
    $DataSource[1] ++
}

$Hash.Button4.Add_Click{
    # Decrement
    $DataSource[1] --
}

$Hash.Button5.Add_Click{
    # Reset Values to 1
    $DataSource[0] = [int]1
    $DataSource[1] = [int]1
}

# Events: TextBox values changed
$Hash.TextBox1, $Hash.TextBox3, $hash.TextBox5 | foreach {
    $_.Add_TextChanged{
        # Calculate
        $DataSource[2] = $DataSource[0] * $DataSource[1]
    }
}

# Show the window
[void]$Hash.Window.Dispatcher.InvokeAsync{$Hash.Window.ShowDialog()}.Wait()

Conclusion

Data binding can get much more complex than this including two-way bindings, priority binding and multiple bindings, but hopefully these examples will whet your appetite to explore it further. When using data binding in your UI you are taking a step towards implementing the MVVM, a kind of best-practice concept for designing UI applications. Whether you really need to use data binding or not depends a lot on the type of UI you are creating and what it does, but certainly consider taking advantage of it to create a more efficient and logically designed PowerShell UI.

Have fun!

Decrypting Remote Desktop Connection Manager Passwords with PowerShell

Today I needed to find a password for a certain account I had used before (but had forgotten), and I remembered that I had stored the credentials in the Remote Desktop Connection Manager, Microsoft’s free RD tool. Of course, it crossed my mind whether these credentials could be decrypted, and it turns out they can, quite easily, with a little PowerShell.

The credentials are stored in encrypted form in the RDG file you create for your RDP connections. It’s just an XML file, so can be easily parsed with PowerShell. Using a handy trick blogged by , I decrypted all the credentials found in the RDG file.

The passwords can only be decrypted with the user profile that added them to the RDG file, however, so they are still reasonably secure. If the credentials were added by another account or on another system, you will get a “Failed to decrypt” error as seen below, which is the same error you get if you try to copy the RDG file and open it on another computer or with another user profile.

Needless to say, I found the password I needed!

capture


# Path to RDCMan.exe
$RDCMan = "C:\Program Files (x86)\Microsoft\Remote Desktop Connection Manager\RDCMan.exe"
# Path to RDG file
$RDGFile = "$env:USERPROFILE\Documents\RDPConnections.rdg"
$TempLocation = "C:\temp"

Copy-Item $RDCMan "$TempLocation\RDCMan.dll"
Import-Module "$TempLocation\RDCMan.dll"
$EncryptionSettings = New-Object -TypeName RdcMan.EncryptionSettings

$XML = New-Object -TypeName XML
$XML.Load($RDGFile)
$logonCredentials = Select-XML -Xml $XML -XPath '//logonCredentials'

$Credentials = New-Object System.Collections.Arraylist
$logonCredentials | foreach {
    [void]$Credentials.Add([pscustomobject]@{
    Username = $_.Node.userName
    Password = $(Try{[RdcMan.Encryption]::DecryptString($_.Node.password, $EncryptionSettings)}Catch{$_.Exception.InnerException.Message})
    Domain = $_.Node.domain
    })
    } | Sort Username

$Credentials | Sort Username

Explore WPF Controls with PowerShell

If you’ve ever tried creating a tool or an application with WPF, you know that the built-in controls contain many properties, methods and events, and finding the right one to use can be, well, fun!

As an aid to creating WPF applications, I created this simple tool which explores the various controls and exposes their properties, methods and events. It’s handy as a quick reference, or as a convenient way to get familiar with the various controls.

The tool is a PowerShell script, so simply download the script, right-click and ‘Run with PowerShell’.

More info here: https://smsagent.wordpress.com/tools/wpf-control-explorer/

wpf-controlexplorer