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"
