Sorting non-normalized multi-valued attribute results

PowerShell Help
1

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:

Start-Transcript
$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}

 

2

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:

3

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

4

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]

5

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?

Brian

6

Update.

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.

Brian

7

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!
[pre]
$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]
[pre]
Name Case1 Case2 Case3 Case4 Case5 Case6

Terry Crews ABC CBA
stah06 ABC ACB BAC BCA CAB CBA

[/pre]

8

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.

 

Brian