Bringing users and mailboxes together

  • Article

Now, I can't take credit for coming up with this solution, but thanks to Matt and Jon and Bruce for figuring out a great way to solve this problem!

That out of the way, what is the problem we're about to solve? Well, let's suppose you want to use powershell to get back a result-set that includes all of the mailboxes in the org, including details of their first name, last name, and alias.

Ok, let's give it a shot...

C:\>Get-Mailbox | fl firstname,lastname,alias

Alias : TestUser

Hmm, no firstname or lastname in that result. That's because these two properties are actually "AD" properties on the user rather than "Exchange" properties on the mailbox (see Ross' post on Exchange 2007 property sets for more details on the AD vs Exchange property set division).

That means you can't just do this either since it'll just show the other side of the results:

C:\>Get-User | fl firstname, lastname, alias

FirstName : Test

LastName : User

What we really need is a way to "join" these two output sets on some common characteristic. Since "Get-User" and "Get-Mailbox" are really just controlling the view (what properties are output) for the same underlying AD object, it should be really easy to do this.

A great way to handle this (and the basis for this solution) is the use of Powershell hashtables. Hashtables allow us to build up a "dictionary" of related data that we can then use to "join" the two datasources for a consolidated output.

This will break down into three easy steps:

1) Load all of the objects into a hash table.

Get-User -ResultSize Unlimited | Where { $_.RecipientType -eq 'UserMailbox' } | ForEach { $Users = @{} } { $Users[$_.SamAccountName] = $_ }

Explanation:

  • We're getting all of the users in the current recipient scope (not stopping after 1000 users). Note that your recipient scope defaults to the domain, not the forest.
  • We're dropping all but those that are of UserMailbox type (Note that this used to be called MailboxUser recipient type in case you're trying this against a beta build of Exchange 2007)
  • Then, we're dumping these mailbox objects into a HashTable called $Users, using SamAccountName property as the "key"

Notice the result of this HashTable creation after it's been done:

C:\>$Users

Name Value
---- -----
TestUser e12dom.local/Users/Test User

2) Build a new object with data from both sides, using the Hash Table and some Powershell object magic.

Get-Mailbox -ResultSize Unlimited |
ForEach {
New-Object psobject |
Add-Member -PassThru NoteProperty Alias $_.Alias |
Add-Member -PassThru NoteProperty FirstName $Users[$_.SamAccountName].FirstName |
Add-Member -PassThru NoteProperty LastName $Users[$_.SamAccountName].LastName
}

Once again, explanation:

  • We're getting all of the mailboxes in the current recipient scope (not stopping after 1000 users). Very similar to the above Get-User query except we're getting mailbox objects (and therefore mailbox properties) instead of user objects/properties.
  • We're creating a new object for each mailbox we find and adding it to the pipeline
  • Each new object we create has three properties: Alias, FirstName, and LastName.
    • We add "PassThru" switch to make sure the object keeps going on the pipeline
    • We source the Alias from the current (mailbox) pipeline.
    • We source the FirstName and LastName by drilling into the User object referenced in the Hash Table. This is our poor-man's "join" since we're joining on the unique SamAccountName associated with both the Mailbox and the User objects.

3) Do whatever you need with the output!

You now have a set of output objects with the properties you want. You can pipe this output to Export-CSV, reformat it on the screen, bulk address some email, etc... Isn't Powershell great?!

Updated 7/26/2007 - Bharat points out that if only I'd used the keyword dictionary (yes dictionary dictionary dictionary), he would have been better able to find this post. I'll even throw in IDictionary keyword for the really dedicated types. :)

Comments

  • Anonymous
    January 01, 2003
    Exchange 2007 has some very powerful cmdlets which can be used to generate fairly effective reports,

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    October 25, 2006
    Isn't this so much easier with csvde?