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 (I’ll show how in another article), 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 the complete script from GitHub. Feel free to improve it and make it behave the way you think it should work. The point is that it’s PowerShell, so the code is very adaptable for different purposes.

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. 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. Avatar photo
      Tony Redmond

      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…

      1. Alex

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

        1. Avatar photo
          Tony Redmond

          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.

  2. 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. Avatar photo
      Tony Redmond

      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.

      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. Avatar photo
          Tony Redmond

          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

          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

          2. Avatar photo
            Tony Redmond

            Change the line which removes duplications from DL members to:

            $MembersNames = $MembersNames | Sort MemberId -Unique | Sort MemberName

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