Export Active Directory Users to Excel Using Powershell-CodePudding

I am trying to write a script that exports users of each AD group to Excel. In Excel, I will have one workbook, but each group will have a separate sheet. However, I cant seem to get the script to work correctly. What am I doing wrong?

 ''''
        $A = "group A"
        $B = "group B"
        $C = "group C"
        $D = "group D"
        $E = "182"
        $F = "182Basic3"
        $G =  "A182Robots"
        $H = "501"
        $I = "601"
        $J = "765"
        $K = "S8808"

        $groups = "$A", "$B", "$C", "$D", "$E", "$F", "$G", "$I", "$J", "$K"

        $fileName = "C:\users\rqw82758\Desktop\GroupMemberShip.xlsx"

        foreach ($group in $groups) {
           Get-ADGroupMember $group | select samaccountname, name, @{n='GroupName';e={$group}}, @{n='Description';e={(Get-ADGroup $group -Properties description).description}}



        $A| Export-Excel $filename -Autosize -AutoFilter -Append -WorksheetName "A136 Basic"
        $B| Export-excel $filename -Autosize -AutoFilter -Append -WorksheetName "A151 Basic"
        $C| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A154 Basic"
        $D| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A182 Basic"
        $E| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A182 Basic2"
        $F| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A182 Basic3"
        $G| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A182 Robots"
        $H| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A501 Basic"
        $I| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A601 Basic"
        $J| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "A765 Basic"
        $K| Export-excel $fileName -Autosize -AutoFilter -Append -WorksheetName "S8808 Basic"

        }

''''

CodePudding user response：

The problem with your code is that you try to export to Excel all groupnames you have in your variables in each iteration, while you are only retrieving info for one single name in each iteration. Also, I wouldn't try this using all those variables, but instead use a lookup table that holds both the group name as well as the sheet name for Excel.

# create a lookup Hashtable with the group names as key and the desired Excel sheet name as value
$lookup   = @{
    'group A'    = 'A136 Basic'
    'group B'    = 'A151 Basic'
    'group C'    = 'A154 Basic'
    'group D'    = 'A182 Basic'
    '182'        = 'A182 Basic2'
    '182Basic3'  = 'A182 Basic3'
    'A182Robots' = 'A182 Robots'
    '501'        = 'A501 Basic'
    '601'        = 'A601 Basic'
    '765'        = 'A765 Basic'
    'S8808'      = 'S8808 Basic'
}
$fileName = 'C:\users\rqw82758\Desktop\GroupMemberShip.xlsx'

foreach ($group in $lookup.Keys) {
    $result = Get-ADGroupMember -Filter "Name -eq '$group'"
    if ($result) {
        $result | Select-Object SamAccountname, Name, @{n='GroupName';e={$group}}, @{n='Description';e={(Get-ADGroup $group -Properties description).description}} | 
                  Export-Excel $filename -Autosize -AutoFilter -Append -WorksheetName $lookup[$group]
    }
    else {
        Write-Warning "Could not find a group called '$group'.."
    }
}

P.S. perhaps also property objectClass might be of interest, because Get-ADGroupMember can return objects of type 'user', 'group' and 'computer'

CodePudding user response：

this is what you miss

foreach ($group in $groups) {
       
       Get-ADGroupMember $group | select samaccountname, name, @{n='GroupName';e={$group}}, @{n='Description';e={(Get-ADGroup $group -Properties description).description}}`
       |Export-Excel $filename -Autosize -AutoFilter -Append -WorksheetName $group
              
      }