In PowerShell, how can I combine the results of two teams that have a 1-to-1 relashionhip?

Question

This specific example is Get-User and Get-Mailbox (Exchange 2010). Get-User returns some of the columns that I need, and Get-Mailbox returns some others. It’s hard for me to understand how I can combine the results of these two into one table with the results of both.

Get-User -Filter "..." | Get-Mailbox -Filter "..." 

How to take the results of a team similar to the one above and turn it into results similar to the ones below?

 FirstName LastName Alias CustomAttribute1 --------- -------- ------ ---------------- Bob Smith bsmith Example Johnny NoMail Adam Blye ablye Has a Mailbox 

Note that FirstName and LastName are not returned by Get-Mailbox, and, conversely, Alias ​​and CustomAttributes are not returned from Get-User. Not every user has a mailbox, so sometimes part of the columns will be zero. But I have damn time figuring out the best way to bring back a combo table like this.

Answer 1

Get users, save each user in a variable, get a mailbox for each user, and then create a new object with properties from both variables

 Get-User -Filter ... | Foreach-Object{ $user = $_ $mbx = Get-Mailbox $user New-Object -TypeName PSObject -Property @{ FirstName = $user.FirstName LastName = $user.LastName Alias = $mbx.Alias CustomAttribute1 = $mbx.CustomAttribute1 } } 

Answer 2

I am creating a custom object that may be redundant, but this is the easiest way I've found.

Here is a sample code to play. Let me know if it caused any problems or additional questions:

 $outputCollection = @() $users = Get-User -Filter "..." $mailboxes = Get-Mailbox -Filter "..." $users | Foreach-Object { #Associate objects $userObject = $_ $mailboxObject = $mailboxes | Where-Object {$_.Name -eq $userObject.Name} #Make a combined object $outputObject = "" | Select Name, UserAttribute, MailboxAttribute $outputObject.Name = $userObject.Name $outputObject.UserAttribute = $userObject.UserAttribute $outputObject.MailboxAttribute = $mailboxObject.MailboxAttribute #Add the object to the collection $outputCollection += $outputObject } $outputCollection 

Another option that should work is called computed properties:

 Get-User -Filter "..." | Select Name, UserAttribute, @{Name="OtherAttribute"; Expression={(Get-Mailbox $_.Name).MailboxAttribute}} 

... note that this will result in a new Get-Mailbox command for each entry, which potentially increases the execution time

Answer 3

Thanks guys. I spent a lot of time figuring out my own problem, and your code helped me get it right. I needed to find all the calendars if the default account was not set. Below is what I needed to use.

 Get-Mailbox | ForEach-Object{ $user = $_ $calPerms = Get-MailboxFolderPermission $user":\calendar" -User Default | Where-Object {$_.AccessRights -eq "none"} New-Object -TypeName PSObject -Property @{ Name = $user Permissions = $calPerms.AccessRights Users = $calPerms.user } } 

Answer 4

One liner to get FirstName, LastName, Alias ​​and CustomAttribute1:

 Get-User | Select Firstname, Lastname, @{Name="Alias"; Expression={(Get-Mailbox $_.Name).Alias}}, @{Name="CustomAttribute1"; Expression={(Get-Mailbox $_.Name).CustomAttribute1}}