Powershell Get all groups of ADusers into unique columns in a CSV

Good Afternoon,

I searched through this forum and a few others combining ideas and trying different angles but haven't figured this out. If this has been answered, and I suck at searching, I am sorry and please let me know.

End Goal of script: have an excel file with columns for the AD properties Name, Office, Org, and most importantly a seperate column for each group a user is a member of. The problem I am running into is creating a new column for each/every group that a user has. Not all users have the same amount of groups. Some have 10 some have 30 (yes 30, our AD is a mess). Here is what I have done so far, and the spot that I am having difficulty with is towards the end:

$scoop = get-content C:\temp\SCOOP.txt ##This is a text file with a list of user id's, to search AD with
$outfile = 'C:\temp\SCOOP_ID.csv'
$ou = "OU=Humans,OU=Coupeville,DC=ISLANDS"  #This is the searchbase, helps AD isolate the objects
Clear-Content $outfile #I clear content each time when I am testing 

Foreach($ID in $scoop){
##AD Search filters##
$filtertype = 'SamAccountName'
$filter1 = $ID
##End AD Search filters##

##AD Search --MY MAIN ISSUE is getting the MemberOF property properly
$properties = get-aduser -SearchBase $ou -Filter {$filtertype -eq $filter1} -Properties Name,Office,Organization,MemberOf | select Name,Office,Organization,MemberOf    

##AD Search ## Turns the MemberOf property to a string, I tried this during my testing not sure if objects or strings are easier to work with
#$properties = get-aduser -SearchBase $ou -Filter {$filtertype -eq $filter1} -Properties Name,Office,Organization,MemberOf | select Name,Office,Organization, @{n='MemberOf'; e= { $_.memberof | Out-String}}  


#Attempt to specify each property for the output to csv
$name = $properties.Name
$office = $properties.Office
$org = $properties.Organization
$MemberOf = $properties.MemberOf 


$membersArray = @()
foreach($mem in $MemberOf){ $membersArray += $mem }


###This is what I typically use to export to a CSV - I am sure there are other maybe better ways but this one I know. 
$Focus = [PSCustomObject]@{}
$Focus | Add-Member -MemberType NoteProperty -Name 'Name' -Value $name -Force
$Focus | Add-Member -MemberType NoteProperty -Name 'Office' -Value $office -Force
$Focus | Add-Member -MemberType NoteProperty -Name 'Org' -Value $org -Force
$Focus | Add-Member -MemberType NoteProperty -Name 'Groups' -Value $MemberOf -Force 

<########################
#
#     Main ISSUE is getting the $memberOf variable, which contains all of the users groups, into seperate columns in the csv. To make it more plain, each column would be labeled 'Groups', then 'Groups1', and so on.

I have tried a few things but not sure if any were done properly or what I am messing up. I tried using $memberof.item($i) with a FOR loop but couldnt figure out how to send each
item out into its own Add-Member property. 

#
#######################>

##Final Output of the $Focus Object
$Focus | Export-Csv $outfile -Append -NoTypeInformation

}

Solution

I came up with this solution, simply loop $MemberOf and add a unique name.

$MemberOf = @('a','b','c','d','e','f') #example

$Focus = [PSCustomObject]@{}
$Focus | Add-Member -MemberType NoteProperty -Name 'Name' -Value 'test' -Force

$i=0; $MemberOf | % {
    $i++
    $Focus | Add-Member -MemberType NoteProperty -Name "Group $i" -Value $_ -Force 
}

$Focus | Export-Csv xmgtest1.csv -Append -Force -NoTypeInformation

However this doesn't work in your case, since you run this code once per user and there's no "awareness" of the entire set. So you would (in your existing architecture) need to ensure that the largest number of groups is added first.

You can fix this by creating the CSV all at once (only other option would be to constantly load/unload the csv file).

First, add $csv = @() to the top of your file.

Then, after you finish creating $Focus, add it to $csv.

Finally, you may remove -Append.

The slimmed down version looks like this:

$csv = @()

#Foreach($ID in $scoop){ etc..

$MemberOf = @('a','b','c','d','e','f') #example

$Focus = [PSCustomObject]@{}
$Focus | Add-Member -MemberType NoteProperty -Name 'Name' -Value 'test' -Force
#$Focus | Add-Member etc ...
$i=0; $MemberOf | % {
    $i++
    $Focus | Add-Member -MemberType NoteProperty -Name "Group $i" -Value $_ -Force 
}

$csv += $Focus

# } end of Foreach($ID in $scoop)

$csv | Export-Csv xmgtest1.csv -NoTypeInformation

Hope this helps.