Reporting Group-Enabled SharePoint Online Sites

A Little Detail Makes the Difference

A reader asked how easy it might be to find out what SharePoint Online sites are connected to Office 365 Groups and Teams and how the link works. Well, the easy answer is to say that you can get a list of group-enabled sites by running the Get-SPOSite cmdlet as follows:

# Fetch a list of SharePoint Online sites created using the Office 365 Groups template
Get-SPOSite -Template "GROUP#0" -IncludePersonalSite:$False -Limit All

However, the output is a simple list that’s not very interesting. The list also includes sites belonging to deleted groups that are being retained by Office 365 because of a retention policy. Things get a lot more interesting when you use the group identifier for a site to look at some group information. To have SharePoint return the group identifier, you must pass the Detailed parameter to Get-SPOSite. This slows down processing, but it means that you get a group identifier. The group identifier (a GUID) can be used with the Get-AzureADGroup, Get-UnifiedGroup, and Get-Team cmdlets (alas, all in separate PowerShell modules) to fetch different information about the group. You can also use the identifier with other cmdlets to retrieve group member information, and so on.

Scripting a Report of Group-Enabled Sites

Equipped with the knowledge of how to get to group information, we can build a script to generate a report. Here’s a rough and ready script to do the job.

# Report Group-enabled SharePoint sites
# Find all sets created with the Office 365 Group template and reports them in a CSV file. Checks if group exists for the site
# and if it is enabled for Microsoft Teams
Cls
Write-Host "Analyzing SharePoint sites created with the Office 365 Groups template..."
$Sites = Get-SPOSite -Template "GROUP#0" -IncludePersonalSite:$False -Limit All
$Report = [System.Collections.Generic.List[Object]]::new()
$ErrorSites = 0
$i = 0
Foreach ($Site in $Sites) {
   $i++ 
   $ProgressBar = "Processing site " + $Site.Title + " (" + $i + " of " + $Sites.Count + ")"
   Write-Progress -Activity "Checking SharePoint Sites" -Status $ProgressBar -PercentComplete ($i/$Sites.Count*100)
   $GroupId = (Get-SpoSite $Site.Url -Detailed).GroupId.Guid
   # Check if the Office 365 Group exists
   $ErrorText = $Null
   $O365Group = $Null
   $O365Group = (Get-UnifiedGroup -Identity $GroupId -ErrorAction SilentlyContinue)
   If ($O365Group -eq $Null) {
       $O365GroupName = "Unknown Office 365 Group"
       $ErrorText = "Failed to find Office 365 Group for site " + $Site.Title + " identifier " + $GroupId
       $O365GroupMembers = 0
       $ErrorSites++ }
   Else {
       $O365GroupName = $O365Group.DisplayName
       $O365GroupMembers = $O365Group.GroupMemberCount
       # Check if site has a team
       $TeamsEnabled = $True
       Try {Get-Team -GroupId $GroupId | Out-Null}
          Catch {$TeamsEnabled = $False} }
   # Generate a line
   $ReportLine = [PSCustomObject]@{
    Site         = $Site.Title
    URL          = $Site.URL
    LastContent  = $Site.LastContentModifiedDate
    O365Group    = $O365GroupName
    Members      = $O365GroupMembers
    TeamEnabled  = $TeamsEnabled     
    Error        = $ErrorText }
  $Report.Add($ReportLine)
}
Write-Host "All done" $ErrorSites "sites reported errors"
$Report | Sort Site | Export-CSV c:\temp\Sites.csv -NoTypeInformation

As with all my scripts, there’s lots that could be done to improve the robustness of the code, improve error handling, and so on. Creating a production-ready script is not the point. What I want to illustrate is how to exploit the connection between SharePoint Online and other PowerShell modules.

Reviewing the SharePoint Online Sites Report

The report is output as a CSV file. You can add extra columns to the file by adding properties to the table generated by the script, which I’ve limited to site title, URL, the last date for content, the name of the Office 365 group, count of group members, a True/False flag to show if the group is enabled for Teams, and some error text. The date for last content modified is unreliable as content can be modified by background activities, so it’s included as an example of using information returned by Get-SPOSite. Figure 1 shows an example of the CSV file.

The script highlights sites that have a group identifier that no longer points to a valid Office 365 group. These situations are likely to be caused by groups that have been removed but the site remains, probably because an Office 365 retention policy governs the site. You can try to remove the site in the SharePoint Online Admin Center: if you see an error, you know that a retention policy is blocking the deletion.

I look forward to hearing how people amend or expand the script to meet different circumstances. Please post a comment if you do.

---

For more information about working with SharePoint Online, Groups, and Teams through SharePoint, see the hundreds of code examples included in the Office 365 for IT Pros eBook. We’re bound to have something that will make you think!