If you use PowerShell long enough you will inevitably need to combine data from more than one module, method, or object into a report of some kind. As an example, I ran into a situation where I needed to show mailbox sizes by city. Exchange doesn’t have the city attribute, but we know Active Directory does.
First we will get the mailbox size from the Exchange module; Get-MailboxStatistics user@domain.com|FL TotalItemSize should do the trick.
We know that Azure Active Directory has our address in it; Get-MsolUser -UserPrincipalName user@domain.com|Select City -ExpandProperty City will show the city name to us.
I’ve witnessed people resorting to complicated procedures to combine the output from two different modules. Copying and pasting the data from each module into Excel seems to be very popular. There are many problems that can occur with these types of solutions. The best way to accomplish our goal is to use a construct called a Hash Table.
What’s a hash table? In the simplest terms it is an array (a spot in memory) that stores key/value pairs. In our case, we are going to store a key (the name of our column) and a value (the results of a command) in the array. It sounds complicated, but is actually quite simple once you get the hang of it. The trick with hash tables in PowerShell is to get the syntax right. @{Name = “Name of Column”; {Expression = {Value, or Command}} is the formula.
To create the hash table from our example the command is: Get-MailboxStatistics user@domain.org|FL TotalItemSize, @{Name = “City”; Expression={Get-MsolUser -UserPrincipalName user@domain.org|Select City -ExpandProperty City}} We get the mailbox size and then insert a hash table that contains the city. The comma in between TotalItemSize and the @ symbol that starts our hash table is what tells PowerShell this is a new column of output.
Keep in mind this technique works with any number of PowerShell modules. SQL, SharePoint, Lync, Teams, most Microsoft server products and even many third party Windows based programs have a PowerShell module at this point.
techbloggingfool.com 