Create Office 365 Group Reports with PowerShell Script (Export CSV)
Create Office 365 Group Reports with PowerShell. In this post, we will be discussing how to create an Office 365 Group report using a Powershell script.
What is an Office 365 Group?
An Office 365 group, which is currently known as a Microsoft 365 group, is an object in Azure Active Directory that holds a list of members that are attached to related Office 365 workloads, including a SharePoint team site, an Exchange mailbox, Planner, One Note, and Power BI.
- Public: This can be joined by users, without getting approval from a group owner. Anyone can access the group content.
- Private: This is not open for everyone to join, and group owners are the ones that can add members to them. Only members can access the group content.
Types of Office 365 Group Reports
Office 365 Administrators have some routine tasks that could include regularly auditing Office 365 Groups in the tenant.
The auditing of these groups will assist the administrator when cleaning up the tenant, checking for mistakenly created groups by users (if users are not blocked from creating Office 365 groups), during on-boarding and separation of users to update internal documentation.
- A report displaying the owner(s) of an Office 365 Group
- A report displaying the owner(s) of all Office 365 Groups on a tenant
- A report showing the date of the creation of an Office 365 group
- A report showing the date of creation of all Office 365 groups on a tenant
- A report showing the privacy status of an Office 365 group
- A report showing the privacy status of all Office 365 groups on a tenant
- A report showing the number of members in an Office 365 group
- A report showing the number of members in all Office 365 groups on a tenant
- A report showing the number of Office 365 groups in the tenant
- A report showing the list of members in an Office 365 group
- A report showing the list of all Office 365 Groups and their members in a tenant
Generating Office 365 Group Reports using PowerShell
Installing the Exchange Online Management Module
To gain access to this module, first, we would have to install the module on the PC we are going to use to generate the report.
# set the execution policy of the process
Set-ExecutionPolicy -Scope Process Unrestricted -Force
# Install Exchange Online Module
Install-Module -Name 'ExchangeOnlineManagement' -Force
Importing the Exchange Online Management Module
# Import Exchange Online Module
Import-Module -Name 'ExchangeOnlineManagement'-Force
There are several Office 365 Group reports that we have listed in this article, and we are going to generate a combination of these reports using majorly the Get-UnifiedGroup and Get-UnifiedGroupLinks Exchange Online PowerShell CMDLETs.
- Get-UnifiedGroup: This cmdlet gets the Office 365 groups in an organization’s Exchange Online environment.
- Get-UnifiedGroupLinks: This cmdlet gets the membership information for Office 365 Groups in an organization’s Exchange Online environment.
Connecting to Exchange Online
We will first need to connect to Exchange Online to gain access to these CMDLETs using the cmdlets below:
# save tenant admin email in the variable
$AdminUpn = 'admin@contoso.onmicrosoft.com'
# connect to exchange online powershell
Connect-ExchangeOnline -UserPrincipalName $AdminUpn
Export Office 365 Group Members to csv using PowerShell
- Group Display Name
- Group PrimarySmtpAddress
- When Created (UTC)
- Privacy Status
- Group Member Count
- Group External Member Count
- Dynamic MemberShip Status
- Owners
- Display Name of Members
- Members’ PrimarySmtpAddress
Below is the PowerShell script that generates this Office 365 Group member csv report:
Note: Change the $ReportName variable to suit your desired output file path and file name. Also, the “.\” represents the present working directory (you can get this information by running the following cmdlets in your PowerShell session: PWD or Get-Location).
“.\” can be changed to any desired directory on the machine (E.g C:\Windows\)
# set the name of the report alongside the path ".\ relative path"
$ReportName = ".\Office365GroupReport.csv"
# get office 365 group
$GroupDetails = Get-UnifiedGroup -ResultSize Unlimited | Select-Object PrimarySmtpAddress, DisplayName, ManagedByDetails, `
WhenCreatedUTC, AccessType, GroupMemberCount, GroupExternalMemberCount, IsMembershipDynamic
# set export array
$ExportArray = @()
# loop through each group
Foreach ($GroupDetail in $GroupDetails) {
# set the group primary smtp address
$GroupUpn = $GroupDetail.PrimarySmtpAddress.ToString()
# get office 365 group members
$GroupMembersDetails = Get-UnifiedGroupLinks -ResultSize Unlimited -Identity $GroupUpn -LinkType Members `
| Select-Object Name, PrimarySmtpAddress
# add values to array
$ExportArray += [PSCustomObject][Ordered]@{
# get office 365 group display name
"Group Display Name" = $GroupDetail.DisplayName.ToString()
# get office 365 group primary smtp address
"Group PrimarySmtpAddress" = $GroupUpn
# get office 365 group creation date
"When Created (UTC)" = $GroupDetail.WhenCreatedUTC.ToString()
# get office 365 privacy status
"Privacy Status" = $GroupDetail.AccessType.ToString()
# get office 365 group member count
"Group Member Count" = $GroupDetail.GroupMemberCount.ToString()
# get office 365 group external member count
"Group External Member Count" = $GroupDetail.GroupExternalMemberCount.ToString()
# get dynamic memberShip status
"Dynamic MemberShip Status" = $GroupDetail.IsMembershipDynamic.ToString()
# get office 365 group owner(s)
"Owners" = ($GroupDetail.ManagedByDetails | Out-String).Trim()
# get the display name of the members
"Display Name of Members" = ($GroupMembersDetails.Name | Out-String).Trim()
# get the primary smtp address of the members
"Members' PrimarySmtpAddress" = ($GroupMembersDetails.PrimarySmtpAddress | Out-String).Trim()
}
}
# export to csv file
$ExportArray | Export-Csv -Path $ReportName -Delimiter "," -NoTypeInformation
Modifications to Excel Office 365 Group Members CSV Export Report
Once the report is open in Excel, right-click on any column heading and select the Format Cells… option.
The above steps will give you a detailed view of the reports in Microsoft Excel as displayed below:
Create Office 365 Group Reports with PowerShell Conclusion
In this article we covered the steps to Create Office 365 Group Reports with our PowerShell script. Check out our Office 365 reporting tool, that allows you to report on Active Directory and Office 365 users.
