close
Office 365

Exporting Office 365 Group membership to a CSV file

Office365_Logo

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 is 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, it’s 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:

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:

Abdulsalam Garba

The author Abdulsalam Garba

Leave a Response