The following PowerShell script finds all of the Active Directory group memberships for users in a target Active Directory Group. The PowerShell script is looking for 2 parameters:

1. TARGET_AD_GROUP – The AD group in question
2. $logfile – A text file destination, saved as a CSV

The script will first find all members of the TARGET_AD_GROUP. The script will then store the array of users in the variable $user. The script will then iterate over each of the users in the array. During each iteration, the script will find all groups that the current user is a member of. This resulting groups are stored in the array $groups. The script will then iterate over the groups and write out the current user and all of their group memberships to the logfile, one group at a time.

Import-Module ActiveDirectory
$users = get-adgroupmember "TARGET_AD_GROUP" | Select-Object  SamAccountName, Name | Sort-Object name 
$logfile = "C:\OutFile.csv"
add-content $logfile "AccountName,UserName,GroupName"

foreach($user in $users){
    $groups =GET-ADUser -Identity $user.SamAccountName  –Properties MemberOf | `
    Select-Object -ExpandProperty MemberOf | `
    Get-ADGroup -Properties name | `
    Sort-Object name | `
    Where-Object { $_.GroupCategory -eq "Security"}|`
    Where-Object {$_.name -like "*SQL*"}|` # An additional filter
    Select-Object name
    foreach($group in $groups){
        $outline = "{0},{1},{2}" -f $user.SamAccountName, $user.Name, $group.name
        add-content $logfile $outline
    }
}

The results will look something like this:

AccountName	UserName	GroupName
asmith	Adam Smith	TARGET_AD_GROUP
asmith	Adam Smith	SECURE_AD_GROUP
asmith	Adam Smith	OTHER_AD_GROUP
bjones	Brad Jones	TARGET_AD_GROUP
bjones	Brad Jones	MANAGER_AD_GROUP
bjones	Brad Jones	CALENDAR_AD_GROUP
kcarter	Kelly Carter	TARGET_AD_GROUP
kcarter	Kelly Carter	SR_MANAGER_AD_GROUP
kcarter	Kelly Carter	MANAGER_AD_GROUP
wwoods	Wilhem Woods	DBA_AD_GROUP

You can then import the results into SQL Server, Excel, etc for pivoting and further analysis.