Export Office 365 Distribution Group Members to CSV

What is Distribution Group?

Office 365 Distribution Groups (aka Distribution List) are used to send emails or meeting request to list of people (both internal and external users), without having to add the email address individually each time. Distribution Groups are suitable when you need to broadcast information to a group of people, such as “People in the sales department” or “All people at your company”.

 

How to Get Distribution Group Members?

Finding out who is members of which Distribution Group is always a tedious task. You can view Distribution Group members through Microsoft 365 Admin Center, but it is per user base. So, you need to click each group to view its membership which is time-consuming. Fear not! Your friend, PowerShell is here for the rescue.

Using PowerShell, you can export Office 365 Distribution List and members to CSV file.

If you want to export Dynamic Distribution Group membership, you can refer our Export Office 365 Dynamic Distribution Group Members to CSV blog

 

Export Office 365 Distribution Group Members to CSV using PowerShell

We have written a PowerShell script to export Distribution List members to CSV along with most required attributes like Group DisplayName, Group Primary SMTP Address, Group Alias, Group Type, Group Owner (Managed By), Members Count, Members, Member Email, Member’s Type.

Exchange Online PowerShell cmdlet Get-DistributionGroup used to list all distribution group available in the tenant and Get-DistributionGroupMember used to list group members. Export-CSV cmdlet used to export the report to CSV file. A group can have many members and exporting them in a single line is not an aesthetic one.

To understand your Distribution Group membership better, our script will provide two output files.

  • Distribution Group Summary Report
  • Detailed Distribution Group Membership Report

You can Download the PowerShell script from TechNet Gallery.

 

Script Highlights:
  • Allows you to filter the report result based on group size(i.e., Members count).
  • The script can be executed with MFA enabled account.
  • You can choose to either “Export Members of all Distribution Lists” or pass an input file to “Export Members of Specific Distribution List”.
  • You can filter the output based on whether the group accepts message from external senders or not.
  • Output can be filtered to list Empty group. i.e., Distribution Group without members
  • Exports the report result to CSV.
  • You can get members count based on Member Type such as User mailbox, Group mailbox, Shared mailbox, Contact, etc.
  • The script is scheduler friendly. i.e., credentials can be passed as parameter instead of saving inside the script.
  • Above all, script exports output in nicely formatted 2 CSV files. One with detailed information and another with summary information.

 

Office 365 Distribution Group Summary Report:

Distribution Group summary report has following attributes.

  • Group Display Name,
  • Primary SMTP Address,
  • Group Alias,
  • Group Type,
  • Group Owner (Managed By),
  • Group Members Count,
  • Members Count by Recipient Type,
  • Allows External users
  • Hidden From Exchange Address List (Optional)
  • Description (Optional)
  • Creation Time (Optional)
  • DirSynEnabled (Optional)
  • Member Join Restriction (Optional)
  • Member Depart Restriction (Optional)

get Office 365 distribution group members report

 

Detailed Office 365 Distribution Group Membership Report:

Detailed Office 365 Distribution Group membership report has following attributes:

  • Group Display Name,
  • Primary SMTP Address,
  • Group Alias,
  • Group Members Count,
  • Group Members,
  • Member Primary SMTP Address
  • Member Recipient Type

Export Office 365 Distribution group membership

 

Script Execution: 

To export all Ofiice 365 Distribution Lists and members report, execute script as follows

For detailed script execution steps, please refer our Office 365 users’ last logon time blog.

If you want to execute this script with MFA enabled account, click here: How to execute export distribution group members report with MFA

 

Unlock the Full Potential of this Script

This script has many built-in parameters to filter/customize the report based on your requirement. We have listed some of the primary uses cases below.

  • Gets members for a single/list of Distribution Group
  • Gets members for Distribution Group that has more than ‘N’ members
  • To get empty Distribution Group
  • Exports Distribution Group that allows external users to send message
  • Using multiple parameters, you can get more granular report
  • Script can be executed with MFA enabled account
  • Script can be scheduled by explicitly passing credential
  • You can add additional attributes to export file.

Let’s check the use cases in detail.

 

Get Members for a Single/List of Distribution Group

You can use –GroupNamesFile param to get members of a Distribution Group from the input list called “DistributionList.txt” and exports all membership into CSV.

To get members of specific Distribution List, pass an input file with a Display Name/ Alias/ Distinguished Name/ Canonical DN/ Email Address or GUID of groups.

The GroupNamesFile must follow the format below:  Group identity separated by new line without header.

Get members for list of distribution group

 

Get Distribution Group Members That has more than ‘N’ Members

To filter result based on group members count, you can use –MinGroupMembersCount  param. i.e., You can get groups that have more than the specified number of members.

Above script reports all Distribution Lists that has more than 50 members.

 

Get Empty Distribution Groups

To get an empty Distribution Group (ie, Distribution Group without members), execute the script with -IsEmpty param.

Using this filter, you can delete unused Distribution List available in your tenant.

 

Get Distribution List That Allow External User to Send Message

Using –ExternalSendersBlocked Param, you can filter the result based on the delivery option. I.e., you can get list of Distribution groups that allows/denies external users to send a message to that group.

To list Distribution Groups that allows external users to send message to that group, execute a script with –ExternalSendersBlocked $false

To list Distribution Groups that doesn’t allow external users to send message to that group, execute a script with –ExternalSendersBlocked $true

 

Get more Granular Report

You can use multiple filters together, to get more granular report. For example, you can get members for a list Distribution Group that has more than 50 members

 

Execute ‘Export Distribution Group Members Script’ with MFA

To execute script with MFA enabled account, you need to mention -MFA switch during script execution.

To know more about how to connect exchange online PowerShell with MFA, refer our blog Connect Exchange Online PowerShell with MFA.

 

Schedule  ‘Export Distribution Group Members’ PowerShell Script

You can schedule this script in task scheduler by explicitly mentioning the credential.

 

How to Add more Attributes in the Export File?

To add following attributes to Distribution Group members report, uncomment the comment section in the line 110 and 112.

  • HiddenFromAddressList (HiddenFromAddressListsEnabled) – Hide group from Global Address List(GAL).
  • Description – Short information about group.
  • CreationTime (WhenCreated) – Group creation time.
  • DirSyncEnabled (IsDirSynced) – Whether the group has directory sync enabled or not.
  • JoinGroupWithoutApproval (MemberJoinRestriction) – Shows whether owner approval is required to join a group.
    • Open – Anyone can join group without being approved by the group owner.
    • Close – Members can be added only by the group owner.
  • LeaveGroupWithoutRestriction (MemberDepartRestriction) – Shows whether the group is open to leave.
    • Open – Anyone can leave group without being approved by the group owner.
    • Closed – Members can be removed only by the group owner.

If you have any queries regarding script, reach us through the comment section.