I was reading an old blog article by Jeffrey Snover and found a hidden gem. Buried in the middle of the article is a side note with a single paragraph about PowerShell variables that are not stored in memory. Fascinated, I have been playing and experimenting and researching, and this is what I have figured out so far.
A PowerShell variable name, such as $X, points to a spot in memory where your variable object is stored. But here's the cool thing. The pointer doesn't have to point to memory. It can point to a file. (It has the potential to point other places as well. More on that later.)
Strings
Here is the syntax for storing a variable in a file.
${D:\Temp\CoolVariable.txt} = "very blue"
No quotes. Just a dollar sign with the file path and name in curly braces.
And it works the same for using the variable in a sentence.
$Full = "The sky is " + ${D:\Temp\CoolVariable.txt} + " today."
Numbers and dates
It stores the value of the variable, and only the value, in a text file. On the surface, this makes it appear that this only works for string variables. But thanks to the magic of PowerShell's powerful dynamic type conversion, it actually means it works for any simple variable type, any variable that has a single independent value. So it also can be used for all of the different types of numbers and dates, though you need to be aware that it will come back to you as a string which may or may not dynamically convert back to a number or date depending on the context.
For example
${D:\Temp\CoolVariable.txt} = 27.6
100.3 + ${D:\Temp\CoolVariable.txt}
Will work the way you expect.
But if you reverse the order of these two mismatched variable types, PowerShell will assume you want the result to be a string, and concatenate the two instead of adding them together. So you need to do the conversion explicitly.
[double]${D:\Temp\CoolVariable.txt} + 100
It does NOT work with Boolean values
It does NOT work with Boolean values. The problem is false. That is, the problem is with converting $False to and from a string.
The PowerShell designers made some great decisions about how to convert things into [Boolean] true/false values. Empty strings, empty arrays, the number zero, $Null, and $False all evaluate as $False when converted to [Boolean] values. Non-empty strings, populated arrays, non-zero numbers, $True, and more or less every other object evaluate as $True. This is very nice to have for simplifying syntax of conditionals.
If ( $X )
Is short for "If $X exists and contains a tangible thing". ("Tangible" being a relative term in a computer-modeled world. ; ) I like that functionality. I use it a lot.
But part of what I said up there is "non-empty strings… evaluate as $True." Even when the non-empty string is the word "False".
So if we convert $False to a [string], it becomes "False", and when we convert that back to a [Boolean], it becomes $True. You can demonstrate that like this.
[boolean][string]$False
And of course the same thing happens when you assign $False to a file variable and then try to use it as a [Boolean].
So don't store [Boolean] values in file variables.
Arrays
It does works with arrays.
${D:\Temp\CoolVariable.txt} = Get-ChildItem D:\Stuff | ForEach { $_.FullName }
This stores a list of the full file path and names of the files found in D:\Stuff.
And they come back out as an array of strings.
ForEach ( $FilePath in ${D:\Temp\CoolVariable.txt} ) { $FilePath }
If you stick an array of numbers in there, you can explicitly convert them as you pull them out or as you use them.
ForEach ( $EmployeeNumber in [int[]]${D:\Temp\CoolVariable.txt} ) { $EmployeeNumber }
Or
ForEach ( $EmployeeNumber in ${D:\Temp\CoolVariable.txt} ) { [int]$EmployeeNumber }
Complex objects
It works nicely as a shortcut for complex object types when you only wanted the name of the object anyway. Whenever you try to assign an object to a file variable, it uses the object's built-in .ToString() method to convert it as it saves it.
For example, if you want to save a list of services that are running,
${D:\Temp\CoolVariable.txt} = Get-Service | Where { $_.Status -eq "Running" }
This will give you a list of the service names, not the display names, because the name is what you get when you run the .ToString() method on a service object.
If you want to save the display names instead, and you are still using PowerShell 2.0, we can do that.
${D:\Temp\CoolVariable.txt} = Get-Service | Where { $_.Status -eq "Running" } | ForEach { $_.DisplayName }
If you are using PowerShell 3.0 or later, we can tighten that up a bit:
${D:\Temp\CoolVariable.txt} = ( Get-Service | Where Status -eq "Running" ).DisplayName
You may have been tempted to try using Select-Object there instead. If you did, you noticed unexpected results.
When you use Select to choose only certain properties, the objects are converted to type PSCustom. PSCustom objects act differently when you try to stick them in a file variable. The object is converted to a hash table, and the hash table is converted to a string, and the string is saved to the file variable. If you do this with an array of objects, you get an array of hash table strings.
This is almost useful. This allows you to save all of the properties of a complexes object, or at least those property values that can be easily converted to strings.
But it's not really useful, because when you try to use the variable, what you are going to get is a single string for each object, representing the hash table that represents the custom object, and it does not automatically reconstitute itself. To use the objects, you have to do something's like this.
${D:\Temp\CoolVariable.txt} = Get-Service | Select DisplayName, Name, Status
$Services = ${D:\Temp\CoolVariable.txt} | ForEach { New-Object PSObject -Property ( ConvertFrom-StringData $_.Substring( 2, $_.Length - 3 ).Replace( "; ", "`n" ) ) }
Which is far more trouble than it's worth. Use Export-/Import-CSV instead.
$S = Get-Service | Select DisplayName, Name, Status
$S | Export-CSV D:\Temp\Services.csv -NoTypeInformation
$Services = Import-CSV D:\Temp\Services.csv
Variable variable names
One disadvantage is that we are now using paths and file names as a variable name, which means they can't vary. You don't embed variables within variable names and expect PowerShell to parse them.
This does NOT work: ${$LogShare\$LogName}
Well, we can make it work by wrapping entire lines in Invoke-Expression, but it gets messy.
Invoke-Expression "`${$LogShare\$LogName} = [array]`${$LogShare\$LogName} + `$LastResultCode"
Not worth the effort.
Shared variables
One way to use a file variable is when you need a variable that can survive a catastrophic failure of the script. Or any other time you want the variable to persist between subsequent runs of the script.
Or between runs of the script on different servers.
There might be an issue if two servers tried to access the variable at exactly the same time, but you would have that with any file-based system for sharing the data. If they never ran at the same time, it would work just fine.
For example…
I have a report that needs to run every night. A scheduled task kicks off a script to generate the report at 10 PM on server A. For redundancy, the same script runs at 11 PM on server B.
I don't want server B to tax the database re-running the report if sever A was successful. But my users have a tendency to move, rename, or otherwise modify the report almost as soon as it's generated, so I can't look to the report file to confirm it's done. Coincidentally, a file variable is perfect for this.
The script ends with
${\\MainFS\TJC\ReportLastRun.txt} = Get-Date
And starts with
If ( [datetime]${\\MainFS\TJC\ReportLastRun.txt} -gt ( Get-Date ).AddHours( -2 ) ) { Exit }
That way, if the script successfully ran less than two hours ago, it immediately exits without re-running the report.
File variables vs. Get-Content and Set-Content
You may have noticed that working with file variables is similar to using Set-Content and Get-Content. When you play around with them, you find that they work exactly the same. This is because behind the scenes, they are the same. They both work by calling the iContentCmdletProvider interface of the FileSystem provider.
Measuring the performance of the two leads to some interesting results.
This
$ServerList = ${D:\Temp\ServerList.txt}
is actually faster than this
$ServerList = Get-Content D:\Temp\ServerList.txt
Where else can I stick it?
I asked Jeffrey Stover that question, and rather than file a restraining order, he graciously explained that while it had never been a high enough priority for the PowerShell team to extend the functionality to other places, he did make sure that it was possible for anyone to extend it to other things, like the registry or databases.
Then he showed how to do it in MUMPS, which wasn't exactly helpful, but his enthusiasm was inspiring.
I am planning to see if I can use this to create a simpler, more intuitive way to store information in the registry. I believe it can be accomplished by implementing the iContentCmdletProvider interface on the PowerShell registry provider. This will be my first foray into such an extreme enhancement to PowerShell, so it will be an adventure. I'll tell you all about it when it's done.
Wednesday, December 4, 2013
Pause in PowerShell
So what ever happened to "pause"?
Back in the old batch file days, you could stick in a "pause", and your script would, well, pause. It would sit there and wait until someone hit the any key. Most often you would stick it at the end of a batch file, so that the user could see that the script was done and read any output before the little black box disappeared.
It would sometimes be helpful to do that in our little blue boxes as well. But PowerShell doesn't have a Pause-Script cmdlet.
.Net to the rescue! I keep telling you that everything in PowerShell is an object. And when I say "everything", I mean everything. Even PowerShell itself. $Host is the built-in variable representing the PowerShell host itself, the thing that is running the script, the little blue box as it were. And we can access some of the things it can do, such as watching for keyboard input.
The $Host has a UI, $Host.UI. And the .UI has a UI, $Host.UI.RawUI. I have no idea why it has two layers. The .RawUI has some methods. Pipe it to Get-Member to see them.
$Host.UI.RawUI | Get-Member
.ReadKey() is the one we want. .ReadKey() tells the RawUI to get the next key press. Which means it has to sit and wait for someone to press the any key.
Let's try it without parameters.
$Host.UI.RawUI.ReadKey()
Well, that does wait for the any key, but when we press the any key, it tells us more than we ever need to know about the key we pressed. So let's pipe the output and send it to nowhere.
$Host.UI.RawUI.ReadKey() | Out-Null
Better. That prevented most of the information from showing up on the screen, but it did delay the key we pressed. It would be nice if we could make that go away as well.
Maybe if we fed it into a dummy variable.
$X = $Host.UI.RawUI.ReadKey()
Nope. That just fed the key information we had previously out-nulled into $X. That can be useful if we want to capture the specific key pressed for some reason. But the key that was pressed still displayed on the screen.
Maybe we need parameters. When we ran Get-Member on the RawUI, it showed us that for parameters it wants… Oh. It didn't really tell us. The information was truncated. We can fool around with the Get-Member command to get it to give up the information, or we can turn to MSDN, which we were going to do in a few paragraphs anyway.
After some Googling and clicking around, we end up at the MSDN article on the "PSHostRawUserInterfaceReadKey method." And we find that .ReadKey() wants TeadKeyOptions. What the heck are those? Well click on it. Hmm. That didn't help much. Apparently the "Windows PowerShell Managed Reference" section isn't formatted an internally linked as logically and intuitively and discoverable as the .Net Framework library. (Fortunately, most of the time we are searching for information that resides in the .Net library, but not this time.) so after a little more Googling and clicking, we find the article on "ReadKeyOptions enumeration."
We see there are four options we can include: Whether to react when the key is pressed or when it is released (you have to specify at least one of the two); whether or not to echo the key press to the screen; and whether or not to intercept Ctrl-c as a key press.
We really don't care whether we respond to the key up or the key down, so just pick one. We do not want the key to print to the screen, so we want the no echo option. Don't include the AllowCtrlC option, because we might want to use the pause to stop the script.
We could use the ReadKeyOptions enumerations, using bit-wise or to combine them.
$Host.UI.RawUI.ReadKey( [System.Management.Automation.Host.ReadKeyOptions]::NoEcho `
-bor [System.Management.Automation.Host.ReadKeyOptions]::IncludeKeyDown )
But that's crazy.
Enumerations just represent integers. In this case, each enumeration is a power of two, as the ReadKeyOptions parameter is storing each of the options in a single bit within. We can ask PowerShell what our enumerations represent.
[System.Management.Automation.Host.ReadKeyOptions]::NoEcho `
-bor [System.Management.Automation.Host.ReadKeyOptions]::IncludeKeyDown
Returns 6.
So for brevity, we could just use:
$Host.UI.RawUI.ReadKey( 6 )
To avoid having to remember 6, flipping all four bits by using 15 actually produces the same desired result.
$Host.UI.RawUI.ReadKey( 15 )
I am tempted to use this one. It is unlikely that I will ever use .ReadKey() for anything else, so I won't need to remember or understand what the 15 does. But I don't like it when I don't understand things, so the next time I look at this and can't remember, I'll waste time looking it up.
So I will compromise and use this:
$Host.UI.RawUI.ReadKey( "NoEcho, IncludeKeyDown" )
PowerShell's powerful dynamic type conversion capabilities convert the string to the appropriate enumerations and thence into the integer they represent. It's easier to read than the full enumerations and more informative than the integers.
.ReadKey(), of course, reads the key, and then gives it to us. But we didn't really want the key, we just wanted the pause while waiting for it, so we banish it to null.
And that gives us our (almost) simple one line replacement for "pause":
$Host.UI.RawUI.ReadKey( “NoEcho, IncludeKeyDown” ) | Out-Null
I experimented further and found two other one-liners that do the same thing. I'll spare you the blow-by-blow on discovering these.
[console]::ReadKey($True)
And
cmd /c pause
Except it doesn't work in ISE
If you have been following along and trying this in a PowerShell console, you think I'm brilliant.
But if you have been trying it in PowerShell ISE, you think I'm an idiot, because all three methods have thrown an error every time, no matter what you try.
The problem is, the "console" in the bottom of the ISE is not a real PowerShell console. It's a simulated PowerShell console. PowerShell ISE does a very good job of simulating the PowerShell console, but one of the few things that it can't do is give you direct control of the raw console UI, because there isn't one.
So what is my brilliant solution for a snippet that would work equally well in the ISE? Unfortunately, I don't have one. (I told you that you would think I was an idiot.) All of my research thus far has only suggested alternatives to the desired behavior, such as a "Click OK to continue" pop-up box to replace our "Hit any key to continue."
Such solutions have their place, but seem unsatisfactory when we were searching for something specific, something so simple that a batch script could do it with a single word. If you have any ideas, please comment below.
Thanks to Charles Christianson for asking the question that I ultimately failed to answer. Thank you, Charles, for making me look like an idiot.
Back in the old batch file days, you could stick in a "pause", and your script would, well, pause. It would sit there and wait until someone hit the any key. Most often you would stick it at the end of a batch file, so that the user could see that the script was done and read any output before the little black box disappeared.
It would sometimes be helpful to do that in our little blue boxes as well. But PowerShell doesn't have a Pause-Script cmdlet.
.Net to the rescue! I keep telling you that everything in PowerShell is an object. And when I say "everything", I mean everything. Even PowerShell itself. $Host is the built-in variable representing the PowerShell host itself, the thing that is running the script, the little blue box as it were. And we can access some of the things it can do, such as watching for keyboard input.
The $Host has a UI, $Host.UI. And the .UI has a UI, $Host.UI.RawUI. I have no idea why it has two layers. The .RawUI has some methods. Pipe it to Get-Member to see them.
$Host.UI.RawUI | Get-Member
.ReadKey() is the one we want. .ReadKey() tells the RawUI to get the next key press. Which means it has to sit and wait for someone to press the any key.
Let's try it without parameters.
$Host.UI.RawUI.ReadKey()
Well, that does wait for the any key, but when we press the any key, it tells us more than we ever need to know about the key we pressed. So let's pipe the output and send it to nowhere.
$Host.UI.RawUI.ReadKey() | Out-Null
Better. That prevented most of the information from showing up on the screen, but it did delay the key we pressed. It would be nice if we could make that go away as well.
Maybe if we fed it into a dummy variable.
$X = $Host.UI.RawUI.ReadKey()
Nope. That just fed the key information we had previously out-nulled into $X. That can be useful if we want to capture the specific key pressed for some reason. But the key that was pressed still displayed on the screen.
Maybe we need parameters. When we ran Get-Member on the RawUI, it showed us that for parameters it wants… Oh. It didn't really tell us. The information was truncated. We can fool around with the Get-Member command to get it to give up the information, or we can turn to MSDN, which we were going to do in a few paragraphs anyway.
After some Googling and clicking around, we end up at the MSDN article on the "PSHostRawUserInterfaceReadKey method." And we find that .ReadKey() wants TeadKeyOptions. What the heck are those? Well click on it. Hmm. That didn't help much. Apparently the "Windows PowerShell Managed Reference" section isn't formatted an internally linked as logically and intuitively and discoverable as the .Net Framework library. (Fortunately, most of the time we are searching for information that resides in the .Net library, but not this time.) so after a little more Googling and clicking, we find the article on "ReadKeyOptions enumeration."
We see there are four options we can include: Whether to react when the key is pressed or when it is released (you have to specify at least one of the two); whether or not to echo the key press to the screen; and whether or not to intercept Ctrl-c as a key press.
We really don't care whether we respond to the key up or the key down, so just pick one. We do not want the key to print to the screen, so we want the no echo option. Don't include the AllowCtrlC option, because we might want to use the pause to stop the script.
We could use the ReadKeyOptions enumerations, using bit-wise or to combine them.
$Host.UI.RawUI.ReadKey( [System.Management.Automation.Host.ReadKeyOptions]::NoEcho `
-bor [System.Management.Automation.Host.ReadKeyOptions]::IncludeKeyDown )
But that's crazy.
Enumerations just represent integers. In this case, each enumeration is a power of two, as the ReadKeyOptions parameter is storing each of the options in a single bit within. We can ask PowerShell what our enumerations represent.
[System.Management.Automation.Host.ReadKeyOptions]::NoEcho `
-bor [System.Management.Automation.Host.ReadKeyOptions]::IncludeKeyDown
Returns 6.
So for brevity, we could just use:
$Host.UI.RawUI.ReadKey( 6 )
To avoid having to remember 6, flipping all four bits by using 15 actually produces the same desired result.
$Host.UI.RawUI.ReadKey( 15 )
I am tempted to use this one. It is unlikely that I will ever use .ReadKey() for anything else, so I won't need to remember or understand what the 15 does. But I don't like it when I don't understand things, so the next time I look at this and can't remember, I'll waste time looking it up.
So I will compromise and use this:
$Host.UI.RawUI.ReadKey( "NoEcho, IncludeKeyDown" )
PowerShell's powerful dynamic type conversion capabilities convert the string to the appropriate enumerations and thence into the integer they represent. It's easier to read than the full enumerations and more informative than the integers.
.ReadKey(), of course, reads the key, and then gives it to us. But we didn't really want the key, we just wanted the pause while waiting for it, so we banish it to null.
And that gives us our (almost) simple one line replacement for "pause":
$Host.UI.RawUI.ReadKey( “NoEcho, IncludeKeyDown” ) | Out-Null
I experimented further and found two other one-liners that do the same thing. I'll spare you the blow-by-blow on discovering these.
[console]::ReadKey($True)
And
cmd /c pause
Except it doesn't work in ISE
If you have been following along and trying this in a PowerShell console, you think I'm brilliant.
But if you have been trying it in PowerShell ISE, you think I'm an idiot, because all three methods have thrown an error every time, no matter what you try.
The problem is, the "console" in the bottom of the ISE is not a real PowerShell console. It's a simulated PowerShell console. PowerShell ISE does a very good job of simulating the PowerShell console, but one of the few things that it can't do is give you direct control of the raw console UI, because there isn't one.
So what is my brilliant solution for a snippet that would work equally well in the ISE? Unfortunately, I don't have one. (I told you that you would think I was an idiot.) All of my research thus far has only suggested alternatives to the desired behavior, such as a "Click OK to continue" pop-up box to replace our "Hit any key to continue."
Such solutions have their place, but seem unsatisfactory when we were searching for something specific, something so simple that a batch script could do it with a single word. If you have any ideas, please comment below.
Thanks to Charles Christianson for asking the question that I ultimately failed to answer. Thank you, Charles, for making me look like an idiot.
Wednesday, November 13, 2013
Going home on time even with PowerShell script running long
It was toward the end of my day, after most of the users were gone, when I decided to take the opportunity to briefly shut down an old virtual machine and compact the disks. It was running on a Hyper-V 2012 host, so you can't compact the disks through the GUI. (It will let you try, and it won't throw any errors, but it won't actually compact anything.) So I just tweaked a little snippet of code I had lying around, and let it fly.
You have to mount the disks read only, run the compact, then dismount the disks.
$Paths = (Get-ChildItem "D:\Virtual Machines\Server27" -Recurse -Include "*.vhd*" ).FullName
ForEach ( $Path in $Paths )
{
Mount-VHD $Path
Compact-Disk $Path
Dismount-VHD $Path
}
It mounted the first disk, and zipped right through the compact. Then it mounted the second disk…
Uh oh. Apparently there was some work to be done on that one. It was going real slow. Not normally that big of a deal, but I had to leave shortly to catch the last bus home, and I need to be sure that server was back on for users in the morning.
If I had anticipated this, I could have easily added a line at the end of my snippet to start the virtual back up, but I didn't. If I had run it as a job, I could have another script watch for the job to finish, but I hadn't. If I had more time, I could write something to query the disk system to see when the disk was dismounted, but that wasn't something I could do off the top of my head in the time I had available.
I finally realized that if the disk was still mounted, it was visible to the file system. I opened Windows Explorer and found it was assigned drive letter E:. That made it easy.
I launched another PowerShell window, put in my one-liner, and went home to my lovely wife.
While ( Test-Path E:\ ) { Sleep -Seconds 10 } ; Get-VM Server27* | Start-VM
You have to mount the disks read only, run the compact, then dismount the disks.
$Paths = (Get-ChildItem "D:\Virtual Machines\Server27" -Recurse -Include "*.vhd*" ).FullName
ForEach ( $Path in $Paths )
{
Mount-VHD $Path
Compact-Disk $Path
Dismount-VHD $Path
}
It mounted the first disk, and zipped right through the compact. Then it mounted the second disk…
Uh oh. Apparently there was some work to be done on that one. It was going real slow. Not normally that big of a deal, but I had to leave shortly to catch the last bus home, and I need to be sure that server was back on for users in the morning.
If I had anticipated this, I could have easily added a line at the end of my snippet to start the virtual back up, but I didn't. If I had run it as a job, I could have another script watch for the job to finish, but I hadn't. If I had more time, I could write something to query the disk system to see when the disk was dismounted, but that wasn't something I could do off the top of my head in the time I had available.
I finally realized that if the disk was still mounted, it was visible to the file system. I opened Windows Explorer and found it was assigned drive letter E:. That made it easy.
I launched another PowerShell window, put in my one-liner, and went home to my lovely wife.
While ( Test-Path E:\ ) { Sleep -Seconds 10 } ; Get-VM Server27* | Start-VM
Saturday, November 2, 2013
Using Excel functions in PowerShell
As I was saying, we can do some basic arithmetic in PowerShell, but not much. A .Net System class named [math] has 30 math functions we can use for doing pretty much any math you will ever need to do in PowerShell.
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.
Why not do it in Excel within PowerShell?
Everything in Excel is built on the same object models as everything else, and has all the same programmatical accessibility as everything else Microsoft these days. So we can just reach inside, grab it by its objects, and tell it what we want it to do. (That didn't come out right.)
Excel needs to be installed on the machine you are running this on, so you pretty much have to run it on your workstation. It is going to load an instance of Excel in the background, so it is going to use some computer resources and time. But we have computers with nothing better to do than what we tell them to do, and we're a little crazy.
First load the assembly so that PowerShell knows what we are talking about when we start speaking in Excelese.
[reflection.assembly]::LoadWithPartialName( "Microsoft.Office.Interop.Excel" )
Next load an Excel application into an object to do our bidding.
$XL = New-Object -ComObject Excel.Application
$XL.WorksheetFunctions holds the functions we want to use. To see the full list, pipe it to Get- Member.
$XL.WorksheetFunctions | Get-Member
398 functions available! Now we’ve got math coming out of our asymptotes!
Do you have a number that you need to round to the nearest multiple of 22.5?
Just use
$XL.WorksheetFunction.MRound( $X, 22.5 )
Do you want to know the size range that 80% of your file sizes fall into?
Try
$XL.WorksheetFunction.Percent( ( $Files | ForEach { $_.Length } ), 0.1 )
$XL.WorksheetFunction.Percent( ( $Files | ForEach { $_.Length } ), 0.9 )
(If you are using PowerShell 3.0 or higher, you may be tempted to try using $Files.Length instead of ( $Files | ForEach { $_.Length } ). It won't work. The array $Files has a property called .Length, so PowerShell is going to use that, and give you the number of files in the array, instead of giving you an array of the .Length’s of the files in the array. So you have to loop through the array to pull out the lengths.)
Do you want to know the monthly payment on a 3-year $2,000 loan at 4.25% interest?
That's easy.
$XL.WorksheetFunction.Pmt( 0.0425 / 12, 36, -2000 )
If you need details on how to use the functions, open up Excel and use its Help. You are, by definition, on a machine with Excel installed.
That ends my two-part series of Math in PowerShell articles for now, but I like PowerShell and I like math, so there will probably be more in the future.
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.
Why not do it in Excel within PowerShell?
Everything in Excel is built on the same object models as everything else, and has all the same programmatical accessibility as everything else Microsoft these days. So we can just reach inside, grab it by its objects, and tell it what we want it to do. (That didn't come out right.)
Excel needs to be installed on the machine you are running this on, so you pretty much have to run it on your workstation. It is going to load an instance of Excel in the background, so it is going to use some computer resources and time. But we have computers with nothing better to do than what we tell them to do, and we're a little crazy.
First load the assembly so that PowerShell knows what we are talking about when we start speaking in Excelese.
[reflection.assembly]::LoadWithPartialName( "Microsoft.Office.Interop.Excel" )
Next load an Excel application into an object to do our bidding.
$XL = New-Object -ComObject Excel.Application
$XL.WorksheetFunctions holds the functions we want to use. To see the full list, pipe it to Get- Member.
$XL.WorksheetFunctions | Get-Member
398 functions available! Now we’ve got math coming out of our asymptotes!
Do you have a number that you need to round to the nearest multiple of 22.5?
Just use
$XL.WorksheetFunction.MRound( $X, 22.5 )
Do you want to know the size range that 80% of your file sizes fall into?
Try
$XL.WorksheetFunction.Percent( ( $Files | ForEach { $_.Length } ), 0.1 )
$XL.WorksheetFunction.Percent( ( $Files | ForEach { $_.Length } ), 0.9 )
(If you are using PowerShell 3.0 or higher, you may be tempted to try using $Files.Length instead of ( $Files | ForEach { $_.Length } ). It won't work. The array $Files has a property called .Length, so PowerShell is going to use that, and give you the number of files in the array, instead of giving you an array of the .Length’s of the files in the array. So you have to loop through the array to pull out the lengths.)
Do you want to know the monthly payment on a 3-year $2,000 loan at 4.25% interest?
That's easy.
$XL.WorksheetFunction.Pmt( 0.0425 / 12, 36, -2000 )
If you need details on how to use the functions, open up Excel and use its Help. You are, by definition, on a machine with Excel installed.
That ends my two-part series of Math in PowerShell articles for now, but I like PowerShell and I like math, so there will probably be more in the future.
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:
[math].GetMethods()
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. https://www.google.com/#q=MSDN+system.math+methods
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
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.
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.)
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
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.
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:
[math].GetMethods()
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. https://www.google.com/#q=MSDN+system.math+methods
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 )
Trigonometry
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()
Miscellaneous
::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
)
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.
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.
Form
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 [system.windows.forms.size] object as input, not a string. Other sources will tell you to create a new [system.windows.forms.size] 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".
$formRDP.Controls.Add($dgvServers)
$formRDP.Controls.Add($labelLoading)
Label
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"
DataGridView
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).
OnFormShown
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.
$formRDP.Refresh()
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" )
}
OnColumnHeaderMouseClick
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
}
OnDoubleClick
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.
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.
Form
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 [system.windows.forms.size] object as input, not a string. Other sources will tell you to create a new [system.windows.forms.size] 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".
$formRDP.Controls.Add($dgvServers)
$formRDP.Controls.Add($labelLoading)
Label
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"
DataGridView
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).
OnFormShown
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.
$formRDP.Refresh()
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" )
}
OnColumnHeaderMouseClick
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
}
OnDoubleClick
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.
Subscribe to:
Posts (Atom)