Sunday, October 27, 2013

[Math] in PowerShell

Basic arithmetic is, of course, built-in to PowerShell.

3 + 2  yields  5
3 - 2  yields  1
3 * 2  yields  6
3 * ( 3 + 2 )   yields  15
3 / 2   yields  1.5

That last one is nice.  In some programming languages, if you divide two integers, it will give you an integer result; if you don't want to lose your precision, you need to convert your variable types before the calculation, which is messy.  But in PowerShell, the result is of a logical type, and in those rare circumstances when you need to change it, it's always very easy.

And that's about all of the math PowerShell can do.

Wait, what?  That's it?

That's pathetic.  My granddaughter can do more advanced math than that.  Why can't PowerShell do better than that?

It would have been nice if PowerShell had more advanced mathematical operators built in to it to allow us to write pretty mathematical equations that look like what we expect them to look like.  But that would have been difficult to do without breaking PowerShell's language parsing paradigms, forcing the whole thing to get klunkier and slower.  So they didn't.

But we can live without it, because anything you can do with .Net, we can leverage with PowerShell.

There is a System class named [math], with 30 static methods we can use for doing math.  (I think that's where they got the name from.)

[math] is a static class, which means you can't create a [math] object.  It is a collection of methods, code snippets that we can leverage in our scripts.

Because it is a static class, we can't use our usual trick of piping a variable to Get-Member to see what it can do. Instead, use:


If you actually tried that, you saw more than was useful.  Try piping it to a Select command to see the names of the 30 methods:

[math].GetMethods() | Select -Property Name -Unique

We use the -Unique switch to eliminate duplicates from our list.  There are duplicates because it has separate methods for each type of input parameters we might give it.  For example, there is a .Min() method for 32-bit integers, another for 64-bit integers, another for double length decimals, etc.  We won't have to differentiate between them, because .Net will dynamically choose the correct one based on what we feed into it.  And if none of them quite match, such as if we feed in strings that can be converted to numbers, PowerShell is kind enough to convert them to an appropriate number class for us. 

The other way to get details on what a class can do and how to use it, is to find the official Microsoft article about it.  Search for MSDN, the class name (including the namespace, if you know it), and the word "methods", for example.  In this case, Google "MSDN system.math methods" without the quotes.

Or just click here.

Constants Pi and E

In addition to the 30 methods, there are also 2 useful fields, which give you the two most used mathematical constants.

To access a static method or field, we use the name of the class and the name of the method or field, separated by two colons.

[math]::e  yields  2.71828182845905
[math]::pi  yields  3.14159265358979

You can use them thusly:

$Area = [math]::pi * $Radius * $Radius

Powers and exponents and logarithms

You place the parameters for methods in parenthesis after the method name, separated by commas.  This is a little clunky if you are used to standard mathematical notation.  But if you are experienced with Excel formulas, you will feel right at home. One advantage these have over Excel formulas is that you can add spaces, making them actually readable.

::Pow(), short for "power" is the method for raising a number to a power.  For example, to raise two to the third power, two cubed:

[math]::pow( 2, 3 )
yields 8

So we could rewrite our area of a circle formula:

$Area = [math]::pi * [math]::pow( $Radius, 2 )

But I just stick to $x * $x when I need to square something, and save ::pow() for higher or fractional powers.

::exp() is specifically for raising e to a power, and not for more general exponents as you might logically think.  So it's not very useful, but if you need to square e, use:

[math]::exp( 2 )

::sqrt() is for square roots.

[math]::sqrt( 64 )
yields 8

[math] doesn't have a method for doing other roots directly.  This is when you have to dredge up the math you never thought you would use, and remember that taking a root is the same as raising to the power of the reciprocal of the root.

So, to find the cube root of 27:

[math]::pow( 27, 1/3 )
yields 3

Use ::log() to calculate the base e logarithm of a number and ::log10() to calculate the base 10 logarithm.

To calculate the logarithm in any other base, divide the logarithm of your number by the logarithm of the desired base.  Thus, to find the base 2 logarithm of $X, use:

[math]::log( $X ) / [math]::log( 2 )

Is that useful?  Sometimes.  The base 2 logarithm of a number tells you how many bits you need to represent that number.

Rounding and remainders

While there is a glaring lack of some types of mathematical functions, there is a bunch of them for rounding.

::round() is the one you will likely use most.  Give it a number and a number of places to the right of the decimal, and away it goes.

[math]::round( 1234.5678 , 2 )  yields  1234.57
[math]::round( 1234.5678 , 0 )  yields  1235
[math]::round( 1234.5678 , -2 )  yields  1200

I don't use [math]::round() when rounding to zero decimal places.  In that case it's much neater (pun intended) to take advantage of PowerShell's dynamic type conversion.

Instead of
[math]::round( $X, 0 )

I use
[int]( $X )

This causes PowerShell to convert the value to an integer, which is essentially what rounding to zero does anyway.

One caveat.  By default, both ::round() and [int] do "midpoint rounding" differently from what you may have learned in school.

I was taught that when the remainder was exactly 5, always round up.  That is what we will call "away from zero" midpoint rounding.

There is also something called banker's rounding, used by statisticians and the financial industry.  They would prefer to round up only half the time, so they always round to the nearest even number, what we'll call "to even" midpoint rounding.

::round() and [int] use "to even" rounding.

This means that:

[math]::round( 12.345 , 2 )  yields  12.34
[math]::round( 12.355 , 2 )  yields  12.36
[math]::round( 12.365 , 2 )  yields  12.36
[math]::round( 12.375 , 2 )  yields  12.38

If you want to use "away from zero" rounding, you can add an additional parameter to specify your desired rounding method.  The parameter takes the [system.midpointrounding] enumeration.  Or we can use a string and force PowerShell to do the conversion, as it's a little less messy that way.  Or, you can just use the integer behind the enumeration, which in this case is 1, but then the average person looking at your script won't know what that means.  All of these examples work to force "away from zero" midpoint rounding.

[math]::round( 12.345 , 2 , [system.midpointrounding]::AwayFromZero )  yields  12.35
[math]::round( 12.345 , 2 , [midpointrounding]::AwayFromZero )  yields  12.35
[math]::round( 12.345 , 2 , "AwayFromZero" )  yields  12.35
[math]::round( 12.345 , 2 , 1 )  yields  12.35

(If you really like extra typing or if you are using a variable to store your midpoint rounding method, you can use [midpointrounding]::ToEven  or "ToEven" to specify "to even" midpoint rounding.)

[int] cannot use anything other than "to even " midpoint rounding.

::truncate() simply truncates or chops off the decimal.  This is effectively toward zero rounding.  Note that this is NOT midpoint rounding.  Everything rounds toward zero, not just 5’s.

[math]::truncate( 2.2 )  yields  2
[math]::truncate( 2.8 )  yields  2
[math]::truncate( -2.2 )  yields  -2
[math]::truncate( -2.8 )  yields  -2

::ceiling() rounds everything up, towards positive infinity.

[math]::ceiling( 2.2 )  yields  3
[math]::ceiling( 2.8 )  yields  3
[math]::ceiling( -2.2 )  yields  -2
[math]::ceiling( -2.8 )  yields  -2

::floor() rounds everything down, towards negative infinity.

[math]::floor( 2.2 )  yields  2
[math]::floor( 2.8 )  yields  2
[math]::floor( -2.2 )  yields  -3
[math]::floor( -2.8 )  yields  -3

(And if those aren't enough, in my next article I talk about for using Excel's plethora of worksheet functions in PowerShell, which gives you another umpteen ways to round things.)

Minimum and maximum

::min() and ::max() have two uses.

The first is to choose the larger or smaller of two values.
[math]::min( 2 , 3 )   yields  2
[math]::max( 2 , 3 )   yields  3

The second is to set a maximum or minimum for a given value.  The weird trick is, when you're using them for this, you use the function with opposite name from the effect you want your limit to have.

To set a minimum of zero, for example, you take the maximum of your value and zero:

[math]::max( 20 , 0 )   yields  20
[math]::max( -20 , 0 )   yields  0

This seems obvious when you are looking at those two commands, but when writing these things off the top of your head with variables, it's easy to do them backwards.

$XButAMinimumOfZero = [math]::max( 0, $X )
$XButAMaximumOfTen = [math]::min( 10, $X )


All of the basic trig functions are here.  Keep in mind that they assume the input parameter to be in radians, not degrees.  If you are using degrees, multiply your variable by pi divided by 180.

Sine of 45 degree would be:
[math]::sin( 45 / 180 * [math]::pi() )

Sine  ::sin()
Cosine  ::cos()
Tangent ::tan()

The "arc" functions are for doing it backwards.  They are for when you know the sine or cosine or tangent of the angle, and you want to know the angle.

Arcsine  ::asin()
Arccosine ::acos()
Arctangent ::atan()

The results are going to be in radians, so if you need degrees, you need to multiply the results by 180 divided by pi.

$AngleInDegrees = [math]::asin( $X ) * 180 / [math]::pi

::atan2() is for when you know the lengths of the sides of the triangle opposite and adjacent to a given angle, and you want to know the size of the angle.  Since dividing the two gives you the tangent, you can easily use ::atan() for this, but commas are better than forward slashes?  Whatever.

These both do the same thing:

$MouseAngleInDegrees = [math]::atan2( $DeltaY, $DeltaX ) * 180 / [math]::pi
$MouseAngleInDegrees = [math]::atan( $DeltaY / $DeltaX ) * 180 / [math]::pi

And then there are the hyperbolic trig functions, but if you need to use those, you already understand what those are for better than I do.  (It has been a long time since high school trig, and they are hardly ever needed for administering servers.)

Hyperbolic sine ::sinh()
Hyperbolic cosine ::cosh()
Hyperbolic tangent ::tanh()


::equals() compares two values and gives you a [boolean] (true/false) result.

Be careful with this one.  Unlike many of these methods, your input parameters will NOT dynamically adjust the input parameters so that the types match.

As far as this method is concerned, integer 7 does NOT equal decimal 7.0!

$X = 7
[math]::equals( $X, 0 )  yields  False
[math]::equals( $X, 7 )  yields  True
[math]::equals( $X, 7 )  yields  False
[math]::equals( $X, "7" ) yields False

It’s generally preferable to use PowerShell’s –eq comparison operator.  The –eq operator does convert between types and give the results you are likely expecting.

$X -eq 0   yields  False
$X -eq 7   yields  True
$X -eq 7.0   yields  True
$X -eq "7"   yields True

Plus syntactically it's closer to human-speak, so it's easier for  humans to understand when you say stuff like:

If ( $x -eq 7 ) { Stop-Computer }

::abs() returns the absolute value of a number.

[math]::abs( 10 )   yields  10
[math]::abs( -10 )   yields  10

::sign() returns a 1 for positive numbers, a 0 for zero, and a -1 for negative numbers.

[math]::sign( 45.2 )  yields  1
[math]::sign( 10 - 6 - 4 )  yields  0
[math]::sign( 5 - 72 )  yields  -1

::IEEERemainder() calculates the remainder of a division, but a little oddly.  Normally, when dividing X / Y, you find the greatest multiple of divisor X less than the dividend Y, and subtract it from dividend Y to get the remainder.  This function instead calculates the multiple of divisor Y that is closest to dividend X, even if it is greater than dividend X, and then subtracts.  So the IEEERemainder can be positive or negative, but its absolute value is always less than half of divisor Y.  Another way to look at it is that it tells you the minimum amount you would have to add or subtract from dividend X to make it an exact multiple of divisor Y.

[math]::ieeeremainder( 23, 7 )  yields  2
[math]::ieeeremainder( 21, 7 )  yields  0
[math]::ieeeremainder( 19, 7 )  yields  -2

::BigMul() is an oddball you'll never use.  It's just for multiplying and it's only useful if you need more then 32-bit precision, but oddly it only takes 32-bit precision numbers as input.  You will never need that much precision. If you do, you probably already have it in your multiplicands, and this function will fail.  It's better to just use PowerShell to dynamically convert your numbers, and multiply normally.

Instead of ::bigmul(), use:

$X = 1234567891
$Y = 1234567891
[int64]$X *[int64]$Y

Which yields 1524157877488187881

::divrem() will do division and give you the quotient and remainder in separate variables.  This one is for the software developers looking to shave milliseconds off of their processing time.  We're scripters.  We prefer simple and elegant.

Let's say we need to divide $X by $Y to get integer quotient $Q and remainder $R.  (::divrem() requires variable $R to exist before we use it, so we'll need to create it.  Developers always do that, but we're scripters, and we prefer simple and lazy.). We could use:

$R = 0
$Q = [math]::divrem( $X, $Y, [ref]$R )

But I would just use modulus (%) to calculate the reminder, and then use it to calculate the integer quotient.

$R = $X % $Y
$Q = ( $X -$R ) / $Y

Excel in Powershell

We can do most things with the [math] functions above.  But sometimes we need to get a little crazy.  Sometimes you wish you could do something that's easy in Excel, but next to impossible in PowerShell.

I’ll cover that in my next article.

Clean up WSUS synchronization reporting with SQL script

Okay, this one isn't about PowerShell.  But it's a script, and it's for server admins, not database admins, so I'm going to post it here anyway.

When you go to the Synchronizations page of the WSUS console, it takes a long time to load.  If you have been using it for years, it takes a really, really long time to load.  If you have cranked up synchronization frequency, forget it.  Don't even try.  It's a useless report.  Well, it's a useful report, but you can't ever use the report, because it won't load in any reasonable amount of time.

In my environment, we use SCCM to deploy and manage ForeFront Endpoint Protection, but nothing else.  All patching still goes through WSUS, including definition updates for Endpoint Protection.  To be sure we get virus dates a soon as they come out, including and especially emergency out-of-band updates, we synchronize every hour.

So we have lots and lots of synchronizations, and can't use the report unless we clean up the database.

The information displayed in the report is a simple, flat table, summarizing synchronization results.  It would take a trivial amount of disk space to store this summarized data in a table for easy reference.  If it was stored in this format, the amount of CPU and disk I/O and user wait time required to retrieve it would be too small to measure.

Unfortunately, it wasn't somebody's job on the Microsoft WSUS team to care about any of that.  Instead, through many generations of WSUS, they have continued to use an algorithm that is, well, insane.  They query the event table for "started synchronization" events.  Then they find all of the corresponding "finished synchronization" events.  Then, for every single one of those synchronizations, they query the revision table to count up the number of revisions that were received during that time period.

They run a query for each and every line of the report, one for each of the thousands of synchronizations.  And they repeat the whole thing every time you open or refresh the report.

I have seen other scripts that you can run from time to time to delete all synchronizations, if you want to truncate your synchronization history, but if you delete them all before you can look at the report, then you have a blank report, so that doesn't help us much here.

I don't care about synchronizations that found nothing new.  And I don't care about synchronizations that find nothing but virus updates.  (I like to see all of today's synchronizations, so that I can see that they are happening, but I don't need to see older, trivial synchronizations.

So I have created a SQL job that runs at 2 AM every day, and runs the script below.  (We have our WSUS database in a full version of SQL.)

This script finds all of the synchronizations that received no new updates other than virus updates, and deletes the corresponding "synchronization started" entry from the event table.

--  CleanupWSUSSynchronizationHistry.SQL
--  Delete All Synchronization Started events from the tbEventInstance table
--    so that they don't show up on the Synchronizations page of the WSUS console.
--    (because that takes forever if there are hundreds of them)
Delete From [SUSDB]..[tbEventInstance]
Where EventID in ( 381, 382 )
  And [EventInstanceID] in ( Select [EventInstanceID]
   From (  Select [EventInstanceID],
    [TimeAtServer] As StartTime,
    ( Select Top 1 [TimeAtServer]
     From [SUSDB].[dbo].[tbEventInstance] As I
     Where EventID in ( 384, 386, 387 )
       And I.EventOrdinalNumber > E.EventOrdinalNumber ) As EndTime
    From [SUSDB]..[tbEventInstance] As E
    Where EventID in ( 381, 382 )
      ) As O
   Where ( Select COUNT(*)
    From [SUSDB]..[tbUpdate]
    Where LegacyName is not Null
      And LegacyName not like '2461484_Definition%'
      And [ImportedTime] > O.StartTime
      And [ImportedTime] < O.EndTime ) = 0

Thursday, October 24, 2013

PowerShell koan: When is an array not an array?

They did it to me again.  I was halfway through a perfectly good rant about the half-witted way PowerShell handles a ForEach loop when the array we're looping through isn't as expected, when I went and tested the behavior I was describing.  Only to find that the annoying behavior had disappeared between versions 2.0 and 3.0.

Thanks, PowerShell team, for once again screwing up a perfectly good blog rant by improving your product.  Thanks.  Thanks a lot.

Wednesday, October 16, 2013

A simple GUI for connecting to your servers in PowerShell

This article was the basis for a talk I gave at the Twin Cities PowerShell User Group.  If you are in the Minneapolis area, join us the second Tuesday of the month.

As people reading this blog, we are by definition command line geeks.  We like to sit at a black or blue console with white text and tease information and action out of our systems.  We sit at a white screen and write clever ad hoc scripts to do half a day's work in minutes.  We write snippets to interact with no one as they perform automated tasks in the middle of the night.

But we are also Microsoft geeks.  We are comfortable with GUI interfaces.  We like mousing around just as much as an arcane command.  We know at least two ways to do everything, one with a keyboard, and one with a mouse.  We know that each method is useful in different circumstances.

And because we are using modern versions of Windows, we have the capability of leveraging GUI interfaces for our scripts.

PowerShell is built on .Net.  .Net is designed for Windows software development.  Almost anything you can do in C#, you can do in PowerShell.  And while it can get quite complicated to do complicated things, it is relatively simple to do simple things, because all of the functionality is built into .Net and Windows.

Six months ago, we were spinning up a new network infrastructure in for a new corporate entity.  We were up to a couple dozen servers in numerous sites on several continents.  We had several engineers in remote locations spinning up new servers every day.  We weren't communicating effectively about our respective tasks, and it was getting hard to keep track of what was what and where it was and how to connect to it.

So I threw together a quick script to grab all of the information about our servers from Active Directory, organize it into a sortable table, and use it to quick launch RDP sessions.

When designing forms, it can be very helpful to use a visual forms editor.  Sapien Technologies, makers of PrimalScript, also make PowerShell Studio.  They have a free version, PrimalFormsCE, which has not quite all, but most of the functionality intact.

But this is a simple form, with only two controls on it, and I could have just borrowed the necessary chunks from other scripts to tweak for this one.

First we load the assemblies we need.  I have a long blog article around here somewhere, about Add-Type versus the alternatives.  But it's mostly just a rant, so I will spare you the details.  This is where we load the definitions of the .Net objects we are going to use.

Add-Type -AssemblyName "System.DirectoryServices"
Add-Type -AssemblyName "System.Drawing"
Add-Type -AssemblyName "System.Windows.Forms"

We define the three objects that will make up the form: the Form itself, a DataGridView to display the server table, and a Label to display "Loading…" while we wait.

We don't need anything else, because this is going to be a very simple form.  The data doesn't change much, so I didn't build a mechanism for automatically or manually reloading the data.  Sorting will be triggered through a click event on the DataGridView, and creating remote sessions will be triggered by a double-click event.

$formRDP = New-Object System.Windows.Forms.Form
$labelLoading = New-Object System.Windows.Forms.Label
$dgvServers = New-Object System.Windows.Forms.DataGridView

As simple as those three statements are, they have actually done most of the heavy lifting for us. We are going to customize those objects a bit, but they are now in place with scores of properties with default settings, with most of their expected behavior built in.  The form, for example, already has 126 properties.  It already has everything to look and act like a Windows form.  It has the right look.  It has a working red X in the corner.  It can be resized, minimized or maximized.  It inherits colors and other settings from the user's Windows settings.  And it sits and watches for the user to interact with the form, through the keyboard or mouse or otherwise, all without any extra code from us.  It's all built in to .Net and Windows.

Next we define our customizations of the components.  We'll add event handlers near the very end, after we define the code that the events will trigger.

In the Windows Forms world, the big square thing on screen, the application, the dialog box, the Window, whatever else you want to call it, is a Form object.  All of the things on the form are called Controls of various types.

We give the form a .Name, the .Text it is going to display in its title bar, and an initial .ClientSize.

# formRDP
$formRDP.Name = "formRDP"
$formRDP.Text = "RDP servers"
$formRDP.ClientSize = '454, 330'

A Form has a .Size and a .ClientSize.  The .Size is the overall size of the form, including the title bar and the borders, both of which will inherit settings from the user's Windows profile. The .ClientSize is the size of the form minus the title bar and borders; it's the playing field, the part we'll be playing on, and the part where we will be putting our controls.  So we'll define that, and let Windows automatically handle the stuff around it.

The .ClientSize property actually takes a [] object as input, not a string.  Other sources will tell you to create a new [] object, set its properties as desired, and then feed that into the .ClientSize property.  What a waste of typing.

We are scripters, not software developers.  We write scripts to make lives easier, mostly our own.   Our priority when making stylistic scripting choices is for it to be easy to write, easy to read, easy to understand and easy to modify.  (Without sacrificing quality and functionality, of course.)

One of the best things they put in PowerShell is its facility at dynamic type conversion.  If you give it a string variable where it needs an integer, for example, it will try no less than 10 different ways, if needed, to convert that variable into what it needs it to be.

And in this case, we can tell it the number of pixels we want in our playing field in an easily readable string, without having to invest any effort in knowing or remembering what object type it's supposed to be.  PowerShell does all the work for us.

Then we add the two controls to the form.  The order we add them is important in this case, because it determines which one will cover the other one when both are "visible".



The label will be visible when the form first loads because we will make the DataGridView not visible to start with.  That way we have a "Loading…" notice instead of just a long pause before the form appears while it is loading and querying Active Directory.  We give it a .Name, the .Text it is going to display, the .Font to display it in, a .Size, and a relative .Location on the form.  Several of these propertires are of weird object types, but again, PowerShell will do the conversion for us.

# labelLoading
$labelLoading.Name = "labelLoading"
$labelLoading.Text = "Loading..."
$labelLoading.Location = '26, 26'
$labelLoading.Size = '100, 23'
$labelLoading.Font = "Microsoft Sans Serif, 12pt, style=Bold"


What's the difference between a DataGrid control and a DataGridView control?  The DataGridView is just a more advanced version of the DataGrid control.  A few versions of .Net ago, they wanted to make some changes in what a DataGrid did, not just enhancements, which meant they would break old scripts and applications.  So to couldn't keep it backwards compatible with old code unless they used a new name for the version with the new functionality.

The DataGridView is going to give us a pretty table to display our data.  We give it a .Name, a .Size, and a relative .Location on the form.

# dgvServers
$dgvServers.Name = "dgvServers"
$dgvServers.Location = '13, 13'
$dgvServers.Size = '429, 305'

Then we define its .Anchor points.   These will determine how it behaves when an end user changes the size of the window.  We wanted our Label, above, to stay put relative to the top left corner of the form, so we did not define a custom .Anchor, leaving it as the default 'Top, Left'.  We want the DataGridView to grow when the form grows, so we will anchor all four of its sides to the sides of the form.  When the window changes sizes, the sides of the DataGridView will maintain their distance from the sides of the Form.

The .Anchor actually just takes an integer from 0 to 15, but if we used that, you would not be able to tell by looking at the script what the number meant, or how to change it when needed.  You could use AnchorStyle enumerations and use bit-wise-or comparisons to join them together, but that would look like this:

$dgvServers.Anchor = [System.Windows.Forms.AnchorStyles]::Top -bor [System.Windows.Forms.AnchorStyles]::Bottom -bor [System.Windows.Forms.AnchorStyles]::Left -bor [System.Windows.Forms.AnchorStyles]::Right

So instead, we are going to again take advantage of PowerShell's powerful dynamic type conversion.  We just put in a human readable string, and let PowerShell do the rest.

$dgvServers.Anchor = 'Top, Bottom, Left, Right'

Turn off end-user editing of the data, because we're just displaying it, not monkeying with it.

$dgvServers.AllowUserToAddRows = $False
$dgvServers.AllowUserToDeleteRows = $False
$dgvServers.ReadOnly = $True

We set the column header height to auto.

$dgvServers.ColumnHeadersHeightSizeMode = 'AutoSize'

And we make it invisible, so the Loading... will show until the DataGridView is ready.

$dgvServers.Visible = $False

Event handlers

"Events" are specific things that can happen to objects.  Windows watches for events to occur, and then runs an appropriate chunk of code.  Many of these events and code chunks are built into Windows, and we are leveraging those here.  We don't have to script out what will happen if the end-user clicks the red X in the corner of the form.  (We could override the default event handler if we wanted to, but we are content with the default action of closing the form and ending the script.)

We do need to create three chunks of custom code for handling three events, the initial loading of the form (and the data on it), clicking on a column header (to sort the data), and double click on the data (to launch a remote session to the server).


You can search MSDN for details on any native .Net object, including a list of the events that Windows will be watching for.  You would think we would want to use the Load event to do stuff related to loading the form, but everything that happens during Load happens before the form is visible, which means there would be an unacceptable delay while our code ran and retrieved data from AD and made it pretty.  So we are going to use the Shown event of the form, which occurs when the form is fully loaded, and first made visible.

# RDP form OnShown event handler
$formRDP_Shown =

The first thing we are going to do is change the size, because I realized during testing that the original size we used above isn't big enough.  We could just change the size in the code above where we defined the Form, but if we did that, we would also have to do the math and manually modify the size of the DataGridView to match.  Simple enough in this case, but with a more complex form, with lots of objects and lots of different anchor types, manually changing sizes and locations accurately would become onerous.
But, if we leave all of the original configurations as is, with the objects configured properly relative to each other, and the wait until the form is loaded and running, any time we make a change to the size of the form, Windows will automatically move around and/or resize the controls anchored to it, as appropriate for the configured anchor types.

$formRDP.ClientSize = '900, 700'

Then we .Refresh() the Form to show the changes while we continue with loading the data.  Sometimes Windows waits patiently for your script to finish cranking before it refreshes the screen, and we want to be sure our "Loading…" .Label is displayed as early as possible.


We get the name of a local domain controller.

$DC = ( Get-ADDomainController -Discover -DomainName Contoso.local ).HostName[0]

We query the domain controller for all of the servers in the domain, getting the default properties, plus a few more with information we want to display.

$Servers = Get-ADComputer `
    -Server $DC `
    -Filter 'OperatingSystem -like "*Server*"' `
    -Properties Created, Name, DNSHostName, IPv4Address, OperatingSystem, Description, CanonicalName

Then we extract the exact information we want to display, give it property names that will become the column headers we want, give it an initial sort order, and stick it in an array.  This array variable is scoped at the script level so that it can be referred to later within a function.

In the organization this was originally written for, the first two letters of a server name are the site code, and the next two letters indicate the server role, so I wanted each of those in its own sortable column.

We want to know what OS they are running, but we don't need to have the words "Windows", "Server", and "Datacenter" redundantly showing up repeatedly, making it more difficult to easily see what is what, so we'll strip those out.  In your version of the script, you may strip out additional words, or you may prefer to put some back in.

Lastly we want to know what OU structure they are in, but we don't need to know the domain, so we'll strip that out.  You'll need to modify your version of the script for your domain.

Then sort everything by name.

$Script:ServerData = $Servers `
  | Select -Property `
    Name, `
    @{ Name = "Loc" ; Expression = { $_.Name.Substring(0,2) } }, `
    @{ Name = "Type" ; Expression = { $_.Name.Substring(2,2) } }, `
    Description, `
    IPv4Address, `
    @{ Name = "OS" ; Expression = { $_.OperatingSystem.Replace("Windows Server","").Replace("Datacenter","").Trim(" ") } }, `
    @{ Name = "OU" ; Expression = { $_.CanonicalName.Replace("Constoso.local/","").Replace($_.Name,"").Trim("/") } }, `
    DNSHostName `
   | Sort Name

The data in a DataGridView needs to be in an array list rather than an array, so we create an array list object, add the data to it, then assign it as the data source for the DataGridView.

$Script:ServerGridData = New-Object System.Collections.ArrayList
$Script:ServerGridData.AddRange( $Script:ServerData )
$dgvServers.DataSource = $Script:ServerGridData

Make the DataGridView visible (covering the "Loading…" label), and tell Windows to resize the column widths based on the actual data.

$dgvServers.Visible = $True
$dgvServers.AutoResizeColumns( "AllCells" )


This is the function that will run when the end user clicks on a column header, to sort the data alphabetically by that column.  We are using a function instead of a code block so that the event can pass us variables that will include information on which column was clicked.

When we add the event handler--near the end of the script--we will use the built-in variables $This and $_ as parameters for the function.  We'll pick those up within the function as $Sender and $EventArgs.  $Sender won't be used in this particular script, but this is one of those things that I keep standard.

# Servers datagridview column header click handler
function dgvServers_OnColumnHeaderMouseClick ( $Sender, $EventArgs )

$EventArgs.ColumnIndex gives us the column number that was clicked on.
$dgvServers.Columns gives us an array with all of the column objects.
$dgvServers.Columns[$EventArgs.ColumnIndex] gives us the column object for the column that was clicked.
$dgvServers.Columns[$EventArgs.ColumnIndex].HeaderText gives us the column header.

Since the column header was automatically created based on the properties in the array, we can reverse the process and use the column header to reference the correct property in the array objects.

$SortProperty = $dgvServers.Columns[$EventArgs.ColumnIndex].HeaderText

So we take our original array and sort it by the desired column, with a secondary sort on the server name.

$SortedServerGridData = $Script:ServerData `
  | Sort-Object -Property $SortProperty, Name

We then recreate the ArrayList as a new, empty object, add the sorted data to it, and make it the new data source for the DataGridView.

$Script:ServerGridData = New-Object System.Collections.ArrayList
$Script:ServerGridData.AddRange( $SortedServerGridData )
$dgvServers.DataSource = $Script:ServerGridData


When the end user double clicks on any entry, we want to launch an RDP connection to the server that was double clicked.  The first click of the double click will select the cell.  So we can just reference the SelectedCell to know what was clicked on.

The fully qualified domain name of the server is in the eighth column--column 7 when you start counting at zero.

$dgvServers.SelectedCells gives us an array of selected cell(s).
$dgvServers.SelectedCells[0] gives us the first selected cell (the only cell that can be in the array, because selecting multiple cells is disabled by default).
$dgvServers.SelectedCells[0].RowIndex gives us the row number.
$dgvServers.Rows gives us an array with all of the row objects.
$dgvServers.Rows[$dgvServers.SelectedCells[0].RowIndex] gives us the row object for the row number of the cell that was clicked on.
$dgvServers.Rows[$dgvServers.SelectedCells[0].RowIndex].Cells gives us the cells in that row.
$dgvServers.Rows[$dgvServers.SelectedCells[0].RowIndex].Cells[7] gives us the eighth cell in that row.
$dgvServers.Rows[$dgvServers.SelectedCells[0].RowIndex].Cells[7].Value, at last, gives us the contents of the cell in the eighth column of the row that was double clicked.

# Hosts datagridview double click handler
# Connect to host
$dgvServers_OnDoubleClick =
$RemoteServerName = $dgvServers.Rows[$dgvServers.SelectedCells[0].RowIndex].Cells[7].Value

Then all we have to do it tell Windows to launch mstsc and point it to the desired server.  (Mstsc.exe is short for Microsoft Terminal Services Client, which was the old name for the RDP client.)

& mstsc /v:$RemoteServerName

Almost done

Lastly we tell Windows to add event handlers for the three events, and tell it what to do if and when any of these three things happen.  For two of them, we give it the name of the variable with the predefined code block.  For one, we put in a code block that calls the defined function and passes it the variables with the information it needs.

# Add event handlers to form
$formRDP.add_Shown( $formRDP_Shown )
$dgvServers.add_DoubleClick( $dgvServers_OnDoubleClick )
$dgvServers.add_ColumnHeaderMouseClick( { dgvServers_OnColumnHeaderMouseClick $this $_ } )

Then we launch the form we just defined.  We pipe it to Out-Null to suppress the "result" of the form after we close it, which would otherwise just be the confusing appearance of the word "Cancel".

# Show the Form
$formRDP.ShowDialog() | Out-Null

The result

When run, the result looks like this.

Monday, October 14, 2013

Add-Type vs. [reflection.assembly] in PowerShell

Well PowerShell did it to me again.  There is always something new to learn.  Or relearn.  Or sometimes unlearn.

I had a perfectly good article planned wherein I was going to tell you to use Add-Type instead of [reflection.assembly]::LoadWithPartialName.  But then as I started to research some of the finer details, so that I could sound like I actually knew what I was talking about, I found out I didn’t know what I was talking about.

And then I did some more research, and I am back to recommending Add-Type, but with some caveats.  And this article has morphed from a useful tip into a long rant about the poor decisions Microsoft made when implementing Add-Type.

In PowerShell, we have all of .Net at our disposal for making Windows do cool things.  But .Net has a zillion moving parts, and almost everything you install on your computer adds more .Net components.  PowerShell only loads the most commonly need parts of .Net—or .Net assemblies—into memory when it loads.  Additional .Net assemblies might be loaded by imported modules.  (And in PowerShell 3.0 and up, PowerShell can import some referenced modules without being explicitly told to do so.)

But if you want to make use of a .Net component that isn’t there by default, you have to tell PowerShell to load it.  For example, if you want to put a nice GUI interface on your script, you need to load the Windows.Forms assembly, before you can create a [form] object and load it full of [buttons] and [textbox]es and things.

In PowerShell v1.0, we would use the methods built-in to the static .Net class [system.reflection.assembly] to load assemblies.  (Unless there is an ambiguity, you can leave off “system.” any time you see it.  So we can just use [reflection.assembly])

If you know the full name of the assembly you need, you can use the .Load() method.  But what is considered the “full name” of an assembly is a little crazy.

[reflection.assembly]::Load(“System.Windows.Forms, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089”)

Or, if you know the full path to the file, you can use .LoadFrom() instead, but that’s also a little crazy.


Fortunately, there is a third useful method, .LoadWithPartialName(), which allows you to use just that part of the “full name” that most of us would consider the name.


And that worked fine.  It’s kind of ugly, and if you aren’t from a programming background, it isn’t obvious exactly what it is doing or why.  You copied it over with some code you found from the internet, and the code worked with that line, but not without it.  So you didn’t ask questions, your script worked and you moved on.

Then PowerShell 2.0 came along and gave us something better.  Well, at least they gave us something prettier.

Add-Type –AssemblyName Windows.Forms

does the same thing as


(most of the time).

It’s slightly less opaque in meaning, and it looks nicer and more PowerShelly.

So since then, I have used Add-Type in my scripts instead of [reflection.assembly].

Add-Type works great.  Most of the time.  Sometimes it’s a little quirky, and some of my script required extra research, troubleshooting, and the one line became several ugly lines to compensate.

After researching for this article, I know why.

If you use the full name, Add-Type works every time.  But as we said, the "full name" is crazy, and we don't want to have to use it.  We want to use the more normal name.

The challenge, for the architects, is how do you have your command find the correct assembly based only on a "partial" name?  There many different things they could have done, not the least of which is to use the .LoadWithPartialName() method behind the scenes.  They could have used a layered approach with different methods to fall back on if preferred methods failed.  (For comparison, the PowerShell algorithm for converting object types will try no less than 10 different methods, if necessary.  See Understanding PowerShell's Type Conversion Magic)

Unfortunately, .LoadWithPartialName() has been deprecated, so they can't use that.  (Actually, several version of .Net later, it's still there, but it might not be there in version 5.  Of course, version 5 will also come with a new version of PowerShell, but logic doesn't seem to have been a large part of this decision.)

The PowerShell team, despite the numerous examples of where they made it so much more flexible and useful than more rigid programming languages, decided in his case to sacrifice useful for…I'm still not sure what.

It seems they decided that it was more important for Add-Type to work consistently than for it to work consistently.  They would prefer that your script fail on all computers, rather than work better on some than on others.

Rather than make any attempt to parse your request in the context of your system, it looks at a static, internal table to translate the "partial name" to a "full name".

If your "partial name" doesn't appear in their table, your script will fail.

If you have multiple versions of the assembly installed on your computer, there is no intelligent algorithm to choose between them.  You are going to get whichever one appears in their table, probably the older, outdated one.

If the versions you have installed are all newer than the obsolete one in the table, your script will fail.

Add-Type has no intelligent parser of "partial names" like .LoadWithPartialNames.

I can almost understand the logic behind deprecating .LoadWithPartialNames, forcing software developers to demand specific versions of assemblies.  But extending that decision into PowerShell makes no sense.  If I am writing a formal script in PowerShell that needs to run reliably on production servers for years to come, I have a responsibility to tighten it up and be specific about ensuring the right dependencies are loaded.

But PowerShell is not just about formal scripts.  It's about ad hoc scripts and command-line work.

When I am tossing together a quick couple of lines to run a SQL query and use the results in some Active Directory work, for example, I can accomplish that most efficiently if I can write the whole thing off the top of my head.

I can remember

Add-Type -Assembly Microsoft.SqlServer.Smo

I can't remember

Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

and I sure don't want to have to type it.

And worse, if I don't know what version of SQL is installed, I don't want to have to enter

Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
If ( -not $? ) { Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91" }

when I'm just fooling around at a PowerShell prompt.

PowerShell is a scripting language, Microsoft, not a programming language.  It is wonderful that you have made it so that we can use it as formally and powerfully as a programming language.  It is wonderful that in so many other respects you simultaneously give it the flexibility to be used informally as an ad hoc scripting and command-line tool.  But Add-Type is a failure in this regard.

End of rant



If your assembly name works with Add-Type, and you are happy with the version it loads, use:

Add-Type -AssemblyName Windows.Forms

If your short assembly name doesn't work with Add-Type because it isn't in the internal table, or the table lists a version that isn't available on this computer, and you are happy with any version that might be loaded, use:

[reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" )

If you need a specific version of your assembly, or if this is the future and neither of the two methods above work anymore, use:

Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"