Sometimes I want to create an email distribution list from the results of an SCCM query, so I can email all the members to advise them about the software I am deploying to their machines, especially if the end user must initiate the deployment from ‘Run Advertised Programs’ (SCCM2007) or the Software Center (SCCM2012).  For example, recently I needed to deploy SP2 of Microsoft Project 2010 to all machines that had MS Project 2010 installed, but didn’t yet have SP2.  So I created a query in SCCM to find those machines, and included the ‘Last Logon User Name’ in the query, so I know who is using that machine and who to email.

I then export that data to Excel to create a CSV.  In SCCM 2007 you can export to csv by right-clicking the query, but in SCCM2012 you don’t have that option so you must just copy and paste into a spreadsheet and save as csv.  This gives me something like:

I only need the usernames to create the AD Group, so lets strip out everything else, and replace the username column header with ‘SamAccountName’ – something AD can work with.

Then I use this little powershell script (AD Module required) to create a distribution list in AD, and populate the members from all the usernames in the csv.

# Variables
$ADGroupName = "Project2010NoSP2"
$GroupCategory = "Distribution" # or Security
$GroupScope = "Universal" # Or Global, DomainLocal
$Path = "OU=User Groups,OU=SCCM2007,OU=IT,OU=Global,DC=MyDomain,DC=Com" # AD Path to create group in
$Description = "All Users with Project 2010 but no SP2"
$CSV = "C:\Script_Files\Project2010NoSP2.csv" # Location of CSV File

# Create AD Group
New-ADGroup -Name $ADGroupName `
 -SamAccountName $ADGroupName `
 -GroupCategory $GroupCategory `
 -GroupScope $GroupScope `
 -DisplayName $ADGroupName `
 -Path $Path `
 -Description $Description

#Enter CSV Input file
$CSVInputData = import-csv $CSV

#Add Group members from CSV
foreach ($row in $CSVInputData) {
Add-ADGroupMember $ADGroupName -Members $row.SamAccountName}

The result?

I get a new DL in AD, populated with all the members I need to inform about the software deployment I’m targetting to their machines 🙂

You’ll need to mail-enable it for use with Exchange, from an Exchange Management Shell simply:

Enable-DistributionGroup -Identity "Project2010NoSP2"