March 22, 2024

Export Microsoft 365 Group Report to CSV Using PowerShell

by Kathy Cooper

6 min read

No Comments

As an admin, monitoring groups and their membership is one of the routine tasks. Most admins prefer PowerShell over Microsoft admin center for better customization. So, let’s see how to get Microsoft 365 group report using PowerShell in this blog.

PowerShell has several cmdlets like Get-MSolGroup, Get-AzureADGroup, Get-UnifiedGroup, Get-DistributionGroup, Get-MgGroup to get group information. Are you confused to pick the correct one? Fear not! We are here to help you. We have created an All-in-One PowerShell script to export groups and their membership details to CSV file.

With the single script, you can generate multiple group reports with the help of advanced filtering options. Here are a few example reports,

  • All groups report
  • All groups membership report
  • Get group report for a single/list of groups (through CSV import)
  • Get group membership for a single/list of groups (through CVS import)
  • Microsoft 365 security group report
  • Microsoft 365 security group membership report
  • Distribution group report
  • Distribution group membership report
  • Mail enabled security group report
  • Mail-enabled security group membership report
  • Empty group report (Group without member report)
  • Group size report (Group report based on the member count report)

Download Script: M365GroupReport

Additional Highlights of the Script:

  • The script uses Microsoft Graph PowerShell.
  • The script can be executed with certificate-based authentication (CBA) too.
  • Exports the report result to CSV.
  • You can get members count based on Member Type such as User, Group, Contact, etc.
  • The script is scheduler friendly.
  • Above all, the script exports output to nicely formatted 2 CSV files. One with group information and another with detailed group membership information.

Microsoft 365 Group Report PowerShell Script – Execution Steps:

Method 1: Execute the script with admin account

To view all the groups and their members’ info, run the script as follows:

After the script execution, both output files will be available in the execution directory.

Method 2: Execute the PowerShell Script with Certificate

To run the script with certificate based authentication, use the below format

You can also use the above format to run the PowerShell script as scheduled task in Windows Task Scheduler.

Note: To use CBA, you must register app in Azure AD. To get more details on registering Azure app and certificates, you can refer the connect MS Graph PowerShell with certificate blog. You can follow the manual steps or automate the process (through ready-to-run script) based on your requirement.

Microsoft 365 Group Report:

The Microsoft 365 Group report has the following columns: Group Name, Email Address, Group Type, Group Members Count, and Members Count by Type.

The group report looks similar to the screenshot below:

Microsoft 365 group report

Microsoft 365 Group Membership Report:

The Microsoft 365 group membership report has the following columns: Group Name, Group Email Address, Member Name, Member Email, and Member Type. Group member type is helpful in identifying type of a member such as user, group, and contact.

The group members report looks similar to the screenshot below:

Office 365 group membership report

More Use-cases of Microsoft 365 Group Report Script:

As earlier said, the script supports advanced filtering params to get the report as per your requirement.

Export all Microsoft 365 Groups and Membership to CSV:

To export all the Microsoft 365 groups, execute the script as follows:

By referring to this report, admin can add or remove user from the group.

The exported report contains all the group types such as Distribution List, Security Groups, Mail-enabled Security group and Unified groups. You can also leverage PowerShell scripts to export all M365 groups and their ownership.

.

Get Members for a Single/List of Groups:

You can use GroupIDsFile param to get the report for specific group(s) from the input list called “GroupId.txt” and exports all membership into CSV.

To get group report and membership report for specific groups, pass an input file with a GUID of groups.

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

Office 365 group report

Group Size Report:

The report helps you to get the groups that have more than an ‘N’ number of members. To get this report, you can execute the script with MinGroupMembersCount param.

The above script exports all groups that have more than 50 members.

Note: If you want to determine group membership (I.e., members count) based on certain conditions, you can use Dynamic Group membership report.

Microsoft 365 Security Group Report:

Security groups are used for granting access to Microsoft 365 resources. To export security group report, you can run the script with –Security param.

By referring group membership report, you can view the users who has rights and access to SharePoint sites and CRM Online.

Distribution List Report:

Distribution lists are used for sending notifications to a group of people. To export distribution group and their membership, you can use DistributionList param while executing the script.

The above script lists all the distribution groups and their members info.

Note: The report lists Microsoft 365 groups (group mailboxes) as a Distribution List.

You can also check our dedicated script on the Distribution Group membership report for a more detailed report.

Mail-enabled Security Groups Report:

This type of group works as regular security along with the ability to send mail to its members. To get a list of all the mail-enabled security groups, execute the script with MailEnabledSecurity param.

The script exports mail-enabled security groups info and membership to the CSV file.

Get Empty Groups Report:

To get an empty group report (i.e., groups without members), execute the script with IsEmpty param.

By referring to this report, you can delete unused/inactive groups in your tenant.

Schedule Group Report to run Periodically:

As said earlier, this script is scheduler friendly. You can pass the credential as a parameter. So, it will not prompt for username and password during execution.

You can use Windows Task Scheduler for PowerShell scheduled task.

Get more Granular Group Report:

To export a more granular group report, you can use multiple filters (i.e., params and switches). I have given few examples below.

  • To get empty distribution groups,

  • To view empty security groups,

  • To list empty mail-enabled security groups,

  • To get large distribution groups,

  • To get security groups based on the members count,

Microsoft 365 groups are helpful when you want to give access/ send information to multiple people. Adding people to the group automatically gives them the permission they need. Apart from the groups, you can also use shared mailbox membership when multiple people want to access the same mailbox. If you are new to PowerShell and face difficulty in understanding the script, you can simply go with Microsoft365DSC to export M365 configurations including all groups, members, and all. Gi

Get More Detailed Group Reports:

To get more detailed group reports based on

  • Group types
    • Security group
    • Distribution group
    • Mail-enabled security group
    • Microsoft 365 group
    • Dynamic distribution group
  • Group membership
    • Direct members
    • Nested group members
    • Hidden membership
    • External members
  • Group creation source
    • Groups created via Teams
    • Groups created via SharePoint Online
    • Groups created via Yammer
  • Group member changes
  • Groups usage
  • Inactive groups based on Last activity date

you can take a look at AdminDroid Microsoft 365 reporting and auditing tool.

AdminDroid provides 1500+ pre-built reports and 20 smart visually appealing dashboards to know about your Office 365 environment at a glance. This tool provides reports on Office 365 reporting, auditing, analytics, usage statistics, security & compliance, etc.

AdminDroid Office 365 Reporting Tool AdminDroid Office 365 user activity dashboard AdminDroid Mailbox Usage reportOffice 365 group reports

Besides, AdminDroid provides over 100+ reports and dashboards completely for free. It includes reports on Users, Licenses, Groups, Group Members, Devices, Login Activities, Password Changes, License Changes, and more. The free edition doesn’t have any restrictions in reporting functionalities such as customization, scheduling, and exporting. Download Free Office 365 reporting tool by AdminDroid and see how it helps you.

I hope this blog will help you in managing group membership in your organization.

Share article