Sorting non-normalized multi-valued attribute results

Is there a way to sort the values of a non-normalized, multi-valued attribute from AD so that they appear in the same Order?

I am running a query in AD using get-aduser. One of the attributes I am returning is a multi-valued attribute. It seems there is no particular order that values are input into this attribute. I am exporting all returned results into a CSV file then open in Excel for some further manipulation. The multi-valued attribute is converted “Text to columns” using the delaminated “,” separator. When this is done I can see a multitude of combinations of order for the values. Example:

Possible values are: W X Y and Z

Combinations may be: “X W Z Y” , “X Z W Y” , “Z Y X” , “Z” , “Y X” , etc.

I would like to perform my search, then -expandproperty on the multi-valued attribute and sort the results so that all values of W, X,Y, X are in the same order. When I run text to columns in excel it will be easier to filter.

My simple line is this:

$csv = Import-Csv C:\Temp\VPNUsers.csv

foreach ($c in $csv.username) {get-aduser $c -Properties * | select name,givenname,sn,mail,@{label=“Affiliation”;expression={$_ | select -ExpandProperty extensionattribute2}},employeetype,department,company | Export-Csv C:\Temp\VPNUsersAffiliation_revised.csv -Append -NoTypeInformation}


Hmmm … maybe I got this wrong, but why don’t you sort your extensionattribute2 in your calculated expression?

BTW: You should format your code as code. That’d prevent unwanted line breaks. :wink:

Try something like:
@{Label=‘Affiliation’;Expression={$PSItem.extensionAttribute2 -split “,” | Sort-Object}}

Just reread your question, you may need to change the split operator depending on your delimiter (space).
[pre]@{Label=‘Affiliation’;Expression={$PSItem.extensionAttribute4 -split " " | Sort-Object}}[/pre]

That was it. The split and then sort object in the expression statement worked. One ore question…

Is there a way to automatically output each value into a separate column rather than suing excel to separate into columns?



The sorting works but the output to csv falls short in the resulting CSV file. For instance, when an identity has 3 of the possible 5 values. (Not all identities have all possible values) Column 1, once the attributed is sorted, has a vast majority of “W” values. But in the case where an Identity does not contain a “W” value, the first sorted value for that identity is placed in column 1 and that may be “Y”. So I am thinking that if I can say something to the effect of IF value=W THEN put in column 1 > ELSE IF value=x THEN put in column 2, and so on…

Does that make sense? BTW: sorry for not posting the code as code in original post.


I am sure there is a better way to do this (this isn’t scalable), but this worked for the small sample.
Hope it helps!
$users = ‘crews2’,‘stah06’ | Get-ADUser -Properties extensionAttribute2 |
Select-Object name, @{N=‘Affiliation’;E={$PSItem.extensionAttribute2 -split " " | Sort-Object}}

$final = @()

foreach ($user in $users) {
$final+= [psCustomObject]@{
Name = $user.Name
Case1 = foreach ($aff in $user.Affiliation) { if ($aff -eq ‘ABC’) {‘ABC’}}
Case2 = foreach ($aff in $user.Affiliation) { if ($aff -eq ‘ACB’) {‘ACB’}}
Case3 = foreach ($aff in $user.Affiliation) { if ($aff -eq ‘BAC’) {‘BAC’}}
Case4 = foreach ($aff in $user.Affiliation) { if ($aff -eq ‘BCA’) {‘BCA’}}
Case5 = foreach ($aff in $user.Affiliation) { if ($aff -eq ‘CAB’) {‘CAB’}}
Case6 = foreach ($aff in $user.Affiliation) { if ($aff -eq ‘CBA’) {‘CBA’}}

$final | FT -AutoSize [/pre]
Name Case1 Case2 Case3 Case4 Case5 Case6

Terry Crews ABC CBA


Thanks Wes. I think after playing around a bit with this, I have an idea of what I need to do. This helped a lot.