Home / server / Questions / 998165
In Process
software is fun
Asked: 2020-01-09 11:17:25 +0800 CST

Trying to export AD with PowerShell and get the values for each ADPropertyValueCollection

  • 772

Some of my fields in my CSV file are showing up as ADPropertyValueCollection

Is there anyway to get all the values of the collection and store them into my CSV?

showInAddressBook
Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

The code I used is

Get-ADUser -Filter * -Properties * | export-csv -path c:\temp\userexport.csv
active-directory

2 Answers

  1. Please don't do -properties * unless you really need every property, which you apparently don't. It takes much longer to execute if you're dragging down every property in a user.

    You don't need the quotes between all of the properties in your Select statement. Also, you had a backtick in front of Name. The ::join in the sample didn't work for me either. This works:

    Get-ADUser jeff.besos -Properties memberof | Select Name,DisplayName,SamAccountName, @{Name='MemberOf';Expression={$_.MemberOf -join ';'}}

Name           : jeff.bezos
DisplayName    :
SamAccountName : jeff.bezos
MemberOf       : CN=!IDAMAdmins,OU=Admin Groups,OU=Domain Groups,DC=mydomain,DC=com;CN=!ExchangeAdministrators,OU=Admin Groups,OU=Domain
                Groups,DC=mydomain,DC=com

    These days I actually recommend exporting to JSON if you have multivalued attributes you're going to reprocess later. Obviously this works if you're querying multiple users as well.

    Get-ADUser jeff.bezos -Properties memberof | Select Name,DisplayName,SamAccountName,memberof | convertto-json -depth 100 | out-file C:\myuser.json
{
    "Name":  "jeff.bezos",
    "DisplayName":  null,
    "SamAccountName":  "jeff.bezos",
    "memberof":  [
                    "CN=!IDAMAdmins,OU=Admin Groups,OU=Domain Groups,DC=mydomain,DC=com",
                    "CN=!ExchangeAdministrators,OU=Admin Groups,OU=Domain Groups,DC=mydomain,DC=com",
                ]
}

    Then you import JSON from file into an object (it's a PSCustomObject) and process that. PSCustomObjects are handy for slicing and dicing.

    $user = get-content C:\myuser.json | ConvertFrom-Json
$user |fl

Name           : jeff.bezos
DisplayName    :
SamAccountName : jeff.bezos
memberof       : {CN=!IDAMAdmins,OU=Admin Groups,OU=Domain Groups,DC=mydomain,DC=com,
                CN=!ExchangeAdministrators,OU=Admin Groups,OU=Domain Groups,DC=mydomain,DC=com}
    • 2

  2. One way would be to | it to a select, and specify each property name. For multi-valued properties, do this: 

    Get-ADUser -Properties * | `
 select SamAccountName,@{Name="ShowInAddressBook";Expression={$_.showInAddressBook -join ";"}} | `
 Export-Csv -Path C:\Temp\UserExport.csv
    • 0