Powershell Objects–How to export-csv information coming from different cmdlets and with potentially multi-valued attributes
Hi all,
Since I keep forgetting where this great post is located, I’m just putting a bookmark here. This article is discussing about the different ways to populate a PowerShell Custom Object.
What can we use PowerShell Custom Object for ? Actually on Exchange for example (including Exchange Online), we sometimes need to have information on users and mailboxes that come from different cmdlets (like some info that you can get using “Get-Recipient” but not with “Get-Mailbox”, some other info you can get using “Get-User” but not with “Get-Recipient” nor “Get-Mailbox”, etc…), and you want to concatenate all these in a single object, making it easier to manipulate and export in a CSV eventually.
Below I’ll also walk through a concrete example about what I’m referring to above, but initially this blog post was just meant to put a bookmark here so that we can refer to it later when we need – that way I don’t have to ask myself on which browser or which computer did I put that information – Below is a link to Don Jones’ article about the many ways to create PowerShell custom objects, thanks Don !
Don Jones' PowerShell Object creation methods description
Title: Windows PowerShell: The Many Ways to a Custom Object
Link: https://technet.microsoft.com/en-us/library/hh750381.aspx
Author: Don Jones
Application of Don's article in an Exchange example:
I will just show 2 of the many methods that Don Jones is exposing in his article, because these 2 are my favourites but I encourage you to have a look on his article too…
Let’s just take a simple example where I want to have in a single PowerShell object that has information from 2 different cmdlets (Get-Mailbox and Get-Recipient), that I want to export in a CSV later along with other information (to add up a collection of Get-Mailbox / Get-Recipient, just do a loop looking like a “ForEach ($user in $usercollection) {$mailbox = Get-mailbox $user ; $recipient = Get-Recipient; <Put your object code sample like below here>; $ObjectCollection += $object})
Then to export all the collection just pipe $ObjectCollection to an Export-CSV cmdlet $ObjectCollection | Export-CSV C:\temp\YourCollection.csv
For simplicity and Quick Ref, I’m just showing it for a single mailbox/recipient …
Method #1 : Add-Member with NoteProperty and Value for each property and associated value
$Mailbox = Get-Mailbox User_Alias
$Recipient = Get-Recipient User_Alias
$object = New-Object –TypeName PSObject
$object | Add-Member –MemberType NoteProperty –Name Name –Value $mailbox.Name
$object | Add-Member –MemberType NoteProperty –Name EmailAddresses –Value ($mailbox.emailaddresses –join ";")
$object | Add-Member –MemberType NoteProperty –Name OrganizationalUnit –Value $Recipient.organizationalunit
$object | Export-CSV $env:userprofile\desktop\yourobject.csv
Quick explanation:
-> first we load the object and its mailbox properties on a variable
$Mailbox = Get-Mailbox User_Alias
-> second we load the object ant its recipient properties on another variable
$Recipient = Get-Recipient User_Alias
-> then we instantiate (=we "create") a new Powershell Object (aka PSObject) that we put in the $Object variable
$object = New-Object –TypeName PSObject
-> Finally we add the properties we want to this objects : the properties coming from the Get-Mailbox will be added from the $mailbox variable ($mailbox.property), and the properites coming from the Get-Recipient will be added from the $recipient variable ($recipient.property)
$object | Add-Member –MemberType NoteProperty –Name Name –Value $mailbox.Name
-> note the second one, where the -Value is like ($mailbox.emailaddresses -join ";") => that is in case you have a collection of non-string values, the "-join" function will concatenate these, and the separator specified immediately after will define the separator, and since it's in between double quotes, the whole concatenated item will be converted to Powershell String
$object | Add-Member –MemberType NoteProperty –Name EmailAddresses –Value ($mailbox.emailaddresses –join ";")
-> And to add other properties, from another cmdlet result, here from the Get-Recipient one, as easy as above, just add a property specifying $recipients.property on the next "-value"
$object | Add-Member –MemberType NoteProperty –Name OrganizationalUnit –Value $Recipient.organizationalunit
-> Finally, if you want to export your object to a CSV file (that will be a single-line because we just have one object in that example, see the text above using ForEAch to populate a variable with many objects): just pipe the variable to an Export-CSV, et voilà!
$object | Export-CSV $env:userprofile\desktop\yourobject.csv
(note: here the $Env:userprofile\desktop points to the current users's desktop – NOTE that if you did a "RunAs" of your powershell window from where you execute your lines, that $env:userprofile\desktop will refer to the profile of the user you are running your powershell window as.)
Method #2 : using a “hash table” (barbarian name for a sort of array that associate properties with values)
$Mailbox = Get-Mailbox User_Alias
$Recipient = Get-Recipient User_Alias
$properties = @{'Name'=$mailbox.Name;
'EmailAddresses'=($mailbox.emailaddresses –join ";");
'OrganizationalUnit'=$Recipient.organizationalunit}
$object = New-Object –TypeName PSObject –Prop $properties
$object | Export-CSV $env:userprofile\desktop\yourobject.csv
Knowing Method #1 above, that is relatively simple to understand :
We define each "Name" and "Value" of the "NoteProperty" that we added using "Add-Member -Membertype" all at once in a "hash table" (@{'Name1' = 'Value1'; 'Name2' = 'Value2', etc….}) – as a one-liner or a multiple-liner for better readability and flexibility, we put that hash table in a variable ($properties in the above example), and then we instantiate the object adding directly the variable containing the hash table :
$object = New-Object –TypeName PSObject –Prop $properties
And then if you need to export, same as the first method:
$object | Export-CSV $env:userprofile\desktop\yourobject.csv
That's pretty straightforward, simpler to write as you don't have to repeat the "Add-Member -MemberType NoteProperty -Name "Name1" -Value $variable.property" each time – cool eh !
NOTE: I just showed 2 of the methods to populate PSObjects because these are my favorites for now, but Don Jones shows more variants, and I encourage you to have a look on his post as well.
Have a great one PowerShell mates !
Sam