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

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.