PowerShell and Visio Part 2 – First Steps

Last time I talked about Visio and PowerShell and told you (in broad strokes) what I wanted to get done.  Now we’ll actually get started!

Starting Visio from PowerShell

To open the Visio application from PowerShell in order to start manipulating it, you need to use the appropriate COM class.  The code looks like this:

New-Object -ComObject Visio.Application

The New-Object cmdlet returns a reference to the new instance of Visio that’s running.  If you ran that line by itself, you’d see a bunch of properties of the new Visio.Application object and you’d see that Visio started, but without putting the object in a variable you’d be kind of stuck.  So…

$Visio=New-Object -ComObject Visio.Application

Now, we can use that reference to do fun stuff.

Opening a Visio Document

To open an existing document, use the Add method of the Documents property (a collection) of the $Visio object.  Just like the last time, this outputs an object that we’ll want to capture.  For example, opening the Visio diagram stored in c:\temp\SampleVisio.vsdx you’d do this:



Documents are made of pages:

$doc.Pages | select-object –property Name

This document only has one page, named (by default) ‘Page-1’.  We can either refer to it by name ($doc.Pages(‘Page-1’) or by number ($doc.Pages(1)).


Once we’ve got the page, we can see the shapes that are found on the page:

$page.Shapes | select-object –Property Name,Text


Visio objects are interesting to work with, and the properties you want might be hard to find, but they’re probably there.  For instance, to find the location (in the current unit of the document) of a shape you have to do this:


That should get you started looking at Visio with PowerShell. In the next post we’ll work on dropping shapes on the page and connecting them.

A reference that will help immensely is the VBA Object model reference for Visio found here.

Let me know if you have questions about this.


PowerShell and Visio Part 1 – Introduction

I’ve been playing around with Visio and PowerShell for quite a while now and the experience is something of a mixed bag.  My first thought was to use PowerShell to build integration diagrams, reading server names, domains, VLANs, and datacenter locations from a database, and adding color-coded connections to show the network connection requirements (e.g. these boxes talk to SQL on these other boxes on port 1433).  While much of the effort was successful, Visio doesn’t have the same idea of “arranging” items on the page.  I figured it would make things not overlap.  It seems to only care if things overlap if they are connected.

I ended up half-automating Visio.  I wrote some functions that did the database lookups and inserted objects (mostly one at a time, but sometimes in sequences), but used it more as an assistant.  I could tell it, for instance, add-server SERVER01-05 and have the script spot the dash, create a range, look up the names of the servers (Server01, for example) and figure out each server’s type (web server, SQL Server, etc.) and IP address.  It would then add an appropriate shape (web server) and label it with the name and IP.

I would share this code, but it’s really tied into the environment I work in and not terribly portable.  Instead, I thought I’d try to build a more general-purpose Visio module and share it as I write it.

My goal is to be able to write a diagram in a PowerShell-based domain specific language (DSL) so that building diagrams will be easier.  They might still need a bit of hand editing, but I’m OK with that.

But what about (insert reference to other PowerShell Visio module here)?  I know there are several well-developed modules out there, but for the most part they didn’t seem to focus on the things that I wanted to or were written in C# (which is fine, but harder to tinker with), and even more so, I wanted to learn how to get PowerShell and Visio to work together.

To give you an idea of what I’m thinking of, here’s some code I just got to work:

Import-Module myvisio -Force
New-VisioApplication                                                                         New-VisioDocument   | out-null                                                              
New-VisioContainer "My new container"  {
    New-VisioRectangle 1 1 5 5 
    New-VisioRectangle 5.5 5.5 6 6
    New-VisioContainer "Inner Container" {
        New-VisioRectangle 1 7 8 8
} | out-null

The output in Visio looks like this:

With some appropriate helper functions and some parameters to help with styling, I think this could work.

I’ll share some actual code in the next installment.

Let me know what you think about this.


Copying Properties to another Object

I had a thought today that it would be interesting to write a function to copy properties from one object to another. Specifically I was thinking about doing a join operation on two arrays and that it would be good to be able to say “copy these properties from the object on the right to the object on the left”. Since I couldn’t think immediately about how that function would look, I wrote it. Here’s the code I came up with:

function Copy-Property{

      $property | Foreach-Object {
      $inputObject | Add-Member -NotePropertyMembers $propHash @passthruHash

And here’s a screenshot showing it in use.

I haven’t “finished” the function because I’m not sure that I’m ever going to use it, but here are a few things to notice:

  • This is an advanced function.
  •  I didn’t enable -Whatif and -Confirm because Add-Member doesn’t
  • I’m using splatting to flow the -Passthru switch along to Add-Member
  • Add-Member allows a hashtable of property names and values, just like New-Object does. I learned that today!
  • I’ve allowed pipeline input, but you only get one object to copy from :-(
  • I could have called Add-Member for each property, but I like doing it this way better.  I might change my mind on that.

So, this was a fun exercise.  I don’t think that it will be useful in anything, but it was fun to write so I thought I’d share.

Having a blast in PowerShell!



P.S.  I know that there are several implementations of Join-Object out there.  I will probably end up using one of them.

PowerShell Code Smells: Boolean Parameters

This is a real quick one. In PowerShell, the “native” way to express a two-state variable is with Switch parameters.

For instance:

function Test-Thing{
  #Code goes here

When calling Test-Thing, you can either supply -Recurse or leave it off. It’s a flag.

This is PowerShell 101.

Programmers coming from other languages are used to Boolean variables. Sometimes, one slips through and makes its way into a parameter list.

It’s not incorrect to have a Boolean parameter. It works just fine, but when I see one it makes me wonder what other PowerShell idioms the writer is not aware of. To me, a Boolean parameter is a code smell.

I mention this because I spotted one in Invoke-SQLCmd this week. [And no, I’m not piling on Invoke-SQLCmd. A co-worker was having problems with Invoke-SQLCmd and asked me to take a look. This was one of the problems]

The -OutputSqlErrors parameter for Invoke-SQLCmd is a Boolean. That means if you want to see SQL errors, you need to pass -OutputSQLErrors $true, rather than just -OutputSQLErrors.

Interestingly enough, there are 8 switch parameters on this cmdlet. This is the only Boolean.

What do you think? Do you ever see parameters like this?

Let me know in the comments.


7 Ingredients of Professional PowerShell Code

If you’ve been using PowerShell for any amount of time you’ve probably written a lot of code. Here are some guidelines I’ve come up with for what I consider “Professional” code. I should note that I’m assuming some basic things like correct verb-noun naming, commented code, reasonable variable names, etc. Also, the code should work! Once you’ve got that going, try to make sure you’ve got these as well.

  1. Output objects
  2. This should go without saying, but it’s vitally important. Write objects to the output stream. Each object should be a self-contained a “record” of output. If you’re creating custom objects, consider including some of the parameter values that led to the object being output. If possible, It should contain enough information to let you know why it was output. For instance, if your parameters filter the output based on properties, including those properties will be very helpful in validating that the output is correct.

  3. Advanced functions (or script)
  4. Changing a function into an advanced function can be as simple as adding [CmdletBinding()] before the Param() statement. It does take a bit more if you need to support -WhatIf and -Confirm, but even then it’s not much effort. In return you get:

    • Common Parameter Support (Verbose, ErrorAction, etc.)
    • Parameter Name and Position Checking
    • Access to $PSCmdlet methods and properties
    • Ability to use pipeline input (in a nice way)

    If you need help getting started with writing advanced functions (or scripts), see about_Functions_CmdletBindingAttribute

  5. Comment-based help
  6. This one takes a bit more work, but is a key to getting other people to use your function or script effectively. It’s simple to write up a code snippet for ISE (or your editor of choice) to include the help elements you like. Most people will start by skipping to the examples, so always include multiple examples! Examples which show different use-cases (parameter sets, if you use them, for instance) are especially helpful so that users understand the full range of how your code can be used. Refer to get-help about_Comment_Based_Help to get you started.

  7. Pipeline input
  8. Pipeline input isn’t always necessary, but it really makes using a function easier. You’ve got to have an advanced function to do this, but you did that already, right? I’d much rather have this:

    Get-Thing | Stop-Thing

    Than this:

    Get-Thing | foreach-object {Stop-Thing $_}

    If you’ve used cmdlets that didn’t allow pipeline input you’ve undoubtedly written some code like this.

  9. Error Handling
  10. This is pretty basic. You need to use try/catch/finally in your code to deal with exceptions that you can predict. You should use Write-Error and Throw to emit errors and exceptions that arise. If you use Write-Error, supply a -TargetObject if possible, it makes the error record much more useful. While we’re talking about errors, Write-Warning, Write-Verbose, and Write-Debug should be used to help provide useful output

  11. Parameter Attributes for validation
  12. Here’s a good rule of thumb:

    Code that you don’t write is code that you don’t have to debug!

    If you use parameter attributes to validate argument values, you don’t have to write code to do it. Ensuring that arguments are valid will make users happier (because they don’t have unexpected results when they pass in bad values) and makes your code simpler because you don’t have to write a bunch of if/then statements to check values. Finally, and this benefit is not as obvious, error messages for parameter attribute-based validation will be localized, so users around the world can benefit even more from your code as they see validation messages in their own language..

  13. Publish it!
  14. If you’ve put this much time into polishing your code, you should take the extra step of sharing it with the community. This might take a bit of extra effort to make sure it doesn’t contain anything proprietary. Sharing on your blog (you do have one, right?), GitHub, Technet, PoshCode, or the PowerShell Gallery
    are all options, so there’s very little stopping you.

How does your code fare against this list? I know most of mine has some room for improvements.

What do you think of this list? Did I miss something important? Let me know in the comments!


Write-Error and -TargetObject

Sometimes I feel like I know a lot about PowerShell.  I’ve taught dozens of classes on PowerShell, written two books, and used it for 8 years now.

And then I stumble upon this post from Jeffery Snover in 2006.xI’m not even sure now why I was looking at it at the end of 2015.   The post is about -ErrorAction and -ErrorVariable.  I’m very comfortable with those two common parameters.  Nothing to see there.  But in scanning down the page, my eye stopped in the middle of a screenshot.


There in the “forced” output of the $Error variable I see something I hadn’t ever seen before. TargetObject.

One of the keys of non-terminating errors is that hypothetically you can address the errors that happen while allowing the rest of the “payload” to continue. For example, you stopped 50 processes but one failed. You wouldn’t want to give up when that process failed. You’d rather finish the list and then do something about that one process.  It’s pretty clear to see in the screenshot that the TargetObject in this case was “13”.  You can do something with that.

Non-terminating errors are a basic part of PowerShell, but I don’t remember hearing or reading about this ever before.

Sure enough, though, there’s a -TargetObject parameter in Write-Error:


And the example in the post showed that the parameter is used when Stop-Process has an error. Why hadn’t I ever seen it before?

Ah, I know. My standard example when explaining non-terminating errors looks like this:

gwmi Win32_OperatingSystem -computername NOSUCHCOMPUTER,localhost

The $Error object written by Get-WMIObject looks like this:

Unfortunately the TargetObject property is empty. Since Get-WMIObject implicitly loops through the ComputerNames, it would be super useful to know the name of a computer where the operation failed.

It will be fun to look through cmdlets to see which ones correctly use this parameter.

My new “best practice”:

Always populate -TargetObject when calling Write-Error

As always, let me know what you think in the comments.


P.S.  I’ve filed a bug for Get-WMIObject on uservoice here.  Please take a minute to upvote it.

Get off my lawn!

About five years ago I gave a presentation to a boys group about the rate of change in computer hardware.  I compared my first computer (a Commodore 64) and a laptop which I had just bought (a decent Asus laptop).  The comparison was humorous.  The almost thirty year old computer was several orders of magnitude inferior in almost every criterion.  The price of the laptop (about $500) was similar to what the C64 cost when it was first released.

A few months ago, I got a new Fire tablet from Amazon.  It is not a top-of-the-line device in terms of specs, but it does have a decent CPU, a reasonable amount of memory, expandable storage (sd card!), 2 cameras, and WIFI.  I’ve heard several people complain about the device because the dpi rating isn’t as good as the latest Samsung/Apple gizmo, and there are faster tablets around.  That’s fine, people complain.

But here’s the thing.  That tablet cost me $50.  When I picked up one for my wife on Black Friday, it was $35.  At those prices the devices are nearly disposable.  I’m kind of kidding, because I don’t throw away $50, but in terms of computer devices, $50 is just about the same as zero.

The specs aren’t as good as the best things around, but think about it this way.  A few years ago those specs would look pretty good, and the price is hard to beat. Thirty years from now the specs of the iPhone 5 won’t look so great.

The new Fire works great for me as an intro tablet, and the specs don’t bother me because I’m evaluating it on its own merits rather than comparing.  I’m finding all kinds of ways that it’s useful and adds value for me and at $50, it is a steal.

Just a quick rant.  I’m old and get off my lawn.

Let me know what you think in the comments.


My 2016 PowerShell Goals

Since I didn’t do a great job realizing my goals from last year, I thought putting a little more “effort” into defining them this year might make a difference.

  1. 100 Posts.  I know I didn’t even get close to 50 last year, but I think I might have just not set a “stretch” goal.  I plan to start doing some more “getting started” posts, perhaps update the “writing a host” series, and plan to spend more time writing PowerShell code which should spur my writing here.   100 is only two per week (with two weeks off, even) so this should be achievable.
  2. Virtualization Lab.  I had this on the list last year, but I wasn’t specific at all.  I did do some virtualization, but it was mostly one-off, and not scripted much at all.  Also, I used VirtualBox because it was so super easy.  This year I will build (with scripts) a Hyper-V virtual lab including (at least) a domain controller, a web server, a SQL Server, and a file server.  Not sure how much DSC will be involved, but that’s the next goal.
  3. Configure Servers with DSC.  I’ve played around with simple push configurations, but have really just scratched the surface of DSC.  I will configure with both push and pull, use file share and IIS.  I don’t know if I’m up to spinning up a SharePoint farm with DSC, but I think that would be a good “capstone” project to make sure I understand DSC.  Maybe if I can think of a good resource that hasn’t been written by the community I’ll go that direction, too.
  4. PowerShell User Group.  There has been some interest in starting a user group in my state (Missouri), but nothing has materialized.  I will definitely get something going at work (~6000 employees, maybe 1000 potential PowerShell users).  Depending on my schedule, I’m considering starting a SW Missouri PowerShell group.  Let me know if you’re interested.
  5. Continue Teaching at work.  I mentioned in my 2015 review that I recruited another instructor, but I’d like to aim for 10 in-person sessions this year like last year.  I think the classes we do have become repeatable enough that I might even record them for distribution to some of the smaller offices that I won’t be able to travel to.
  6. Share more on Github.  I’ve got some connectivity-testing scripts (for a server farm) and some really fun Visio stuff  already written.  Just need to package it up and put it out there.
  7. Write more professional scripts.  I’m going to have a post on this in the coming week.  There are certain things that I think should be standard ingredients in a “professional script”.  I don’t often polish things to this level, but that’s more laziness than anything else.  It’s time I started practicing what I preach and writing things that are on a different level.
  8. Speak.  Being involved in user groups will give me lots of opportunities to speak.  I’ve done this in the past and enjoyed it, but need to make this something I do regularly.
  9. Encourage.  I make it a point to comment on blog posts that I enjoy.  I need to make this more of a priority, especially with newer bloggers.
  10. Digest.  I’ve been enjoying some daily blog digests (Morning Brew and Morning Dew) as well as a couple of weekly ones (John Sansom’s Something for the Weekend and Brent Ozar’s weekly roundup) for quite a while, and I think there’s enough PowerShell content for a weekly PowerShell digest.   I’ve been bouncing this idea around in my head for a while now and I think the time is right.

What do you think?  This ought to keep me plenty busy.



Invoke-SQLCmd considered harmful

I mentioned here that Invoke-SQLCmd (included in the SQLPS module for SQL Server) was susceptible to SQL-Injection attacks, but I haven’t demonstrated that or ever seen anyone show it.

To do so, I’ll start with code out of the help for Invoke-SQLCmd.  Here’s the code (taken from here)

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

Notice that the parameters are encoded in a string. This is a easy-to-see red flag that SQL-Injection might be possible.  To see the problem, let’s replace ‘String1’ with a SQL statement:

$MyArray = "MyVar1 = (select top 1 name from msdb.sys.databases)", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray


So even though our select statement (the -Query parameter to Invoke-SQLCmd) wasn’t looking at any tables, the result we got was a value out of a table. I did have to remove the single quotes, but the command which was in a parameter was executed.

A couple of minor problems with SQLPS which are unrelated to Invoke-SQLCmd but bother me nonetheless are:

  • Encode-SQLName and Decode-SQLName both use unapproved verbs.  Cmdlets with these names were present in the SQLPS Snap-In, so I can understand them being here now.  A better solution would be to rename them (ConvertTo-SQLName/ConvertFrom-SQLName?) and provide aliases with the legacy Encode/Decode names.
  • Importing the module changes the current location.  The module does implement the SQLServer provider and SQLServer: drive, but it should not do a Set-Location when the module is imported.  Especially with automatic importing, this could cause some strange unexpected results if someone isn’t paying attention.

There are a lot of cmdlets in the SQLPS module which are very well implemented, so don’t think that I’m saying you should avoid it altogether.  Also, the parameter interface for Invoke-SQLCmd was implemented the way it was to stay compatible with the command-line tools that ship with SQL Server, so I understand where they are coming from.  I would have preferred that they also provide a solution using real parameters (SQL language parameters, that is).  I would recommend that you look at Invoke-SQLCmd2, Adolib, or POSH_Ado for examples of parameterized implementations.


A confusing PowerShell script

I recently stumbled upon a bit of code (not important where) that had a line that looked like this:

$Something.SomethingElse -Contains 'Foo'

When I saw it, I immediately thought “they’re using the -Contains operator instead of the -Like operator or the .Contains() method.” This is a common mistake, so my radar picks it up without me even thinking about it.

I was wrong, however. The $Something variable was (probably) a list, so the dot-operator got a list of SomethingElse property values from the items in the list. Then, -Contains made sense.

I don’t think I like the dot-operator being used this way in published code. I feel like it’s less clear than something more explicit like this:

($Something | Select-Object -ExpandProperty SomethingElse) -Contains 'Foo'

or even (for this specific example at least):

 [bool]($Something | Where-Object SomethingElse -eq 'Foo')

Both of these are a bit longer than the original but I think it’s a lot clearer that we’re working with a collection of objects with a SomethingElse property and that we’re actually looking for an item in the collection, rather than accidentally using the wrong operator.

There aren’t a lot of features of PowerShell that I don’t like, but this is one that I don’t tend to use.

What about you? Do you use the dot this way?

Let me know what you think in the comments.