I was recently asked to create a load of distribution lists in Exchange Online. I was also asked to give the owner of the distribution list a simple method of keeping them up to date and frankly, I was more than happy to oblige with that. I have enough to do during the day, keeping distribution lists up to date is just a pain in the bum.
We have a system called Salamander which synchronises our MIS system with Active Directory. In theory, we could have used that to keep the DLs up to date but that means putting in an extra layer of abstraction and even then, it doesn’t solve the issue of the DL owner keeping the lists up to date as she doesn’t have access to most of the ten(!) MIS systems we have sitting on our network. I decided to look into other ways of doing it.
It actually turned out to be a quite an educational exercise for me. Of course, I decided to use PowerShell to do this as it’s the one scripting language I have even a basic understanding of, which is lucky because it’s also the official way to do things with Exchange Online outside of its GUI.
The method that I’m using to keep the DLs up to date is quite simple: I have a share which hosts a series of CSV files which are named after the DL that we want to keep up to date. Each CSV file has a single column with the heading “email which, oddly enough, has the member’s email address in it. The script gets all of the CSV files in the share and loops through them. In each loop, it adds the contents of the CSV file to an object. It loops through each member of that object, checks to see if the member in the CSV file needs to be added to the DL and adds them if they do. It then checks the DL against the contents of the object and removes any member which shouldn’t be in there as well. I then have a scheduled task running on a server which runs the script at 3am.
There are more clever things that we could do with this script, such as create DLs from the CSV files but I’m not doing that at the moment.
Prerequisites for this this script are:
- PowerShell 5.1 on Windows 7/Server 2008 R2 or higher, or PowerShell Core 7.0 on Windows, macOS or Linux
- Exchange Online v2 PowerShell Module
The first step is to install the Exchange Online PowerShell Module on the machine that you’re going to be running the script from. This is nice and easy to do, thanks to the Install-Module command. To install it, run this from a PowerShell prompt:
|
1 |
Install-Module -Name ExchangeOnlineManagement |
So, the first part of our script involves connecting to Exchange Online. To do this, we need to authenticate. This is the first tricky bit; with PowerShell 5, we could embed the username and password that you’re using into the script and pass that credential to Exchange. However, if your account is using MFA, if Basic Authentication is turned off for your tenant or if using PowerShell Core to run this script, that’s instantly a problem. Not only that, Microsoft are turning off Basic Authentication for Exchange Online some time in the second half of this year so even if you did use it, your script would stop working then.
Luckily, the Exchange Online PowerShell module supports certificate based authentication so that’s instantly a much easier way of connecting your script to Exchange. However, to use certificate authentication, we to go through the following procedure:
- Create a new application in Azure AD
- Assign the new application the correct Azure AD Role
- Generate a self signed certificate for authentication then attach it to the Azure application
- Grant Admin Consent to the application
- Put the certificate into a certificate store for the script to use.
I’m not going to pretend that I was clever enough to work all this out for myself. I used an excellent guide from June Castillote at adamtheautomator.com to help me through the process. I’m not going to reproduce his work here because that would be stealing credit for someone else’s work.
In any case, once you’ve followed June’s excellent guide, you will have a PFX file with a passphrase. You will need to import this PFX into a certificate store, either the User’s or the Computer’s store. Either is fine; putting it into the user’s store is better from a security point of view as putting it into the computer’s store will mean that anyone who has access to your computer will have access to your Exchange to do whatever they like with, whereas putting it into the user’s store will restrict it to the user. That said if you’re putting it onto a server to run, chances are it’ll a service account that’s running the script and putting that certificate into the service account’s personal store might be more awkward. Anyway, the first part of the script looks like this:
|
1 2 3 4 5 6 7 8 9 |
## Connect to Exchange Online Shell ## Define Tenant ID $tenantID = 'your_aad_tenant.onmicrosoft.com' ## Set the Exo_V2_App app id $appID = 'Exo_V2_App_Id_GUID' ## Set the certificate thumbprint $CertificateThumbPrint = 'CertificateThumbPrintHere' ## Connect to Exchange Online $Exchange = Connect-ExchangeOnline -CertificateThumbPrint $CertificateThumbPrint -AppID $appID -Organization $tenantID |
You will need to get your Azure tenant ID, your applications AppID and the thumbprint of the certificate and populate them in there. You can get the first two things from the Azure AD portal, underneath App Registrations and searching for the Exo_V2_App application. The certificate thumbprint can be got from the certificate properties in the store.
The whole script looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
## Connect to Exchange Online Shell ## Define Tenant ID $tenantID = 'your_aad_tenant.onmicrosoft.com' ## Set the Exo_V2_App app id $appID = 'Exo_V2_App_Id_GUID' ## Set the certificate thumbprint $CertificateThumbPrint = 'CertificateThumbPrintHere' ## Connect to Exchange Online $Exchange = Connect-ExchangeOnline -CertificateThumbPrint $CertificateThumbPrint -AppID $appID -Organization $tenantID $CSVPath = "\\path.to.your.share\here" $InternetDomainName = "your.domain.org" ## Get list of CSVs to process which contain group membership $CSVs = get-item -path $CSVPath foreach ($CSV in $CSVs) { #Get current members of DL $DLMembers = $(Get-DistributionGroupMember -Identity "$($CSV.Name.split(".")[0])@$($InternetDomainName)").Name #Get list of people who need to be in the DL $MembersToAdd = Import-Csv $CSV.FullName if ($MembersToAdd) { Write-Host("Processing " + $CSV.FullName) -BackgroundColor Yellow -ForegroundColor Black #Add members in list who aren't already there foreach ($MemberToAdd in $MembersToAdd) { if ($DLMembers -match $MemberToAdd.email.split("@")[0]) { Write-Host($MemberToAdd.Email + ": User already in DL, moving on") -BackgroundColor Black } else { Write-Host($MemberToAdd.Email + ": User not in DL, adding now") -BackgroundColor Green Add-DistributionGroupMember -Identity "$($CSV.Name.split(".")[0])@$($InternetDomainName)" -Member $MemberToAdd.Email -BypassSecurityGroupManagerCheck } } #Remove members of DLs who aren't in the CSV files foreach ($DLMember in $DLMembers) { #Check to see if they should be in there, remove them if they shouldn't if ($MembersToAdd.email.split("@") -match $DLMember) { Write-Host($DLMember + ": This person should be in the list, moving on") -BackgroundColor Black } else { Write-Host($DLMember + ": This person shouldn't be in the list, delete them") -BackgroundColor Red Remove-DistributionGroupMember -Identity "$($CSV.Name.split(".")[0])@$($InternetDomainName)" -Member "$($DLMember)@haaf.org.uk" -BypassSecurityGroupManagerCheck -Confirm:$false } } #$MembersToAdd } else {Write-Host($CSV.FullName + " File Empty") -BackgroundColor Red} } Disconnect-ExchangeOnline -Confirm:$false |
It does what I want from it at the moment but looking back at it, it’s not too intelligent. There’s no error catching, it doesn’t check to see if the DL you want to process exists, it doesn’t do anything clever like create DLs. All of those things would be simple enough to implement, I’ll probably do it when I have a spare moment or two.
Finally, I know that the user could update the DL themselves from Outlook on the Web but honestly, I think that this is probably simpler in a lot of respects. The person that I wrote this for was happy anyway, which is the main thing!
Comments