Exporting multivalued attributes with Export-CSV cmdlet



In the previous blog post we looked at how to retrieve values of user properties/attributes from Active Directory. We briefly even touched on how to retrieve group membership of users, and how to output this information in different formats, such as the distinguished name or the common name of the groups. In this blog post we will look at how to work with multivalued attributes. As was evident in the previous blog post, group membership is such an attribute, because a user can be member of more than one group.

There are a few attributes that can have multiple values, and exporting these to a CSV file isn’t as straightforward as exporting a single-valued attribute. Export-Csv converts objects into a series of comma-separated (CSV) strings and saves the strings in a CSV file. Converting single-valued objects into strings works fine, but Export-Csv is not able to convert multivalued attributes into strings, unless you specify how it should retrieve those values.

1. Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

This command

get-aduser fsinatra -properties name, memberof | select name, memberOf | export-csv “c:\temp\users.csv” -notypeinformation -Encoding UTF8

will yield the following result
multivalue1
Microsoft.ActiveDirectory.Management.ADPropertyValueCollection is not quite the output we were looking for 🙂
But what happens, is that the memberof attribute consists of a collection of values, rather than a single value. And since you did not specify how to retrieve those values, you are just told that a collection of values is stored in the memberof attribute.

Since name is a single-valued attribute, no special considerations have to be taken into account. But memberof is a multivalued attribute, because a user can be a member of more than one group, therefore we can not retrieve it and export it to a csv file, without actually specifying how to retrieve the different values.

2. Properly exporting multivalued attributes with Export-CSV

Lets look at how to properly export a multivalued attribute to a csv file

get-aduser fsinatra -properties name, memberof | select name, @{name=”MemberOf”;expression={$_.memberof -join “;”}} | export-csv “c:\temp\users.csv” -notypeinformation -Encoding UTF8

What does this part of the command do

@{name=”MemberOf”;expression={$_.memberof -join “;”}}

2.1 First you told, with the @ symbol, that the property you are retrieving is an array, which means it contains multiple values. Then you gave the property a name/label (you can name it anything you like). This will be the header of the column in the CSV file

@{name=”MemberOf”;

2.2 Then you provide an expression; a script block where you tell the PowerShell cmd-let what to fetch for you. Here you want it to fetch the values for the memberof attribute of the specified user(s)

expression={$_.memberof

2.3 In the end, you specify how the different values that are retrieved by the command, will be separated. The separator can for example be a semi-colon, a comma, or nothing. Here we are using a semi-colon. If you don’t specify the join operator, a space will by default be inserted between each value you retrieve.

-join “;”}}

Getting back to the complete command, we can see that we first defined that the attribute is an array, then we named the property, next told it to fetch the values of the memberof attribute for the specified user(s) and finally separated the values with a semi colon (in the end we also told export-csv to exclude type information, and use UTF8 encoding, but those are optional parameters)

get-aduser fsinatra -properties name, memberof | select name, @{name=”MemberOf”;expression={$_.memberof -join “;”}} | export-csv “c:\temp\users.csv” -notypeinformation -Encoding UTF8

3. Outputting a different attribute than the default one for AD objects

But what if you want to provide the output to the CSV file in a different format than the distinguished name of the groups, what if you rather want to output the name attribute of the groups, to the CSV file  instead?

get-aduser fsinatra -properties name, memberof | select name, @{n=’MemberOf’; e= { ( $_.memberof | % { (Get-ADObject $_).Name }) -join “,” }} | Sort-Object -Property Name | export-csv “c:\Temp\Users.csv” -notypeinformation -Encoding UTF8

So what does this command do
3.1 Defines that the attribute is an array, then gives it a name

@{n=’MemberOf’;

3.2 Starts the expression, but then tells that for each (% is alias for foreach-object) value retrieve the objects name. Next, join the different values by a comma, and at the end it is told to use the name attribute of the object when sorting the different values

e= { ( $_.memberof | % { (Get-ADObject $_).Name }) -join “,” }} | Sort-Object -Property Name |

Since a group is an ADobject too, you can use this portion to retrieve a specific attribute for that object

{ (Get-ADObject $_).Name })

4. Other multivalued properties than Group membership

What if we are working with another multivalued property, and not group membership. What if you are working with the proxyaddresses attribute, and want to retrieve specific proxyaddresses for all users in a specific OU

The following command will retrieve all proxyaddresses stored at the number 1 position for all users in the Users OU

get-aduser -searchbase “ou=users,dc=contoso,dc=com” -searchscope 1 -filter * -properties name, proxyaddresses | select name, @{L=’ProxyAddresses’; E={$_.proxyaddresses[0]}} | export-csv “c:\temp\users.csv” -notypeinformation -Encoding UTF8

If you want all addresses at the number 2 position, define it instead (and so on)

@{L=’ProxyAddresses’; E={$_.proxyaddresses[1]}}

But what if you want only a specific type of ProxyAddress, such as for example X400.
Then you can use this command

get-aduser -searchbase ‘ou=users,dc=contoso,dc=com’ -searchscope 1 -filter * -properties name, proxyaddresses | select name, @{l=’X400′;e={ $_.proxyAddresses | ?{ $_ -Like “*x400*” } }} | export-csv “c:\temp\users.csv” -notypeinformation -Encoding UTF8

? is the alias for Where-object, so what you are telling the command is that fetch all proxyaddresses, but output only the ones where the following string (X400) is part of the value (in any manner). For the users who don’t have an X400 proxyaddress, it will output nothing.

Following script will retrieve values for different properties and all X400 proxyaddresses (only) for all users in the Users OU, and store it in a csv file

import-module activedirectory
get-aduser -searchbase ‘ou=users,dc=contoso,dc=com’ -searchscope 1 -filter * -properties GivenName, SN, Description, Userprincipalname, Office, Samaccountname, Title, Emailaddress, Company, Department, Officephone, proxyaddresses | select GivenName, SN, Description, Userprincipalname, Office, Samaccountname, Title, Emailaddress, Company, Department, Officephone, @{l=’X.400 EmailAddress’;e={ $_.proxyAddresses | ?{ $_ -Like “*x400*” } }} | export-csv “c:\UserInformation.csv” -notypeinformation -Encoding UTF8

This entry was posted in Active Directory, Microsoft, Powershell, Windows Server, Windows Server 2008R2 and tagged , , , , . Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback.

7 Comments

  1. jay
    Posted October 17, 2015 at 19:37 | Permalink

    Fantastic article. This helped me figure out my own issue after my colleague suggested I investigate expressions.

  2. Dave
    Posted October 19, 2015 at 17:37 | Permalink

    sorry…but this doesn’t work. Nice detail, and good for helping to learn…but it doesn’t actually give you the value that’s in the multi-valued string.

    • Posted December 10, 2015 at 11:58 | Permalink

      Hi Dave,

      My apologies for the late reply, its been quite hectic lately. But if you look at this blog post http://shabaztech.com/retrieving-user-properties-from-active-directory/, you will see that this method works just fine.

      I also have scripts based on the method outlined here running in several production environments, and they are all working just fine.

      Best Regards,
      Shabaz

    • Art Alexion
      Posted January 20, 2017 at 20:46 | Permalink

      Worked perfectly for me — plus it helped me understand how and why.

      Thanks.

  3. Posted December 18, 2015 at 07:58 | Permalink

    HI, I have a code Get-ADUser -Filter * -SearchBase ‘DC=ju,DC=com’ -Properties company,proxyaddresses | select company, @{L=’ProxyAddress’; E={$_.proxyaddresses -join”; “}} RESULT is column Company (two char) and Column proxyaddresses (there are all values). I need only value SMTP:xxx@xxx

  4. Simon
    Posted February 19, 2016 at 13:02 | Permalink

    Great insight! Amazing job, it helped me alot to create my own script. Thanks.

  5. NewToPowerShell
    Posted June 21, 2016 at 08:06 | Permalink

    Nice sharing with all detailed information. Solved all my frustrations…

    Thanks for the sharing.

Leave a Reply

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

Your email address will never be published.

Captcha * Time limit is exhausted. Please reload the CAPTCHA.