Filter multivalue property field for multiple objects and export to csv

Question

need to get all ad users with a filter on following properties and then export to csv:

Get-ADUser -filter *  -Properties sn, givenname, samaccountname, employeenumber, employeeID,departmentnumber

In this cas all default properties is also included. So i tried this:

Get-ADUser -filter *  -Properties sn, givenname, samaccountname, employeenumber, employeeID,departmentnumber | select sn, givenname, samaccountname, employeenumber, employeeID,departmentnumber

Fine. This is what i want. Now the problem. When i run this with the export-csv option there is an error in the departmentnumber column.

Get-ADUser -filter *  -Properties sn, givenname, samaccountname, employeenumber, employeeID,departmentnumber | select sn, givenname, samaccountname, employeenumber, employeeID,departmentnumber | Export-Csv filexyz.csv

Because this is multivalue property it looks like this in the csv file: (i delete all user specific data)

"sn","givenname","samaccountname","employeenumber","employeeID","departmentnumber" ,"Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"

In CLI Powershell it looks like this: (i delete all user specific data)

sn : givenname : samaccountname : employeenumber : employeeID : departmentnumber : {ONR1200402}

Any idea how to solve this issue for exporting a multivalue property to csv? Thanks for your help.

Answer 1

Since your departmentnumber property is a collection type you could try to -join all values together via property expression feature preparing data to Export-CSV.

Like so:

Get-ADUser -filter *  -Properties sn, givenname, samaccountname, employeenumber, employeeID,departmentnumber | 
    Select-Object sn, givenname, samaccountname, employeenumber, employeeID,@{Name ='departmentnumber';Expression ={$_.departmentnumber -join ';'}} | 
        Export-Csv filexyz.csv

Note that I use semicolon (;) in my example to join values from departmentnumber collection.