locked
Create a CSV with security group name as column headers and members as rows. RRS feed

  • Question

  • I cannot figure out how to format data the way that I need it in powershell. what I want is a CSV with AD security group names as column headers and then the members as rows.

    Here is what I have so far:

    $list = @("ExternalCitrixDefault","ExternalUserGroup")

    $items = foreach ($item in $list) {
    Get-ADGroupMember -Identity $item | select @{Label=$item;Expression={$_.Name}}
    }
    $items | ft  

    Here is what I want:

    SecurityGroup1                           SecurityGroup2                                  SecurityGroup3

    user1                                           user1                                                    user1

    user2                                           user2                                                     user2

    here is what I get:

    Securitygroup1

    user1

    user2

    securitygroup2

    user1

    user2

    securitygroup3

    user1

    user2


    • Edited by Caleb Terry Wednesday, February 14, 2018 2:54 PM
    Wednesday, February 14, 2018 2:51 PM

Answers

  • If you want to format your data in a certain way, you have to present it to PowerShell in the way it expects it. You need to create an object that has a property for each group, holding the username for that group..

    When adding these objects to an array, PS knows what to do with it:

    $list = @("ExternalCitrixDefault","ExternalUserGroup")
    
    
    # Prepare AD data variables
    $data = New-Object PSCustomObject
    $maxcount = 0
    
    # Get all the Userdata from AD
    foreach ($item in $list) {
        $data | Add-Member -MemberType NoteProperty -Name $item -TypeName Array -Value ""
        $data.$item = Get-ADGroupMember -Identity $item | Select-Object -Property Name
    
        # Keep track of the maximum itemcount
        if ($maxcount -lt $data.$item.count) {
            $maxcount = $data.$item.count
        }
    }
    
    
    # Prepare the Output Array
    $output = @()
    
    # Loop through all the lines
    for ($i=0; $i -lt $maxcount; $i++) {
        # Prepare the Object to hold the line of Users
        $userline = New-Object -TypeName PSCustomObject
    
        # Fill the Userline Object with values for line $i
        foreach ($item in $list) {
            $userline | Add-Member -MemberType NoteProperty -Name $item -TypeName Array -Value $data.$item[$i].Name
        }
        
        # Add the userline to the Output Array
        $output += $userline
    }
    
    
    # Display the Output Array
    $output  | select -Property $list |ft -AutoSize
    
    
    $items = foreach ($item in $list) {
    Get-ADGroupMember -Identity $item | select @{Label=$item;Expression={$_.Name}}
    }
    $items | ft  


    • Marked as answer by Caleb Terry Tuesday, February 20, 2018 7:57 PM
    Wednesday, February 14, 2018 4:59 PM

All replies

  • If you want to format your data in a certain way, you have to present it to PowerShell in the way it expects it. You need to create an object that has a property for each group, holding the username for that group..

    When adding these objects to an array, PS knows what to do with it:

    $list = @("ExternalCitrixDefault","ExternalUserGroup")
    
    
    # Prepare AD data variables
    $data = New-Object PSCustomObject
    $maxcount = 0
    
    # Get all the Userdata from AD
    foreach ($item in $list) {
        $data | Add-Member -MemberType NoteProperty -Name $item -TypeName Array -Value ""
        $data.$item = Get-ADGroupMember -Identity $item | Select-Object -Property Name
    
        # Keep track of the maximum itemcount
        if ($maxcount -lt $data.$item.count) {
            $maxcount = $data.$item.count
        }
    }
    
    
    # Prepare the Output Array
    $output = @()
    
    # Loop through all the lines
    for ($i=0; $i -lt $maxcount; $i++) {
        # Prepare the Object to hold the line of Users
        $userline = New-Object -TypeName PSCustomObject
    
        # Fill the Userline Object with values for line $i
        foreach ($item in $list) {
            $userline | Add-Member -MemberType NoteProperty -Name $item -TypeName Array -Value $data.$item[$i].Name
        }
        
        # Add the userline to the Output Array
        $output += $userline
    }
    
    
    # Display the Output Array
    $output  | select -Property $list |ft -AutoSize
    
    
    $items = foreach ($item in $list) {
    Get-ADGroupMember -Identity $item | select @{Label=$item;Expression={$_.Name}}
    }
    $items | ft  


    • Marked as answer by Caleb Terry Tuesday, February 20, 2018 7:57 PM
    Wednesday, February 14, 2018 4:59 PM
  • Hi,

    Did you have a chance to check if this solves the formatting question?

    Thanks

    Monday, February 19, 2018 8:20 AM
  • Yes I did and thank you for the help! I am still going through the growing pains of learning powershell formatting.
    Tuesday, February 20, 2018 7:57 PM