As part of day to day administration, or as part of a migration project, exporting information from your Office 365 tenant is a common requirement. As Office 365 Groups are one of the core foundations for services like Microsoft Teams, you may need to retrieve information not only about the group, but also about group members and owners and share that with others.
For example – if you need to perform a tenant to tenant Office 365 migration then being able to provide reports on current group membership, so that you can plan migrations and update the target groups will require exports of membership information. Or, you might want to simply provide a weekly report on Office 365 Group membership as part of your efforts to track usage and adoption.
A great way to do this is by using PowerShell. You can use the Exchange Online PowerShell module to easily collect Office 365 Group information, and then after getting the list of groups in your tenant, interrogate each group to retrieve membership and owner lists. In some cases you might use that output in another script, but in this example we’ll create a custom CSV file that’s not only easy to read and edit in Microsoft Excel, but can also be re-imported after editing for further scripting.
Before we dive into the PowerShell scripting, we’ll take a quick look at what to expect once the output is open in Excel. You’ll see in the example below we’ve exported some key information, like the group’s SMTP address, its identity, display name and then two columns – one for the members and another for containing owners:
One thing you’ll also notice is that for the list of members, we’ve used a multi-line row in the CSV file. By default, when you export an array to a row using PowerShell, you’ll usually see System.Object[] rather than the actual list itself.
To avoid this issue, we select a useful unique identifier for each member, and concatenate all those members into a single item, separated by a new line. This is good for user visibility in Excel, and also easy to convert back into an array if we re-import the file.
Example PowerShell script
In the example PowerShell script below this is all performed through a few simple steps:
- We first retrieve all Office 365 Groups in the tenant using Get-UnifiedGroup, setting the ResultSize parameter to Unlimited.
- We then use a for each loop on the resulting $Groups object to iterate through each group.
- Within the loop, we then retrieve the list of members and the list of owners for the current group. For each of those, a sub-loop extracts just the members or owners SMTP address and adds it to two temporary arrays, one for $MembersSMTP addresses and one for $OwnersSMTP addresses.
- Next, we create a new custom object and assign it to the $GroupsRow variable. This object represents the current line in the resultant CSV file. We address the SMTP address, Identity and Display Name, and then for both the Members SMTP addresses and the Owners SMTP addresses lines, we concatenate the values in each temporary array – using a newline character (`n).
- Finally, we export the results to a CSV file.
Download the Export-O365Groups.PS1 script from GitHub here.
Using the script itself is straightforward – after connecting to Exchange Online, execute the script (named Export-O365Groups.ps1 in the example below) and use the -CSVFilename to specify the output file to create:
Re-importing the CSV file for use in PowerShell
You might choose to re-import the data for further use in scripts. For example, if you are performing a tenant to tenant migration, then you might edit the membership lists and map the source and target group and member names across (in fact, the idea for this script came from a larger script written to manage that exact process).
In those cases you’ll want to re-import the CSV file data and convert the new-line separated membership lists back into PowerShell arrays. This is very straightforward, as shown below:
$Groups = Import-Csv .\Groups.CSV
$Groups | %{ $_.MembersSMTP = $_.MembersSMTP -split "`n";
$_.OwnersSMTP
= $_.OwnersSMTP -split
"`n";}
When you then view the $Groups object you’ll then see the lists of members and owners are back in the form used in the script prior to conversion to CSV-compatible lines:
Summary
If you need to export Office 365 Group membership and owner information, and make it available in an easily editable format outside of PowerShell, CSV exports are very useful – and with very little effort, even lists of users can be represented in an easy to understand way.
Hello team,
I am trying to get Bulk security groups Owners from Mygroups Portal , can anyone guide if you have any Powershell script.
Many examples are available online. You can tweak this script to report just security groups: https://office365itpros.com/2023/01/19/microsoft-365-groups-report-v2/
How to extract List of member who are in group (Sharepoing365) ?
Steve thank you for this!
can you explain how to make the import of this csv work?
you spoke about there being a larger script somewhere else for Tenant to Tenant Migration?
do you have plans on sharing that?
A very well written script!
The easiest way I have found to export the group members list to a spreadsheet:
Office 365 Admin Center .. Groups .. Active groups .. select the group you want .. Members .. View all and manage members ..
This will show you a list of all the group members. Be careful working with this page because clicking on a member will remove it!
Set your mouse cursor at the top on ‘Group members (nn)’, hold and highlight the list of members all the way down until all members are highlighted.
Do Ctrl and C on the keyboard to copy the highlighted field.
Open a blank spreadsheet (I use Google Sheets). Place your cursor in the first cell [A1] and do Ctrl and V on the keyboard to paste the selected list into the spreadsheet.
It will create one column with names and email addresses. From here you can use column filters to clean out the email addresses or move them out of the way to another column. Then sort the column A–>Z to sort the names alphabetically.
Hello Steve,
How can we get members name last time added in a distribution list ?
Example scenario: We have a DL with 100 members. We added 110 more out of which only 10 are new . We need to fetch the new users names added successfully. Please share a script if you have.
so the script works correctly, I am just not sure how to handle the output. I can see multiple lines created and I can understand the output, BUT I can seperate them like in the picture above. When I seperate them by data with “comma” It still leaves the members in the first row.
Guys please assist, how can I get this script to return distribution lists as well ?
Thanks
Hi Steve,
Thanks for this, I’m about to give it a spin.
An enhancement I’d like to see, if you’re so inclined and if possible, would be to have an Excel macro that takes the final product and generates an individual worksheet for each group, and lists that group’s membership on that sheet.
I’ve been working with a different GroupMembership script I found a while ago that lists each group member of each group on its own row in the CSV, and trying to do that myself with VBA. Never really having done anything before with VBA I’ve been having trouble figuring that part out and that led me to your script here.
Thanks again for making this available to all of us out in the world.
This is good steve i added on part on my code, can you add it too just in case
SharePointSiteURL = $Group.SharePointSiteUrl
Any way to have this show if it’s Teams connected?
There is a small typo here, or maybe it’s just me but
$Groups | %{ $_.MembersSMTP = $_.MembersSMTP -split “`n”;
$_.OwnersSMTP
= $_.OwnersSMTP -split
“`n”;}
Should have been
$Groups | %{ $_.MembersSMTP = $_.MembersSMTP -split “`n”;
$_.OwnersSMTP = $_.OwnersSMTP -split
“`n”;}
Perfect script, next time I’ll give you a beer
Broken script.
Next! đ
Thank you for the script. I’m seen multiple addresses in the same cell. No problem at all.
Regards.
Steve, you wouldn’t have something similar but specifically for PowerBI Workspaces?
Hello,
Thanks for the script .
But the csv file doesn’t providing all the member of the O365 groups . Only 1 member is reported in the CSV file . Do you know what could be the issue?
Is it possible to add in this report by which application ( Teams, Planner, Outlook, Sharepoint online, etc ) the O365 Groups has been created ? how can we can get this information ?
Regarding O365 groups, we are a financial institution and our audit want to keep the control of the creation of the O365 groups . We found the article how to do it ?
https://docs.microsoft.com/en-us/office365/admin/create-groups/manage-creation-of-groups?view=o365-worldwide
But if we are only allowing some users to create the O365 groups , does it mean that all users (having not the right to create groups) who will create their Teams, Sharepoint online site, Planner , then the O365 will not be created for these Teams , sharepoint Online , Planner etc , crated by them ?
Hiya,
When you open the CSV file in Excel, you should find that each line is a multi-value attribute and contains all the members, on individual lines within the cell.
As this is a fairly simple script aimed at getting the basic, raw information – it doesn’t show where the group was created.
I’m not sure what you are asking in the last question – but if I have got it right, then yes – if you create an Office 365 Group via Planner, Teams etc, it provisions across the core Office 365 services like Exchange and SharePoint.
Steve
just one member per group showing up for me also.
I’m also only seeing one member per line – it’s not multi valued.
Works great for me after adding “sep=,” on a new first row in the CSV so that Excel opens it correctly.
I’m also finding it only sees one member per line, because it doesn’t delimit the email addresses – its just email@domain.comemail@domain.comemail@domain.com where they’re all strung together. So the SEP=, doesn’t work because there’s no comma separating the email addresses.
It looked to me at first as though only one member per group were reported. I had to manually increase the row height to see that the additional names were actually there.