Bringing Old Scripts Forward
Recently, I wrote about the New-DistributionGroup cmdlet and explained how distribution lists remain so useful, even when Microsoft pushes Microsoft 365 Groups as the answer for all collaboration problems. A reader promptly wrote to say that distribution groups are different in Exchange Online than they are for Exchange Server and pointed out that PowerShell scripts written for on-premises consumption often don’t work in the cloud. They even cited a Practical 365 article from 2015 telling how to report the number of members in distribution lists as an example.
The script is to highlight large distribution lists of the type which might potentially cause Reply-All mail storms. Microsoft recently updated the reply-all control settings for Exchange Online to make mail storms less likely, but there’s no doubt that it’s a good idea to know if any very large distribution lists exist and, more importantly, who looks after these monsters.
The Difference Between Get-ADGroupMember and Get-AzureADGroupMember
The original script relies on the ability of the Get-ADGroupMember cmdlet (for Active Directory) to recursively fetch distribution list members. In other words, if a distribution list contains nested distribution lists, you need to fetch details of all members to know how many recipients will receive messages sent to the list. The Exchange transport service expands distribution lists during its fan-out process to create copies of messages for recipients, and that’s when the full number of recipients is known.
The problem about taking the original script to the cloud is that the equivalent Get-AzureADGroupMember cmdlet doesn’t handle recursion. The Graph API for Groups has a transitiveMembers call to return “a flat list of all nested members,” but that capability hasn’t reached the PowerShell cmdlet. To show how transitive members work, Figure 1 shows the result of a call to https://graph.microsoft.com/v1.0/groups/4f6a1e58-84cc-4365-b339-7520508c1cbc/transitiveMembers to retrieve members of a distribution list containing nested lists using the Graph Explorer tool.
If you’ve never used the Graph Explorer, you should spend some time to get used to it because it’s a great way to learn how to interact with Graph API calls.
Groups and Lists
Distribution lists are Exchange mail-enabled objects and exist in both the Exchange Online directory (EXODS) and Azure AD. They’re called distribution lists in the world of Exchange and distribution groups more generally across Microsoft 365, or just groups within Azure AD. Microsoft 365 Groups are much easier to process when it comes to member counts. First, you can’t nest Microsoft 365 Groups. Second, only tenant and guest accounts can be members. Third, the Get-UnifiedGroup cmdlet returns counts for tenant members and guest members. See this article for an example of how to report the membership of Microsoft 365 Groups.
Returning to PowerShell
It is straightforward to write a script to use the Graph API to fetch the membership of nested distribution lists, but let’s return to basic PowerShell and consider what we can do to solve the same problem as Get-ADGroupMember does on-premises.
In this article, Vasil Michev takes an in-depth look at the issues involved in generating a list of all members of all groups in an organization, including Microsoft 365 Groups, dynamic distribution lists, and so on. Although we could adapt Vasil’s script to do what we want, there’s no fun in that, so I spent an hour or so coming up with a solution to the problem of identifying large distribution lists.
The first rule of a successful PowerShell hacker is never to recreate the wheel. Always begin a coding project by searching to see what people have done to solve a problem, or at least part of the problem, that you face. In this case, a search to find how people handled the question of handling nested Azure AD groups turned up several useful functions, including one I decided to use in my script.
The Newly Coded Solution
The code is reasonably simple. First, find all distribution lists in the tenant, excluding those used for room lists.
For each list, we find its membership. In this example, I use Get-AzureADGroupMember rather than Get-DistributionGroupMember. Either will work. My decision was driven by the fact that the function I adapted used Azure AD. Finding the membership involves figuring out which members are simple (user accounts, guest accounts, and mail contacts) and which are groups. The membership of each nested group is resolved and added to the overall membership. At the end, the script removes any duplicate entries from the membership.
After processing a distribution list, the script extracts some statistics about the number of members, tenant accounts, guest accounts, groups, and “others” (mail contacts mostly). The script also generates a list of members. This information is stored in a PowerShell list.
Once the script has processed all the distribution lists, it outputs a CSV file and shows the results via the Out-GridView cmdlet (Figure 2).
You can download two versions of the script from GitHub. The first (described in this article) uses Exchange Online PowerShell; the second uses a mixture of PowerShell and Graph API requests. Feel free to improve the code in either and make the scripts meet your requirements. The point is that PowerShell code is very adaptable for different purposes, which is how it should be.
Off topic a little, sorry, but this is related and very interesting. We have Office365, we used M365 as our Exchange server, and we have AD Connect synching our local AD with Azure AD. If I use AD to remove a user from a local, old fashioned AD distribution group (not a dynamic one) and then wait and confirmed that an AD Connect synch has taken place, I get this issue when I look to see what actually updates:
• In “Exchange Admin Center”, I can confirm the user has been removed from the Distribution List ✅
• In my own Outlook Online if I address a new email to the distribution group and click the “+” to expand the group, I can see the user has been removed from the group ✅
• In my own Outlook Online if I switch from email view to “People” view and open the distribution list and look at the members, I can still see the old user in the list!!❌
Why is the Office365 Outlook Online expanded group different to that same group’s list of members in the People section?
How can I add the “Senders who don’t require message approval:” information to this report.
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
The data that you want is in the AcceptMessagesOnlyFromSendersOrMembers property of a DL. So, you need to retrieve that information and output it in the report. The data is an array, so you probably want to expand the individual members and create a string containing their display names.
Hi,
When I run the script I’m getting the following error in the end of groups processing:
The script failed due to call depth overflow.
+ CategoryInfo : InvalidOperation: (0:Int32) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : CallDepthOverflow
I tried to change the name of the file, but it didn’t help. Any ideas?
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
Do you have any distribution lists that are nested more than 3 deep? That might be the reason (based on the reference to call depth).
Apart from that, I have no idea. I can’t debug PowerShell remotely… but you can debug the code on your tenant…
Thank you for the reply.
I suppose I do. I will try to debug it. Thank you.
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
If you can identify the problematic DL, you might be able to run the code interactively to find where the problem is… and if you report it here, we will see what we can do.
Thank you for the script, it works great!
May I ask you if there is a way to sort all the owner and member names in alphabetical order?
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
The script generates a PowerShell list called $Report. You can sort the contents of that list any way you like. For instance:
$Report = $Report | Sort-Object ManagedBy
will sort the output by DL owner names.
Thank you for your quick reply!
It’s close but not exactly what I am looking for. Is it possible to get the names, specifically in the “member names” column, to sort alphabetically within each cell?
To elaborate, if the report generates 100 Distribution Lists and every DL has 500 or more members in the “Member names” column, I would like to have those 500 names sorted “inside” the cell.
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
It’s PowerShell, so you can sort as you like. To sort the members of a DL by their display name, add a Sort command after the members are fetched. For instance:
[array]$Members = Get-AzureADGroup -ObjectId $DL.ExternalDirectoryObjectId | Get-RecursiveAzureAdGroupMemberUsers | Sort DisplayName
Thank you for that!
My apologies, I was looking into the other powershell script (ReportDLMembershipsCountsGraph) and I noticed that the “Sort-Object DisplayName” doesn’t work for the DL members names.
$Uri = “https://graph.microsoft.com/v1.0/groups/” + $DL.ExternalDirectoryObjectId + “/transitiveMembers”
[array]$Members = Get-GraphData -AccessToken $Token -Uri $uri | Sort-Object DisplayName
When I run the script, I don’t get any error message but the names are all messed up. Am I missing something? Is there a way to sort both the members and the managers names in that script?
Many thanks in advance
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
Change the line which removes duplications from DL members to:
$MembersNames = $MembersNames | Sort MemberId -Unique | Sort MemberName
How can you adjust the script to get the same counts for DLG in AD? Trying to compare Newly created DLG in M365 with DLG in an On-Prem Exchange.
The Real Person!
Author Tony Redmond acts as a real person and passed all tests against spambots. Anti-Spam by CleanTalk.
Well, you can’t use this technique because it depends on the Graph API. Did you look at the original script from 2015? https://practical365.com/get-distribution-group-member-counts-with-powershell/