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.

Let go of Get-Mailbox; it’s had its time – Read more on moving Exchange Online scripts to use Get-ExoMailbox

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.

The Graph Explorer shows members of a nested distribution list
Figure 1: The Graph Explorer shows members of a nested distribution list

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).

Membership statistics for distribution lists
Figure 2: Membership statistics for distribution lists

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.

About the Author

Tony Redmond

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He is the lead author for the Office 365 for IT Pros eBook, the only book covering Office 365 that is updated monthly to keep pace with change in the cloud. Apart from contributing to Practical365.com, Tony also writes at Office365itpros.com to support the development of the eBook. He has been a Microsoft MVP since 2004.

Comments

  1. Finn

    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?

  2. Dan V

    How can I add the “Senders who don’t require message approval:” information to this report.

  3. Alex

    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?

      1. Alex

        Thank you for the reply.
        I suppose I do. I will try to debug it. Thank you.

  4. Francesco S

    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?

      1. Francesco S

        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.

          1. Francesco S

            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

  5. Bryan H

    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.

Leave a Reply