Sorting non-normalized multi-valued attribute results

Welcome Forums General PowerShell Q&A Sorting non-normalized multi-valued attribute results

This topic contains 7 replies, has 3 voices, and was last updated by

 
Participant
1 month, 1 week ago.

  • Author
    Posts
  • #120861

    Participant
    Points: 19
    Rank: Member

    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}

     

  • #120862

    Participant
    Points: 268
    Helping Hand
    Rank: Contributor

    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. 😉

  • #120865

    Moderator
    Points: 175
    Team MemberHelping Hand
    Rank: Participant

    Try something like:

    @{Label='Affiliation';Expression={$PSItem.extensionAttribute2 -split "," | Sort-Object}}
    
  • #120873

    Moderator
    Points: 175
    Team MemberHelping Hand
    Rank: Participant

    Just reread your question, you may need to change the split operator depending on your delimiter (space).

    @{Label='Affiliation';Expression={$PSItem.extensionAttribute4 -split " " | Sort-Object}}
  • #120876

    Participant
    Points: 19
    Rank: Member

    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

  • #120879

    Participant
    Points: 19
    Rank: Member

    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

  • #120885

    Moderator
    Points: 175
    Team MemberHelping Hand
    Rank: Participant

    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 
    Name        Case1 Case2 Case3 Case4 Case5 Case6
    —-        —– —– —– —– —– —–
    Terry Crews ABC                           CBA  
    stah06      ABC   ACB   BAC   BCA   CAB   CBA
    
    
    • #121902

      Participant
      Points: 19
      Rank: Member

      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

You must be logged in to reply to this topic.