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

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