PowerShell Hash Tables; Combine data from multiple modules.

If you use PowerShell long enough you will inevitably need to combine data from more than one module into a report. As an example, My co-worker ran into a situation where she needed to show mailbox sizes by city. Exchange doesn’t have the city but we know Active Directory does. How do you combine the information from the Exchange Module with the information from the Active Directory Module?

SQL, SharePoint, Lync, pretty much all MS server products have a module at this point. The examples below are Exchange and AD but you can use the technique to combine data into a report from any or all of them. First get the data you need from the Exchange module; Get-MailboxStatistics youremailaddress|fl totalitemsize should do the trick.

We know that Active Directory has our address in it. 

How in the world do we combine the data? There are lots of ways to do this programmatically but one of the easiest is to use 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 going to store a key (the name of our column) and a value (the results of another command) in the array and then display only the results. It sound complicated but trust me; it is easy once you get the hang of it.
The trick with hash tables in PowerShell is to get the syntax right. @{ <name> = <value>; [<name> = <value> ] is the formula. I know it looks complicated but if we plug in the data we’re looking for in, it will make more sense.
Get-MailboxStatistics dt208703|fl totalitemsize,@{Name=”City”;expression={(Get-ADUser -Identity dt208703 -Properties city).city}} see that comma between our first command and the @ that starts our hash table? You need a comma separator for each new hash table in your command. English teachers may be trying to get rid of the comma in written language but if you forget them in programming you’ll be sorry.

So what’s up with the ().city in our hash table? The syntax didn’t say anything about that. Turns out it is just some formatting. Who remembers grade school math; what does (3×2)+6 mean? For those of us that are a little rusty, it means multiply 3 and 2 and add 6 to the answer; the () mean do this first. Same thing in programming languages (they’re all just subsets of Algebra). So do this first (Get-ADuser –Identity dt208703 –properties city) and then display only the value named city. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s