Blog » 

PowerShell : Exporting multi-valued attributes with Export-Csv – how to tame the beast (SysAdmins, rejoice!)

July 28th, 2014, by

The Scenario:

You want to query AD, Exchange, SharePoint, etc. to quickly retrieve some basic, but really useful information about something really common. Examples: users, sites, etc. And of course you want to export these values to a CSV so you can sort, filter, aggregate, count, etc. in Excel or some similar tool.  One or more of the values that you’ll retrieve – usually the most important ones, in our experience – is not a single value, but instead, a set of values (e.g., a user belongs to multiple groups, or multiple distribution lists.) Easy, right? Not so much.

The problem:

PowerShell queries on fields/objects that naturally yield multiple results don’t return the values when you use the Export-Csv cmdlet, even when the values are displayed in the PowerShell console itself.

Example

In our sample (simple) Query, we’re looking for all of the Exchange mailbox “proxyaddresses” for a single specific user.

Result from the PowerShell console (using | fl for formatting)

Command run:

Get-QADUser test.user1 -IncludeAllProperties | select name, proxyaddresses | fl

Note how all the values we’re looking for are present.

072314_0147_PowerShellE1.png

Result using the exact same query with “Export-Csv”

Command run:

Get-QADUser test.user1 -IncludeAllProperties | select name, proxyaddresses | Export-Csv .testUser1.csv

Note how the multi-valued attributes (in this case, “proxyaddresses”) show up in the CSV. We only get “System.String[]”. Booo!

072314_0147_PowerShellE2.png

How to fix it: replace multi-valued attributes with Join functions

By replacing each multi-valued attribute with a join function, we can have Powershell create a single string, using any character we specify to concatenate the values. In my examples, example, I’m using semicolons.

Original command (w/o the join function)

Note: this is exactly the same command , using better user (“seth”) to illustrate results.

Get-QADUser seth -IncludeAllProperties | select name, proxyaddresses | Export-Csv .seth-nojoin.csv

Result: no values.

072314_0147_PowerShellE3.png

New command, replacing the attribute with a join function for that attribute

Get-QADUser seth -IncludeAllProperties | select name, @{Name=’proxyAddresses’;Expression={[string]::join(“;”, ($_.proxyAddresses))}} | Export-Csv .seth-all_proxyaddresses.csv

Result: all “proxyaddresses” values concatenated by semicolons.

072314_0147_PowerShellE4.png

(Note: All of the Excel screenshots that follow were prettied up using the “format as table” function after opening the CSV results, for easier review/consumption.)

Slightly more complex (and practical) Examples

  1. Query the Exchange Message tracking Log; focus in on a specific timeframe, and export entries for messages with sortable lists of recipients

    get-messagetrackinglog -EventID “RECEIVE” -Start “7/1/2014 3:59:00 PM” -End “7/4/2014 4:09:00 PM” | Select timestamp, eventid, Source,Messageid, MessageSubject, sender,@{Name=’recipients‘;Expression={[string]::join(“;”, ($_.recipients))}}, clientip, clienthostname, serverip, serverhostname, messageinfo | export-csv C:\temp\message_log.csv

    072314_0147_PowerShellE5.png

    This can be extremely useful if you’re trying to retire an SMTP relay service, for example -what messages are going through this server?

  2. Query all AD groups in a domain, list all the members of each group in a single concatenated field, and then use Export-CSV to review/manipulate results in Excel

    Get-Group -ResultSize ‘Unlimited’ | Select-Object -Property ‘Name’,’DisplayName’,’SamAccountName’,’GroupType’,@{Name=
    ‘members’;Expression={[string]::join(“;”, ($_.members))}} | export-csv C:\temp\group_members.csv

    072314_0147_PowerShellE6.png
  3. Query all users in a domain, and list each user’s group memberships – all of them – in a single concatenated field

    Get-ADUser -Properties * | Select ‘Name’,’DisplayName’,’SamAccountName’, @{Name=’MemberOf’;Expression={[string]::join(“;”, ($_.MemberOf))}}| export-csv C:\temp\users_with_all_groups.csv

    072314_0147_PowerShellE7.png

Credit: Part of this post contains source material from this article: PowerShell : Exporting multi-valued attribute via Export-Csv cmdlet

Share this article:

Author:

Categories: Managed IT Services

Tagged with:

Comments (28)

  2. badbanana said on

    your example,

    Expression={[string]::Join(“;”,($_.proxyaddresses))

    does not work.

    this one
    Expression={$_.proxyaddresses -join “;”}

    works.

    Reply ↓
  6. Adam Morris said on

    This allows me to export the proxyaddress attributes for all user within the given OU of “HQ, Users” in this example and separates them with a ;

    get-aduser -Filter * -SearchBase “ou=Users,ou=HQ,dc=tailspintoys,dc=co,dc=uk” -properties * | select-object samaccountname, @{“name”=”proxyaddresses”;”expression”={$_.proxyaddresses -join “;”}} | Export-Csv C:\test.csv

    Then this imports the created .csv back in..

    $admail=Import-CSV C:\test.csv
    Foreach ($a in $admail) {
    Set-ADUser $a.SamAccountName -Clear proxyaddresses
    $a.proxyaddresses -split “;” | % {Set-ADUser $a.samaccountname -Add @{proxyaddresses=$_ +” “}}#
    }

    I used these to copy attributes across to a new domain after using ADMT to migrate users and passwords. The client is using Office 365 and no longer wanted to maintain Exchange.

    Thanks for all your help!

    Reply ↓
  8. Karthi M said on

    Hi, Can anyone help me out with an issue I am running with for a month now. 🙁

    I wanted to export Multi-Value Properties such as MemberOf, ProxyAddresses, etc., I am able to do it either by using Join() or creating custom object array.

    The problem is I need these multi-value property exported in one cell in multiple lines in CSV.

    Is it possible ?

    Any positive answers much appreciated.

    Regards,
    Karthi

    Reply ↓
  9. Simone said on

    Hi Ben,

    that indeed fixes the issues I had with the transportlog in exchange. The problem I have is that I’ve got too many recipients and they’re cut.. I get something like this:
    ben@test.com;simone@test2.com;name.lastnam-
    And it ends it with a dash.. $FormatEnumerationLimit =-1 didn’t help. Thanks!

    Reply ↓
    • Piotr said on

      OK, I have found one solution:

      Import-Csv -Path C:\temp\message_log.csv |
      % {$row = $_; $_.Recipients.split(“;”)} |
      % {$row.Recipients=$_; $row} |
      Export-Csv C:\temp\message_log2.csv

      or maybe this one (not tested)

      Get-MessageTrackingLog -EventID “RECEIVE” -Start “7/1/2014 3:59:00 PM” -End “7/4/2014 4:09:00 PM” |
      % {$row = $_; $_.Recipients} | % {$row.Recipients=$_; $row} |
      Select Timestamp, EventId, Source,MessageId, MessageSubject, Sender,Recipients, ClientIp, ClientHostname, ServerIp, ServerHostname, MessageInfo |
      Export-Csv C:\temp\message_log.csv

      Reply ↓
      • Piotr said on

        The second one-liner throws error:
        “% : ‘Recipients’ is a ReadOnly property.”

        “Select” moved before % {} to make it work:
        Get-MessageTrackingLog -EventID “RECEIVE” -Start “7/1/2014 3:59:00 PM” -End “7/4/2014 4:09:00 PM” |
        Select Timestamp, Recipients, MessageSubject, Sender |
        % {$row = $_; $_.Recipients} |
        % {$row.Recipients=$_; $row} |
        Export-Csv C:\temp\message_log2.csv

        Reply ↓
  11. Chris Morley said on

    Hello Ben,

    First thanks for this very detailed explanation it was super helpful.

    I’m wondering if I can’t ask for a tip on how to use this exported data, specifically the Proxy addresses. We’ve found we need to export all distribution groups from our legacy exchange, remove them & then re-create them with the same attributes in O365.

    Long story short this has been a bit of a nightmare.

    I’m wondering if you know of a way to import the proxy address field exported above in such a way that I wont have to reformat it? Or failng that another approch to export the proxy addresses so that it will place nice with “Set-DistributionGroup -Identity $group.DisplayName -EmailAddresses”?

    Reply ↓
  13. Thomas said on

    Ben,
    Thanks for sharing. When using Export-Csv it is always helpful to use the -Encoding parameter to preserve any non ASCII character, e.g. -Encoding UTF8

    Reply ↓
  15. Michael McNally said on

    Copy pasting example 2 into powershell 4.0, I get the following value under the heading “members” in my CSV:

    System.Collections.ArrayList

    It would be great if there was a way to retrieve individual values from multivalued properties, but I don’t think this is it.

    Reply ↓
    • Michael McNally said on

      Revisiting this because it still doesn’t work for me at all, and I’m perplexed that others report that it does. Let’s look at the AD account for someguy, who has a half dozen proxyaddresses:

      get-aduser someguy | select name, @{Name =’proxyaddresses’;Expression={[string]::Join(“;”,($_.proxyaddresses))}}

      Returns this for proxyaddresses:
      Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

      I’ve tried ‘get-aduser -properties proxyaddresses’ and ‘get-aduser -properties *’, but no dice.

      The example shown as example 2 still is giving me the result reported earlier. Here is some sample output for one of my groups:
      Name : SCIT
      DisplayName : SCIT
      SamAccountName : SCIT525811860015973
      GroupType : Universal
      members : System.Collections.ArrayList

      Is there a special config needed for Powershell for this? I find it odd that others report this working, yet when I run the identical command, nope.

      Reply ↓
      • Michael McNally said on

        Found an answer that DOES work on a different blog:

        To split up group members, as shown in your Example 2, here is what does work for me:
        @{Name=‘Members’;Expression={$_.members -join “;”}}

        Your example shows:
        @{Name=‘Members’;Expression={[string]::join(“;”, ($_.members))}}

        This does not work for me at all. Powershell 4.0 on Windows 8.1.

        I’d be interested in understanding why the difference, as I encounter this problem frequently. It’s hard to customize to fit my own solutions without understanding why or when or which works.

        Reply ↓
  16. Nick T said on

    Just a note about Example 3

    “Query all users in a domain, and list each user’s group memberships – all of them – in a single concatenated field”

    It doesn’t pull the Primary group of the user (and will return blank if the user is only in one group). At least not in older versions of powershell.

    Reply ↓
  17. Liam said on

    What I am trying to figure out is how to turn this on its head and import-csv when you have the example above and then be able to use the multiple entry field. As an example:
    csv Test,Hi;helloheya

    $csv = import-csv c:\PathToCsv

    $csv.tst
    and then get an array to work with so it ends up looking like
    hi
    hello
    heya

    in order to perform say a foreach loop against each one.

    Reply ↓
  18. Andrew Ciccone said on

    Nice Article Ben,

    I’ve been working with this type of thing a lot lately. Very useful and very powerful when used correctly! I know it’s saved me tons of time! One thing that I would recommend when using Export-Csv is to use the -NoTypeInformation parameter which will spit out the same csv file but without the “#Type.bla.bla.bla” header information so you can save a step in having to delete it in the spreadsheet.

    Just a nice little tip I’ve learned while working with PowerShell. I hope this info come in handy in your PowerShell travels!

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *