Welcome to Day 5 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

After spending 4 days going over the basics, I thought it would be good to put it all together in a script. And since this blog is about using SQL Server, I’ll make it related to SQL Server. So, let’s get started.

Unless you’ve been camped out in the desert, in a tent with a hippo as your buddy, you’ve probably heard about the best practices for SQL Server relating to the Disk Partition Alignment. Today, we’ll create a script that examines the partition alignment on each partition for each physical disk on your system.

We’ll start off by getting various properties from the Win32_DiskPartition class. The properties that I’m interested in from this class are DiskIndex, Index, BootPartition, Size, StartingOffset, and BlockSize. We can get all of these by pipelining the results from the Get-WMIObject cmdlet to the Select-Object cmdlet:

Right off, we can see that this output is a listing, not in tabular format. It sure would look nicer if the output was formatted as a table. Wait a second… Format? Table? Verb-Noun? Yep, we’ll use the Format-Table cmdlet to change this:

Depending on your system, the next thing you might notice is that the results are not ordered, so let’s order it by the DiskIndex and the Index properties before pipelining to the Select-Object cmdlet:

And now, you can see that there is a LOT of white space between the columns – PowerShell spreads out the columns to fill the screen (if it’s not spread out on your screen, maximize the PowerShell screen and run this again). If you run Get-Help Format-Table, you can see that there is an argument named AutoSize. When this is used, you get the data in a nicer format with each column of data automatically sized based upon the results.

As you look at the results, you think it sure would be nice to just display these numbers in GB/MB/KB, with commas and right-aligned to make them easier to read. If you take another look at Get-Help Format-Table, you notice that you can use a hash table to add calculated properties and to specify headings. So, let’s build a hash table and apply it to the Format-Table cmdlet:

I heard that “HUH?”! Okay, let’s pause for a minute and explain this hash table. If you recall from day 2, a hash table is a table of Name-Value pairs. We can see this by executing the command “$b” to see the contents of this hash table:

Here, we have sets of Name-Value pairs. The left side of the “=” becomes the “Name”, and the contents of the script block on the right side become the “Value”. And yes, the Value for the Expression must be a script block. Now, about those Expression properties with a Value that has “{0:N3}”. The initial 0 (before the colon) represents the index number of the item being formatted in that script block. In a zero-based language, this is the first (and in our case, only) item. The “N” represents the type of formatting to be applied; N is for Numeric. The final number is the number of decimal places to be displayed. The “-f” is the format parameter, followed by the value that we want to format. Other types of formatting can be found at //msdn.microsoft.com/en-us/library/dwhawy9k.aspx (other numeric formatting) and //msdn.microsoft.com/en-us/library/fbxft59x.aspx (other formatting).

There are some additional things to point out in the hash table. Firstly, several of the Expressions are performing calculations. Note that PowerShell knows about GB/MB/KB, and I was able to use them directly in the calculation without having to do a “Value/1024/1024/1024” or calculate the exact bytes for a MB/GB. Also note that by using the hash table, I no longer need to use the Select-Object cmdlet to get just the properties that I want – the hash table is doing this for me.

This is almost perfect… almost. It would be nice to have the drive letter for the partition to the output and to tell us if the offset is an interval of 64KB. We can get from the Win32_DiskPartition class to the drive letters in the Win32_LogicalDisk class via the Win32_LogicalDiskToPartition class.

To retrieve the drive letter, we needed the __PATH property from the Win32_DiskPartition class. In the hash table, the “Drive” value has a corresponding expression that is a call to a function, which passes the __PATH property into the function. The function utilizes this to go through the Win32_LogicalDiskToPartition class to get to the Win32_LogicalDisk class and finally returns that DeviceID. Since the script block in the hash table can execute any command, we utilized that to call the function. To indicate if the offset is an interval of 64KB, an IF statement was utilized in the Expression to check if the modulo of the StartingOffset is 64kb and to return either Yes or No.

One drawback to using Format-Table is that you cannot pipeline its output to use with any of the Export cmdlets. Fortunately, the Select-Object cmdlet also supports a hash table, and it can be pipelined to other cmdlets. However, its hash table can only support just the name and expression elements. So we can modularize this code even further:

Notice that two hash tables were required. The first, in the Get-PartitionAlignment function, is not put into a variable but is actually a part of the Select-Object cmdlet (you can work with hash tables either way). Additionally, this hash table does not specify the alignment when used with the Select-Object cmdlet – if you try, you will get an error. However, if you don’t use the alignment with the Format-Table, all of the numbers will be left justified, looking, well, awful. So the Format-Table uses a separate hash table. Since the calculations are all performed in the function, the hash table used for Format-Table only carries the properties through for formatting purposes.

After the object has been displayed, the script then saves the object into a delimited text file, utilizing the Export-CSV cmdlet. The –Delimiter argument lets me specify the pipe (|) as the delimiter. This utilizes the PSDrive $ENV for the Windows environment, gets the temp directory for the current user, and creates the export file in this directory. Finally, Notepad is called to open up the exported file.

For the last step, let’s run this script from a saved file. Save the script (I named it GetPartitionAlignmentInfo.ps1).  In the PowerShell command window, enter

and run the script. The results are returned formatted to the screen and sent to an export file, and the file is opened in NotePad (so you can copy/paste into Excel to show which drives are not properly set up).

Well, this was a big day, but now we have a script that can check the partition alignment offset for all partitions on all disks on a system. This script covers most of the principals introduced so far during this week. This script can be downloaded from my Code Library at //blog.waynesheffield.com/wayne/code-library/powershell/powershelldrive-partition-information/.