Identifying Obsolete Office 365 Groups with PowerShell

Tony Redmond

by Tony Redmond on 8/1/2016

Share this:
Print

Article Details

Date Revised:
8/1/2016

Applies to:
Exchange Online, obsolete, office 365 groups, PowerShell, SharePoint Online


Like any resource, Office 365 Groups can become unused over time. Microsoft doesn’t provide any method to find potentially obsolete groups, but some enthusiastic hacking PowerShell to use Office 365 audit records about SharePoint file activity and a check against the Exchange 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. Those that don’t use 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.

Which brings me to the challenge that I want to cover here, which is how to determine what Office 365 groups in a tenant might be obsolete (not being used) so that they can be removed. The requirement to prune old and unwanted repositories has been with us for a very long time. Network file shares and public folders are just two examples from recent history.

The thing about Office 365 Groups is that they are a child of the cloud and don’t belong to any particular workload. Exchange Online provides a 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. Therefore, 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.

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 records SharePoint operations such as files being created or modified. The audit log can therefore be checked 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 audit records are only stored for this period. 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 = $ObsoleteGroups + 1    
         }
      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 = $ObsoleteGroups + 1   
         }
    }
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 64 groups being identified as totally obsolete out of 71. In other words, file activity in SharePoint Online had only occurred in 7 groups over the last three months. This is unsurprising because many of the groups were created for test purposes.

Check audit files of SharePoint Online for Office 365 Groups document libraries that are inactive
Figure 1: Checking SharePoint for obsolete group document libraries

Checking Group Mailboxes for Low Conversation Activity

However, before we rush to clean up the 64 potentially obsolete groups, it’s important to realize that some groups never use SharePoint Online at all because their focus is on threaded conversations or the shared calendar. It is for this reason that Office 365 does not create the SharePoint site for a group until the first time a user attempts to access the document library. By comparison, the shared group mailbox is set up in Exchange Online immediately when a group is created. This is because the creation of a mailbox consumes fewer resources than a site does and the mailbox is required to store the introductory message for the group that’s sent to new group members. We therefore need to check the level of email activity in a group mailbox before deciding whether a group is obsolete.

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 admirably for user mailboxes but fails miserably for group mailboxes. If you run Get-MailboxStatistics against a group mailbox, you’ll invariably see that the LastLogonTime occurred within the last 24 hours, even for groups 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 time that the last item was added to a conversation. 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
   $Data = (Get-MailboxFolderStatistics -Identity $G.Alias -IncludeOldestAndNewestItems -FolderScope Inbox)
   If ($Data.NewestItemReceivedDate -le $WarningDate)
      {
      Write-Host "Last conversation item created in" $G.DisplayName "was" `
             $Data.NewestItemReceivedDate "-> Could be Obsolete?"
      $BadGroups = $BadGroups + 1
      }
   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. Although it might seem reassuring that this check reveals that only 32 obsolete groups exist, it’s immediately obvious that some adjustments are necessary to make the code really work. As you can see, many of the groups only have 1 or 2 items in the Inbox. This implies that they have never been used since they were created and the “new group” notification message was sent. I’ll let the reader figure out how to adjust the PowerShell to include a check for lightly trafficked groups.

Check Exchange Online for inactive Office 365 Group activity
Figure 2: Checking Exchange for inactive group mailboxes

We now have two separate checks that can validate whether a group is obsolete in terms of SharePoint Online or Exchange Online activity. Another piece of work is necessary to take the output from the two checks and use that data to decide what groups are really obsolete and which simply only make use of either SharePoint Online or Exchange Online.

My programming heritage goes back to VAX COBOL and VAX BASIC, so I am no code-cutting expert at this stage. Instead, proving the point that PowerShell can be usefully put to work by even the humblest hacker, I put together a script that combines the code extracts described here with some additional processing, including the generation of an HTML format report (Figure 3) to help you to identify potentially obsolete groups. You can download the script from the TechNet Gallery The challenge now is for others who are more competent in PowerShell to make the code even better!

Report of obsolete Office 365 Groups
Figure 3: Reporting obsolete Office 365 Groups

You can follow Tony Redmond 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.


Topic: PowerShell

Sign in with

Or register

  • Great post! I had a question about this at Microsoft Tech Community and I was directed to this blog. Tony, do you have any additional information of how to check if there was a Planner activity (it does not update the conversations all the time - only on assigned tasks), and if a Power BI is associated with this group. Here is the link to my question for Group activity: https://techcommunity.microsoft.com/t5/Office-365-Groups/Active-vs-Inactive-Office-365-Groups/m-p/28977#M1176
  • Excellent post thank you very much - extremely useful for gaining control of Office 365 Groups.
    (minor mis-placed back-tick at $WarningDate in the first code sample.