Finding Obsolete Office 365 Groups with PowerShell

PowerShellGroupsHero

The Problem of Old Groups

In August 2016, I wrote about a PowerShell script to find obsolete Office 365 Groups and published the article on ITUnity.com. Time moves on and the mission of ITUnity.com is different, but the need to keep some control over user-created groups that are not in active use remains. In fact, the general availability of Microsoft Teams in March 2017 and the attendant increase in the number of groups created means that the need for control is even greater now.

Microsoft now has the group expiration policy, which requires premium Azure Active Directory licenses for all users in groups that come under the control of the policy. The group expiration policy finds potentially-obsolete groups, but does not take activity into account. The decision to ask a group owner to confirm that a group is still active is entirely based on date.,

Apart from the cost, some people have complained that the emailed notifications for expiring groups look like spam and want to be able to generate tenant-customized messages. That feature is not available today.

I dusted off the text of the ITUnity.com article and present an updated version here, including an updated script that checks for Teams activity.

Using PowerShell to Solve Application Administration

Like any resource, Office 365 Groups can become unused over time. Some enthusiastic hacking PowerShell to use Office 365 audit records about SharePoint file activity and a check against conversation items in the group mailbox turns up some interesting results. And best of all, because it’s all relatively simple PowerShell, you can amend the code for your own purposes.

Microsoft is a very large company that is packed with software engineers. However, no matter how many engineers they have, Microsoft will never be able to do everything that customers want. Fortunately, that’s where PowerShell often comes to the rescue. Products that support PowerShell are often easier to work with because you know that if all things fail, scripting often comes to the rescue.

Products that don’t support PowerShell are limited to whatever administrative capabilities are delivered from the imagination of the engineering teams who develop their code, and that can sometimes be very limited. This is one of the reasons why I was so disappointed with the recent release of the beta PowerShell module for Teams.

Groups are Cloud Objects

The thing about Office 365 Groups is that they are a child of the cloud and don’t belong to any specific workload. Exchange Online provides a group mailbox to store group conversations and calendar while SharePoint Online provides a site to store documents and the shared notebook. Some groups are very email-centric while others focus on collaboration around shared documents.

To understand the full picture, we need to look for evidence of obsolescence in both SharePoint and Exchange, which then means that we need to connect a PowerShell session to both SharePoint Online and Exchange Online using an account with administrative permissions so as to be able to read information about the groups. If you want to check Teams, you also need a connection to the Microsoft Teams service.

Checking Groups for Low SharePoint Activity

If you browse the web, you’ll find various techniques suggested for the right way to detect obsolete SharePoint sites. Most revolve around using the LastContentModifiedDate property for the document library of the site belonging to the group. Unfortunately, this property appears to be very unreliable and I could never get solid results by checking it for a range of sites belonging to groups.

Looking for another solution, I remembered that the Office 365 Unified Audit Log captures details of SharePoint operations such as files being created or modified. We can check the audit log using the Search-UnifiedAuditLog cmdlet to determine whether anything has been done for sites belonging to groups.

The code extract shown below checks to see whether any audit records generated by SharePoint file operations over the last 90 days exist for the site belonging to each group. You cannot go back further than 90 days because Office 365 only stores audit records for this period (if you want to hold audit records for longer, you need a use a third-party product like you need to use a third-party solution like Quadrotech Nova).

If no audit records for SharePoint file operations can be found, it’s reasonable to assume that not much document-centric activity has taken place in the site and the site is therefore potentially obsolete.

$WarningDate = (Get-Date).AddDays(-90)
$Today = (Get-Date)
$Groups = Get-UnifiedGroup 

$ObsoleteGroups = 0
ForEach ($G in $Groups) {
   If ($G.SharePointDocumentsUrl -ne $Null)
      {
      $SPOSite = (Get-SPOSite -Identity $G.SharePointDocumentsUrl.replace("/Shared Documents", ""))
      Write-Host "Checking" $SPOSite.Title "..."
      $AuditCheck = $G.SharePointDocumentsUrl + "/*"
      $AuditRecs = 0
      $AuditRecs = (Search-UnifiedAuditLog -RecordType SharePointFileOperation -StartDate $WarningDate -EndDate $Today -ObjectId $AuditCheck -SessionCommand ReturnNextPreviewPage)
      If ($AuditRecs -eq $null) 
         {
         Write-Host "No audit records found for" $SPOSite.Title "-> It is potentially obsolete!"
         $ObsoleteGroups++   
         }
      Else 
         {
         Write-Host $AuditRecs.Count "audit records found for " $SPOSite.Title "the last is dated" $AuditRecs.CreationDate[0]
       }}
   Else
         {
         Write-Host "SharePoint has never been used for the group" $G.DisplayName 
         $ObsoleteGroups++   
         }
    }
Write-Host $ObsoleteGroups "obsolete group document libraries found out of" $Groups.Count "checked"

Figure 1 shows the code in action. Running it against the groups in my tenant resulted in the identification of 131 groups as totally obsolete out of 149. In other words, only 18 groups had recent SharePoint activity. The low usage might not be an issue if the major use of Groups is to host conversations, but it can be a sign that some of these groups have slipped into obsolescence.

Obsolete Groups - SPO
Figure 1: Checking SharePoint for obsolete group document libraries (image credit: Tony Redmond)

(Note to self: when I originally wrote about this topic in August 2016, my tenant only had 71 groups. Now it has 149. An example of group sprawl?)

Checking Group Mailboxes for Low Conversation Activity

Before we rush to clean up obsolete groups, we need to check the level of email activity in a group.

One method of approaching the problem of understanding whether a mailbox is being used is to run the Get-MailboxStatistics cmdlet to retrieve the last time the user logged onto the mailbox (in the LastLogonTime property). This method works for on-premises user mailboxes but fails miserably in the cloud where many more background agents run against mailboxes. If you run Get-MailboxStatistics against a mailbox, you’ll invariably see that the LastLogonTime occurred within the last 24 hours, even for mailboxes that you know have been ignored for months.

However, another approach is available using the Get-MailboxFolderStatistics cmdlet to retrieve information about the Inbox folder from group mailboxes, which holds all the threaded conversations for the group. This code scans group mailboxes and calls the cmdlet to retrieve the NewestItemReceivedDate property for the Inbox, which should tell us the creation time for the last Inbox item. In this instance, we look for groups that have not been active in email terms for the last year.

$Groups = Get-UnifiedGroup
$BadGroups = 0
$WarningDate = (Get-Date).AddDays(-365)

ForEach ($G in $Groups) {
  Write-Host "Checking Inbox traffic for" $G.DisplayName
  $CheckDate = (Get-MailboxFolderStatistics -Identity $G.Alias -IncludeOldestAndNewestItems -FolderScope Inbox).NewestItemReceivedDate
  If ($CheckDate -le $WarningDate)
  {
    Write-Host "Last conversation item created in" $G.DisplayName "was" `
    $Data.NewestItemReceivedDate "-> Could be Obsolete?"
    $BadGroups++
  }
  Else
  {
    Write-Host $G.DisplayName "has" $Data.ItemsInFolder "conversation items amounting to" $Data.FolderSize
  }
}
Write-Host $BadGroups "Obsolete Groups found out of" $Groups.Count

Figure 2 shows the code in action. Some groups only have a few items in the Inbox. This implies that they have never been used since they were created and the “new group” notification message was sent, a fact that seems true when looking at the date of the last conversation.

Obsolete Groups email
Figure 2: Checking conversations in group mailboxes (image credit: Tony Redmond)

Checking Teams Activity

The same technique works to detect whether team-enabled groups are active. When channel conversations occur, Office 365 captures records in the Team Chat sub-folder under Conversation History. We can therefore check that folder to see if any recent records exist.

$TData = (Get-MailboxFolderStatistics -Identity $G.Alias -IncludeOldestAndNewestItems -FolderScope ConversationHistory).NewestItemReceivedDate
If $TData -le $WarningDate)
      {
      Write-Host "Last Teams chat item created in" $G.DisplayName "was" `
             $Data.NewestItemReceivedDate "-> Could be Obsolete?"

Bringing Everything Together

We can check different aspects of activity to understand whether a group is active. The task now is to combine the checks to create a unified picture.

My programming heritage goes back to VAX COBOL and VAX BASIC, so I am no code-cutting expert now. Instead, proving the point that even the humblest hacker can use PowerShell, I created a script that combines the checks with some additional processing, including the generation of an HTML format report (Figure 3) to help you find potentially obsolete groups.

PowerShell Obsolete Office 365 Groups
Figure 3: Reporting Potentially Obsolete Groups (image credit: Tony Redmond)

As I refreshed this article, I updated the original script to include a check whether a group is team-enabled and report the date of the last chat. You can download the script from the TechNet Gallery The challenge now is for others more competent in PowerShell to make the code even better!

Follow Tony on Twitter @12Knocksinna.

Want to know more about how to manage Office 365? Find what you need to know in “Office 365 for IT Pros”, the most comprehensive eBook covering all aspects of Office 365. Available in PDF and EPUB formats (suitable for iBooks) or for Amazon Kindle.