locked
Powershell Column to Rows RRS feed

  • Question

  • Hiya,

    I want to change the output from my powershell cmdlet to a different format. But I cannot a way to get the output right. It looks like transposing with unique selections. But to get seperate columns for every UPN is driving me nuts. Does anyone know maybe how to do this? 

    $Groups =  get-adgroup -filter * -SearchBase "OU=Sub,DC=Company,DC=NL" -searchscope Subtree
        
     Foreach ($Group in $Groups) { 
            
            $members = Get-ADGroupMember -Identity $Group

            If (!($members)) { 

                $Properties = @{
                        GroupName = ($group.Name)
                        UserprincipleName = "No Members"
                }
                $ObjList += @(New-Object pscustomobject -property $Properties)
            } 
      
            Else {

                Foreach ($member in $members) {

                    $Properties = @{
                        GroupName = ($group.Name)
                        UserPrincipalName = (Get-aduser -Identity $member.SamAccountName | Select UserPrincipalName).UserprincipalName
                    }
                    $ObjList += @(New-Object pscustomobject -property $Properties)
                } 
            }
    }

    $Objlist | Out-File M:\Group_UPN.csv -append -force

    Example 1
    GroupName UPN
    Group1 D.Hendrix@company.nl
    Group1 S.Meatloaf@company.nl
    Group2 P.Omen@company.nl
    Group2 O.Havefun@company.nl
    Group2 W.Clit@company.nl

    Example 2
    GroupName UPN1 UPN2 UPN3
    Group1 D.Hendrix@company.nl S.Meatloaf@company.nl
    Group2 P.Omen@company.nl O.Havefun@company.nl W.Clit@company.nl

    Friday, December 7, 2018 1:32 PM

All replies

  • Please post code using the code posting tool provided. You also have failed to tell us what you want.  1 or 2?


    \_(ツ)_/

    Friday, December 7, 2018 1:53 PM
  • Is it possible that you're looking for the cmdlet Group-Object?  ;-)

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Friday, December 7, 2018 2:00 PM
  • Here is my best guess at what you want.

    Get-AdGroup -filter * -SearchBase 'OU=Sub,DC=Company,DC=NL'
        ForEach-Object{
            [pscustomobject]@{
                GroupName = $_.Name
                UserprincipleName = (
                    Get-ADGroupMember $_ |
                    Where-Object{ objectClass -eq 'User' } |
                    Get-AdUser |
                    Select-Object -ExpandProperty UserPrincipalName
                ) -join '|'
            }
        } |
        Out-File M:\Group_UPN.csv 
    


    \_(ツ)_/

    Friday, December 7, 2018 2:03 PM
  • Tipp: you can also to it with Excel and copy & paste and save your time

    Chris

    Friday, December 7, 2018 4:32 PM
  • While this script doesn't exactly meet your requirements, it will take your "Example 1" data (in a CSV) and produce the data in the "Example 2" format.

    $InputCSV = 'C:\temp\F.csv'
    $OutputCSV = 'C:\temp\FGrouped.CSV'
    
    $A = import-csv $InputCSV | Group-Object GroupName
    
    # get the maximum number of UPN values for a single Group
    $max = ($A | Measure-Object -max count).Maximum
    $tmplt =  [ordered]@{'Group' = ''}
    1..$max | foreach {$tmplt.Add("UPN$_", '')}
    
    $A | ForEach {
        $row = New-Object PSObject -Property $tmplt
        $row.Group = $_.Name    
    
        $count = 1
        ForEach ($G in $_.Group) {$row."UPN$count" = $G.'UPN'; $count++} # get each ITS-Number for this sAMAccountName-Gruppen
        
        $row | Write-Output
    } | Sort  'GroupName' | Export-CSV -Path $OutputCSV -NoTypeInformation
    
    ###### Sample data in F.csv
    $F = @"
    GroupName,UPN
    Group1,D.Hendrix@company.nl
    Group1,S.Meatloaf@company.nl
    Group2,P.Omen@company.nl
    Group2,O.Havefun@company.nl
    Group2,W.Clit@company.nl
    "@


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Friday, December 7, 2018 7:13 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, December 11, 2018 6:29 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Monday, December 24, 2018 5:48 AM