List Server – Exchange

Everyone wants to make the most of what you have right?

For years we used an old version of GNU Mailman as a moderated listed server. In an effort to consolidate resources I got it in my head I wanted to do a moderated list in our Exchange server.

The moderated part wasn’t that hard, thats already built in.

Exchange ECP -> Recipients -> Groups -> “Group Name” -> Message Approval

What took some getting creative was the syncing of users. The users were passed using an SQL query, and imported into the list on a semester basis.

Im sure there are a bunch of different ways this could of been approached. Here is out I tackled the problem.

https://github.com/ke5awf/exchangelistusermanagement

I set the following script to run nightly and rebuild the groups.

#10/10/2018
#Script for Syncing Emails from SQL to Distro list in Exchange
#Only Exchange Admin's should attempt to run this script

$SYNCNAME = "All Mailing Lists"
#Name of Sync

#The following sets up the connection to Exchange, it will ask you for Exchange Admin Credentials to start the remote session
$Username = "DOMAIN\USER"
$Password = 'PASSWORD' | ConvertTo-SecureString -AsPlainText -Force
Import-PSSession $Session -DisableNameChecking
$UserCredential = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$Password
#Or you can have it prompt you for the credentials by using the following line instead. 
#The following sets up the connection to Exchange, it will ask you for Exchange Admin Credentials to start the remote session
#$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://EXCHANGE_FQDN/PowerShell/ -Authentication Kerberos -Credential $UserCredential
Import-PSSession $Session -DisableNameChecking

#Declare group list that the Users will be put into. 
#There is an assumption that this group exists already in exchange, if it does not you must create it first!!
$group1="DISTRO1"



#Get List of emails from SQL that need to be in this sync, plus any extras that need manually added.
#DISTRO1
$emaillist1 = (Invoke-SQLcmd -server 'SQL_SERVER' -database 'DATABASE' -Query "select email from database.dbo.maillist where listname = 'DISTRO1' order by lastname, firstname")



#Remove users from list that are currently in the list. This is being done with the update-distibutiongroupmember command.
Update-DistributionGroupMember -Identity $group1 -Members user@domain.com -Confirm:$false




#Get results of SQL and aux list and add email to specified group. 
$emaillist1 | ForEach {Add-DistributionGroupMember -Identity $group1 -Member $_.email}
Add-DistributionGroupMember -Identity $group1 -Member "specialadd@domain.com"


#required to exit SQLServer
cd c:


#Save results to \\FILESERVER\DESTINATION and email a copy to the admin
Get-DistributionGroupmember -identity $group1 | select DisplayName,PrimarySMTPAddress | Export-CSV "C:\MailLists\Export\$group1 $(get-date -f yyyy-MM-dd).log"
Send-MailMessage -SMTPServer SMTP_SERVER -To "emailadmin@domain.com" -From "EMAILSYNC@domain.com" -Subject "Distro List SYNC Complete $SYNCNAME" -Body "Sync results can be found in \\FILESERVER\DESTINATION" -Attachment "C:\MailLists\Export\$group1 $(get-date -f yyyy-MM-dd).log"

exit

This has worked for me very well. I would like to improve the code down the road, but for now it works great. I run the script from a server as a scheduled task nightly and everyone is synced up.

 72 total views