Kae Travis

Group Data using PowerShell

I’ve been running an audit on Active Directory (AD) recently, with a view to sending emails to multiple users based on AD group membership. Rather than sending a separate email to each user in an AD group (adding load to our Exchange server) I decided to send one email per AD group, and Bcc in all the users that are a member. And to do this I needed to group data using PowerShell.

Group Data using PowerShell

There are around 200 AD groups that I needed to audit, with a total of around 6000 users spread across all groups. i didn’t want to send 6000 emails to each user due to the load on our Exchange server; instead I opted to send 200 emails, and Cc in the relevant members of each AD group.

I ran a PowerShell script to extract AD groups and user details in the following raw format (example data):

ADGroup Email
Group 1 jfox@alka…utions.co.uk
Group 1 ptaylor@alka…utions.co.uk
Group 2 towen@alka…utions.co.uk
Group 3 mthomas@alka…utions.co.uk
Group 3 jrivers@alka…utions.co.uk
Group 3 djones@alka…utions.co.uk

But I needed to group this data by the ADGroup field to look similar to this:

ADGroup Email
Group 1 jfox@alka…utions.co.uk,ptaylor@alka…utions.co.uk
Group 2 towen@alka…utions.co.uk
Group 3 mthomas@alka…utions.co.uk,jrivers@alka…utions.co.uk,djones@alka…utions.co.uk

And i did it like so:

$currentDirectory = [System.AppDomain]::CurrentDomain.BaseDirectory.TrimEnd('\') 
if ($currentDirectory -eq $PSHOME.TrimEnd('\')) 
$currentDirectory = $PSScriptRoot 
#read in CSV from raw export
$adgroups = import-csv "$currentDirectory\ad_groups_and_users_test.csv"
#group by AD group
$grouped_adgroups = $adgroups |
Group-Object ADGroup|
Select-Object @{Name='ADGroups';Expression={
$GroupName = $_.Values[0]; $adgroups | where-object { $_.ADGroup -eq "$GroupName" } | foreach { $_.Email };
write-host "Processing $($grouped_adgroups.Count) AD groups..."
#loop through each grouped AD group
foreach ($g in $grouped_adgroups) {
#string representing the AD group name
$adgroup = $g.Apps 
#array of string representing each email
$emailrecipientcount = $g.Emails.Count
write-host "Sending email to $($emailrecipientcount) recipient(s) for AD group $adgroup"
#When using Send-MailMessage, we can simply add $g.Emails to the Bcc field
Group Data using PowerShell
Group Data using PowerShell

Leave a Reply