Using PowerShell to get data for Microsoft Ignite

I was registered for Ignite yesterday (yipeeee!!), and decided to take a look at the session list. 

Navigation and search is a bit of a chore, so I set out to see if I could get the information I needed via PowerShell. If so, I was free to obtain whatever data wanted quickly.

Here’s what i came up with. After the script a couple of examples of post querying the data are given. Note that instead of querying the web services each time for data, I’ve just downloaded all the data, and query it locally. This isn’t really best practice, but (IMO) the low size of the dataset mitigates this to some extent.

Recommendations for improving or additions are more than welcome. 🙂 It will be posted to GitHub shortly.





Using PowerShell to make Azure Automation Graphical Runbooks – Part 2

The first article in this series introduced the Azure Automation Graphical SDK, targeting its use to produce a programmatic version of an existing graphical runbook, Get-DiceThrow. It then began covering the classes used by the SDK, referencing the portal elements and associated ones.

This second article continues the above, focusing on the largest and most challenging part of developing in this SDK, Activities.


An Activity refers to an action that is represented by an individual item on the graphic runook. Multiple Activity classes are available to be used. In the runbook we are developing, these are CommandActivity and WorkflowScriptActivity.

Workflow Script Activities

The WorkflowScriptActivity was briefly covered in the first article, when Workflows were outlined. As mentioned above, a workflow script activity is handled by the WorkflowScriptActivity class.

When an instance of this class is made, the following properties are available. Of these, only the first four are native to the class, with the other properties being inherited.

  • Begin
  • Process
  • End
  • CheckpointAfter
  • LoopExitCondition
  • EffectiveLoopExitCondition
  • LoopDelay
  • Name
  • EntityId
  • PositionX
  • PositionY
  • Description

Of note is that the Begin, Process and End properties match the options available in PowerShell advanced functions, providing the opportunity to perform pre-processing, record by record, and post-processing operations respectively.

In the script, Name, Process, PositionX, and PositionY are set. Name is visible on the graphical workbook diagram, as the text label on the activity. Process is the actual script being used. PositionX and PositionY dictate the location on the canvas of the activity.


Command Activities

Not mentioned yet are the command activities. A command activity is represented by the CommandActivity class.

On its own the CommandActivity class has a minimal number of properties that are available. From the screenshot below, only Name and Description are represented directly in this class.

4 - Command Activities

However, when a CommandActivity class is instantiated, other inhertied properties are available, making the following

  • CommandType
  • InvocationActivityType
  • ParameterSetName
  • Parameters
  • CustomParameters
  • CheckpointAfter
  • LoopExitCondition
  • EffectiveLoopExitCondition
  • LoopDelay
  • Name
  • EntityId
  • PositionX
  • PositionY
  • Description

Name, PositionX, and PositionY are implemented for the same use as that mentioned in the workflow script activity mentioned above. Also of note is EntityID, which is a unique identifier for the activity.

For the runbook we are developing CommandType, ParameterSetName, and Parameters to be set.

The first of these, CommandType, refers to one of the available Activity Types in the SDK.

Activity Types

Activity types detail the properties that make the action ‘tick’. That is, it describes what the activity does.

The CommandActivityType class has three properties that we set in the Runbook :

CommandName is the actual command. e.g. Write-Output

ModuleName refers to the module in which the command can be found. Write-Output for example uses Microsoft.PowerShell.Utility

5 - cmdlets

The next article in this series will cover the use of Parameters, and ParameterSets in an activity type, and the classes used by them.

Thanks for reading,


Using PowerShell to make Azure Automation Graphical Runbooks – Part 1


Last week saw the release of the Azure Automation Graphical SDK for creating graphical Runbooks programmatically. On its own, it is a library, represented by a single file,  Orchestrator.GraphRunbook.Model.dll. It contains the .NET components required to allow creation of a graphical runbook object, serialize it, then save it to a .graphrunbook file, suitable for importing into Azure Automation.

The SDK is by default installed to C:\Program Files (x86)\Microsoft Azure Automation Graphical Authoring SDK.

The examples given in the documentation that accompanies the download from Microsoft’s web page for this are in C#. This series will show how these same graphical runbooks can be generated in PowerShell.


The next few articles walk through how we can make a runbook, using an existing graphical runbook generated in the Azure Portal, Get-DiceThrow, as a point of reference. It then shows how an identical one can be programmatically made using PowerShell and the SDK.

The Existing Runbook

In the screenshot below is the graphical runbook we mentioned, Get-DiceThrow.

0 - GraphRunbook Design

Get-DiceThrow accepts as input two integer parameters, simulating the roll of two dice. It then identifies if one is greater than the other, or if they are both of the same value. Then, based on that, a message is displayed, describing the result.

When we’re looking at designing a graphical runbook via the SDK, it’s a good idea to first take a look at the structure of our existing one to get an insight of what it’s composed of. This helps later when development of the script starts.

To do this, the classes and properties that represent what we can see in Get-DiceThrow will be described.

Runbook Elements


A runbook, or more specifically a graphical runbook, is repesented by an instance of the the GraphRunbook class. This describes the runbook in its entirety, and consists of the properties Activities, Links, and Parameters. This last one refers to the runbook’s input an output parmeters (see below).

1 - GraphRunbook

Runbook Input and Output Parameters

Input and output parameters are defined by creating an one or more instances of a Parameter class and setting the Paramters property of the Runbook class.


The attributes for the name of the parameter, the parameters type, and whether it is mandatory or not are represented in the Parameter class by the properties of Name, Type, and Optional.

2 - Input and Output

Name, Type, and Optional


Workflow script activities are represented by the WorkflowScriptActivity class. From the image below (anti-clockwise from Label) , the associated properties are Name, Description, CheckPointAfter, and Process.

3 - WorkFlow Script


Links between activities use the aptly named class, Link. Links can either have conditions attached to them (restricting the use of the activity at the end of the link to situations where the condition’s expression evaluates to true), or be simple unconditional ones.

The settings in the image below (top to bottom) are referred to by the properties LinkType, Description,and Condition. Condition is another class, which contains the properties ConditionMode, to specify if a condition should be applied, and Expression for the conditional expression.

6 - Link

The next article will focus on the activities themselves, and the classes which inherit from them. This is probably the part that most of the development time will be spent on, since there is a substantial amount of configuration required.

Thanks for reading,



Using Drag and Drop in PowerShell GUIs

Drag and drop is a wonderful facility that most of us will use every day without even thinking about it. This article shows how it can be implemented in PowerShell GUI applications.

For this particular script, we’ll implement drag and drop functionality to allow us to drag a file from explorer to a textbox in we’ve created. When the drag and drop operation is complete, the textbox will then show the path to the file.

You can find a copy of the PowerShell Studio form and exported PowerShell code at the PowerShell.Amsterdam repository.

Here is what we our application will do once we have completed it:

To achieve our objective, we need to make use of two events, DragOver, and DragDrop.

DragOver occurs when the mouse is over the control on which we wish to ‘drop’ our object. We’ll typically just use this for changing our pointer to show a move or copy operation is in operation.

DragDrop occurs once the actual operation is finished. That is, the object has been dragged into the form and over the control, and the mouse button released.

To implement this in our application, carry out the following:

  • Create a form
  • Create a Textbox on the form
  • Set the following properties for the Textbox
    • Name : txtDragandDrop
    • Label : Contents dragged:
    • AllowDrop : true

1 - form design and properties

We now define the events that will be processed, and their handlers.

  • DragDrop : txtDragandDrop_DragDrop
  • DragOver : txtDragandDrop_DragOver

2 - form design and events

For the event handler code, use the following :

If you are developing this application in PowerShell Studio, you will need to first export the code, either to a standalone EXE, or .PS1 file.

  • Run the application from either of the above.
  • Drag a file of you choice from explorer to the textbox.

The textbox will be populated with the filepath of the file that has been dragged and dropped over it.

4 - running

Thanks for reading!


Reporting SQL Server Version Information

Pulled together some scripts to make a cmdlet which returns version information from one or more SQL servers. Remember to set the SQLServer parameter value correctly. It will be different depending on whether you are using a named instance. You can find a copy of the source code at my gitlab repository.

I’ve used Invoke-Command in the script so that the function can also be run remotely against other systems.

With this complete, we can use the cmdlet, and expect output similar to the format below.

SQL - VersionInformation

Asynchronously Save Images in the Clipboard to File

PowerShell v5.0 introduces us with two cmdlets, Get-Clipboard and Set-Clipboard which, just as their names suggest, allow us to obtain and set the contents of the clipboard. What’s not immediately apparent though is that these cmdlets can process more than just text in the clipboard.

In this blog, we’ll focus on using Get-Clipboard, and create a script which is used in combination with Register-EvengineEvent to save an image to file any time that one is detected in the clipboard during the current session. A repo containing the script is available at GitHub

Get-Clipboard supports the processing of several types of data that is in the clipboard, one of which is that of an image type. Examples of these would be print screens or copying an image created in Paint into the clipboard.

When an image is in the clipboard, and we use Get-Clipboard -Format Image, an object of Bitmap type (inherited from System.Drawing.Image) is returned. One of the methods this class provides is Save. Save has several overloads, one of which writes to a file and a variety of formats of your choice.

The script below requires use of the ISE, and uses Register-EngineEvent to register a scriptblock, which runs when a PowerShell.OnIdle event is raised. In the scriptblock, we check to see if there is any content in the clipboard of image type. If there is, we take the details of the current file open in ISE the editor, and use it to generate a unique filename. Then, the content of the clipboard is saved to this filename using the method mentioned above. Finally, we clear the clipboard to ensure that there we don’t end up in a continuous looping operation.

Thanks for reading!


Sharing Events Handlers in PowerShell GUIs

When you setup an event handler and its code, it is helpful to know it can be shared amongst multiple controls whilst still allowing access to the specific control which raised the event.

The first apparent benefit of this is that it instantly reduces the amount of code and code replication in your scripts, but it also gives an insight into what information is received by an event handler.

Although there are some exceptions, a typical event will provide two sets of information that the hander can process

  • The calling object (referenced in your code by use of the variable $this), also known as the Sender
  • The event arguments, passed in as a pipelined object $_

To illustrate how we can use this, we’re going to create a form with two buttons, which share the same event handler, which change the background color to white when the mouse hovers over it, and then back to normal when the mouse leaves the button’s area. You can find a copy of the PowerShell Studio form, and exported .ps1 file (for looking at the pure PowerShell code) at my GitHub repository

  • Create a form with two buttons
  • Select the first button
  • For the MouseHover event, call its handler buttonHover
  • For the MouseLeave event, call its handler buttonLeave
  • Repeat the button setting for the second button
Sharing Events - Force and Event Design

The first buttons configuration

Sharing Events - Force and Event Design Button 2

The second buttons configuration

  For our the event code, use the following:

Shared events in action :

Introducing SMAPLEX, an SMA PowerShell Drive Powered by Simplex

How I learned to stop worrying about writing an SMA PowerShell provider and love the drive

In addition to handling storage, distribution, queuing, and execution of runbooks, Service Management Automation (aka SMA) also performs the storage of resources (referred to in the Windows Azure Pack as ‘assets’). SMA resources consist of connections, credentials, variables (though purists will rightly tell you that they’re constants), schedules, runbooks, and jobs. Access to these elements can be carried out via the cmdlets provided with SMA, from its web services, or, if you’re using it, the Windows Azure Pack admin portal itself.

I thought I’d set myself the challenge of seeing if I could write a PowerShell Provider for SMA so that I could review all my resources from a single drive. This prove to be too much of a challenge for me. Fortunately I found out that MVP Jim Christopher (@beefarino on Twitter), has written a PowerShell module called Simplex.


Written for implementation with PowerShell in a module, Simplex deals with the parts that you would normally need to write a provider for. All that’s really required is to define the structure and PowerShell code in a domain specific language (DSL) style template for the provider you wish to create.

NOTE : Simplex creates a navigation provider. This means that whilst you are able to list and access content, writing new and updating content is not possible.

As mentioned, the contents of this file use a DSL type syntax, of which there are only three keywords.

Root is at the top of your provider code and defines what you will see if you are at the root of the providers drive. e.g. SMA:\. Nested under there, using braces to indicate the blocks can be the two other keyboards.

Folder indicates a container within our tree structure. The text immediately after this is the name given to the folder itself. The main thing to keep  in mind with folder keywords in a configuration script is that they represent a static item whose contents will not change.

Script is similar to a folder, but with the exception that when a PS Drive compatible command, such as Get-ChildItem, is used to list contents, the code within braces is dynamically run.

Configuring the SMA PowerShell Provider

Ensuring that you’re running on a system with the SMA cmdlets already installed, there’s four things we need to do in order to setup our SMA drive (or indeed any provider) :

  • Download the latest Simplex module
  • Install the module
  • Create our provider template
  • Register the PS Drive

Download and Install the Simplex Module

         smaplex - jims repo

  • Click Download Module Zip

         smaplex - jims appveyor build

  • Download Simplex
  • Create a folder called Simplex in one of your module paths
  • Extract the contents of the ZIP file to this folder

         smaplex - installing the module

Provider Configuration Document

Copy the content below into the clipboard, and save the content to a location on your hard disk.

(Note that there is no code for Connections, simply because it is not a feature I use at all within my Runbooks)

Configuration Document Structure

The document content for our SMA PS Drive above will generate a structure of the following type.

  • ROOTDRIVE: (We’ll be calling the drive SMA in this blog, so it will be SMA:)
  • Connections
    • Result of Connections scriptblock
  • Credentials
    • Result of Credentials scriptblock
  • Schedules
    • Result of Schedules scriptblock
  • Variables
    • Result of Variables scriptblock
  • Jobs
    • Result of Jobs scriptblock
  • Runbooks
    • Result of Runbooks scriptblock

A Quick Look at a Script section

Let’s take a look at the code within the Credentials scriptblock above.

The first thing to notice is that is now pure PowerShell code. The tasks it performs is to define the default parameters that will be returned, execute the command Get-SMACredential, an SMA cmdlet, and then output this object. You’ve probably noticed that we’re using a splat for it’s parameters. This is defined in our intialisation script, which is covered later in this article.

The rest of the script sections use a similar methodology to obtain the associated information from SMA. However, in the Runbook section, you’ll see that the runbook definition section is commented out. The reason for this is becaue it can result in substantially long waits before the information is returned. Because i am using about 50 runbooks, this is an option i’m currently not . It may be possible to speed up this operation by a direct SQL query or using multiple jobs. I’ve yet to test this though.

Initialising the PS Drive

Now that we’ve installed Simplex and have written our initial configuration document, all this is left to do is initialise the drive. After importing the module in a PowerShell session we use the New-PSDrive command with custom PSProvider and Root parameter settings. The value for Root is set to the path of the configuration document we earlier created.

In this same script we create a credential object, which is included along with the WebServiceEndpoint in our hashtable, used for paramter splatting in the configuration document.

SMA PSDrive in Action

Run the code above. All being well, you will shortly see confirmation that the drive has been initialised.


From this point, you can navigate and use navigational and filtering cmdlets, such as Get-ChildItem, and Where to customise your output.


  • Set-Location SMA:
  • Get-ChildItem
sma - get-childitem

Change to SMA drive and list contents


  • Set-Location -Path .\Runbook
  • Get-ChildItem | Sort RunbookName
sma - list runbooks

List Runbooks,  sorting on the RunBookName property


  • [datetime] $before = ‘1/5/2016’
  • Get-ChildItem | Where StartTime -LT $before
sma - get and filter jobs

Only list jobs which started before a certain date


Combining the use of SMA cmdlet’s with Jim’s module to make a PS Drive has made the retrieval of information from its database a relatively simple process, and makes it easy for extension at a later date. The data abstraction achieved by implementing a PS-Provider is also something for me personally that is desirable for writing other scripts that can use this for data retrieval.

Thanks for reading, and you can find the files for SmaPlex at my GitHub repo.


Automatic Parameter Splatting ISE Add-On

Here’s some code I put together which converts parameters in a command to a splatted variant of them. There’s definately room for improvement with the code (it can struggles with some sequences of parameters), so any contributions or ideas would be great for this. It’s posted in my GitHub area.

The function below parses the selected text and attempts to split them into separate chunks of parameters, which are processed and then concatenated into a single string. Once complete, the existing parameters are removed from the command and replaced with the splat, and the hash table placed on the line above.

Run the function to store it memory. Now, in order for us to be able to call the function either from a menu or keyboard option in the ISE, run the following code. :

With this done, all that’s required is to :

  • Select the parameters you wish splatted in the command
  •  Either
    • Click Add-ons in the menu bar
    • Click Get Splat
  •  Or
    • Press ALT + T
Select the parameters

Selection of the parameters and splatting option

  • View the results
The result of the autosplat

The results of the autosplat

Here’s a video of it in action:

Creating a Shortcut for a Button in a PowerShell GUI

While most of us are all still friends with the mouse in GUIs, keyboard shortcuts once learned are very effective ways of manipulating form controls quicker.

Serveral controls, such as buttons can use keyboard accelerators. This is simply done by prefixing the Text property of the control by an ampersand. By doing this, you can perform the same as the Click event for that control simply by holding down the ALT key on the keyboard, and then the appropriate letter.

For example, a button control with its text property set to &OK will respond to the keyboard combination ALT + O as if the mouse had clicked on it. In keeping with normal Windows functionality, you can identify the keyboard shortcut by holding down the ALT key, and the letter for the accelerator will be listed with an underline.

Here’s how you’d do this in PowerShell Studio :

Keyboard Shortcut

And in PowerShell code itself, this is represented as :