Export AD Group Membership - Excel Multiple Sheets

Question

is there an way to export to excel on tabs (sheets)?  For example each tab (sheet) would be a security group and members listed? But its not working correctly. We are using ImportExcel module from here: https://github.com/dfinke/ImportExcel .

I have tried this so far :

Get-ADGroup -Properties name -Filter 'name -like "*VPN*"' | % { Get-ADGroupMember -Identity $_.Name | Export-Excel -Path C:\tmp\Groups.xlsx -WorkSheetname $_.Name -AutoSize }

output :

distinguishedName,name,objectClass,objectGUID,SamAccountName,SID,PropertyNames,AddedProperties,RemovedProperties,ModifiedProperties,PropertyCount
CN=User,DC=contoso,DC=com,John T,user,1bae7a5f-9f19-4435-a47d-66855024cea8,johnt,S-1-5-21-3248419837-683404524-394759481-53949,System.Collections.Generic.SortedDictionary`2+KeyCollection[System.String,Microsoft.ActiveDirectory.Management.ADPropertyValueCollection]    System.Collections.Generic.HashSet`1[System.String] System.Collections.Generic.HashSet`1[System.String] System.Collections.Generic.HashSet`1[System.String] 6

Desired output :

I want to get all users in an ADgroup with the displayname , name and mail.

Displayname , Name , Mail
John T,john.t, johnt@contoso.com

Thanks in advance,

Answer 1

I don't have your Export-Excel function, so the below code exports to CSV:

# 1. Get the groups that match the name pattern
# 2. Get the members of these groups recursive
# 3. Select only member sthat are users.
# 4. Get the user properties you want in the output
# 5, Store it all in a variable $result.
# 6. Export the $result to CSV (or use Export-Excel)
$result = Get-ADGroup -Properties Name -Filter 'name -like "*VPN*"' | ForEach-Object { 
    $group = $_.Name
    Get-ADGroupMember -Identity $group -Recursive | 
    Where-Object {$_.objectClass -eq 'user'} |
    Get-ADUser -Properties Displayname,Name,EmailAddress |
    Select-Object @{Name = 'Group'; Expression = {$group}}, Displayname,Name,EmailAddress

$result | Export-Csv -Path 'C:\tmp\Groups.csv' -NoTypeInformation

To save each group to a new file or worksheet, use this:

# Group the results by the Group name
$result | Group-Object -Property Group | ForEach-Object {
    $group = $_.Name
    $_.Group | Select-Object * -ExcludeProperty Group | 
    # save as separate csv files
    # Export-Csv -Path "C:\tmp\$group.csv" -NoTypeInformation

    # or as separate worksheets in an Excel document
    Export-Excel -Path 'C:\tmp\Groups.xlsx' -WorkSheetname $group -AutoSize
}

Hope that helps