CODEWITHSUNDEEP
powershellcsvactive-directorygrouping
EverythingDarkMode
EverythingDarkMode

Reputation: 25

Grouping data from Active Directory

wondering if i could get some help with my powershell script. I am halfway (or maybe even 3/4 of the way there) but im just struggling to get my groups grouped on one line per user....

Ill explain

Right now i'm able to get ALL users from Multiple AD groups, took a bit of playing around but i got there in the end...

However it displays CSV like this: 

first_name,last_name,email,group_list
John,Smith,[email protected],Group1
John,Smith,[email protected],Group2
Emily,Rogers,[email protected],Group1
Emily,Rogers,[email protected],Group3

Whilst thats OK, i would really like to format the data like this:

first_name,last_name,email,group_list
John,Smith,[email protected],Group1~Group2
Emily,Rogers,[email protected],Group1~Group3

This is my code so far

## Define the groups, This includes a wildcard which gets all users in groups with that pattern
$Groups = (Get-AdGroup -filter * | Where {$_.name -like "GroupName*"} | select name -expandproperty name) 

## Var for array, empty
$Array = @()

## Var for data
$Data = [ordered]@{
}


## For Each loop to get members of each group
Foreach ($Group in $Groups)
{
## Define the search criteria for AD Search
$Members = Get-ADGroupMember -identity $Group | Get-ADUser -Properties * | select  givenName,sn,sAMAccountName,mail
foreach ($Member in $Members)
{
$Data."first_name" = $Member.givenName
$Data."last_name" = $Member.sn
$Data."email" = $Member.mail
$Data."group_list" = $Group

## Store in PSObject
$DataPSObject = New-Object PSObject -property $Data

## Add to array so it is no longer empty
$Array += $DataPSObject

}

}


## Export array into CSV 
$Array | export-csv "C:\temp\DataFromArray.csv" -NoTypeInformation

As the email is the unique identifier, i tried to Group-Object on the email property but the output is not useful for me

## Export array into CSV 
$Array | Group-Object -Property email | export-csv "C:\temp\DataFromArray.csv" -NoTypeInformation

Also i tried to join the groups using a defined separator -join '~' but this just seemed to create one long string of joined groups (makes sense when i put it that way)

Hoping anyone has some ideas?

Thanks

Upvotes: 2

Views: 57

Answers (2)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 175085

You need to do a little more processing on the output of Group-Object, but you're almost there!

$Array |Group-Object -Property email |ForEach-Object {
    [pscustomobject]@{
        first_name = $_.Group[0].first_name
        last_name  = $_.Group[0].last_name
        email      = $_.Group[0].email
        groups     = $_.Group.group_list -join '~' # join all the group names together
    }
} |Export-Csv "C:\temp\DataFromArray.csv" -NoTypeInformation

Upvotes: 1

Theo
Theo

Reputation: 61263

Just a quick thingy to get what you want:

## Define the groups, This includes a wildcard which gets all users in groups with that pattern
$Groups = Get-AdGroup -Filter 'Name -like "GroupName*"' | Select-Object -ExpandProperty Name

## For Each loop to get members of each group
$Array = foreach ($Group in $Groups) {
    ## Define the search criteria for AD Search and capture in variable $Array
    Get-ADGroupMember -Identity $Group | 
        Get-ADUser -Properties GivenName,Surname,SamAccountName,EmailAddress | 
        Select-Object @{Name = 'first_name'; Expression = {$_.GivenName}},
                      @{Name = 'last_name'; Expression = {$_.Surname}},
                      @{Name = 'email'; Expression = {$_.EmailAddress}},
                      @{Name = 'GroupName'; Expression = {$Group}}

}

$out = $Array | Group-Object email | ForEach-Object {
    # join the GroupName property of this user to get a delimited string
    $grouplist = $_.Group.GroupName -join '; '
    # output a new object with the 'group_list' property
    # this will create duplicates objects, so we need Select-Object * -Unique at the end
    $_.Group | Select-Object first_name, last_name, email, @{Name = 'group_list'; Expression = {$grouplist}}
} | Select-Object * -Unique

$out | Export-Csv "C:\temp\DataFromArray.csv" -NoTypeInformation

Hope that helps

Upvotes: 1

Related Questions