get aduser using powershell, Inconsistent Export file layout in CSV file, how to correct?

Question

I have a script to export the results but get inconsistent results which causes the final csv file to not have the same layout.

What happens is that 2 fields do not always get returned "mail" and "physicalDeliveryOfficeName"

This is the code I am using

Get-ADUser -Server "test.com" -Filter 'SamAccountName -like "123*"' -Properties EmployeeNumber,SamAccountName,EmployeeID,Name,DisplayName,GivenName,Surname,Title,StreetAddress,City,State,POBox,Postalode,EmailAddress,UserPrincipaName,mail,HomePhone,Company, Department, Description,Manager,Office,physicalDeliveryOfficeName,whenChanged,whenCreated, Modified, modifyTimeStamp | Export-csv C:\Users_List.csv

I have an SSIS package setup to import it but it fails fairly regularly due to this issue. The code is being run on a virtual desktop from home which employs file redirection which causes other issues for me from a file saving perspective but I don't think that is the problem here.

How do I ensure that the out is always output is always with the same field layout?

Thanks

Answer 1

If the properties differ on the objects being exported then the results will also be different. Creating your own object using Select-Object will put the same properties in every object even if the property is missing on the input object.

Try something like this (it's easier to read, too):

$Props = "EmployeeNumber,SamAccountName,EmployeeID,Name,DisplayName,GivenName,Surname,Title,StreetAddress,City,State,POBox,Postalode,EmailAddress,UserPrincipaName,mail,HomePhone,Company, Department, Description,Manager,Office,physicalDeliveryOfficeName,whenChanged,whenCreated, Modified, modifyTimeStamp"
$Props = $Props -replace "\s+", ""      # necessary to remove spaces in the string
$Props = $Props -split ","              # Make the string into an array

Get-ADUser -Server "test.com" -Filter 'SamAccountName -like "123*"' -Properties $Props | 
    Select-Object $Props | 
        Export-csv C:\Users_List.csv