How to Use PowerShell to Report Retention Policies for SharePoint Online Sites

Getting a Global View of Retention

A questioner asked how to find out how which Office 365 retention policies process different SharePoint sites in their tenant. This is a reasonable ask because the Security and Compliance Center (SCC) focuses on managing policies on an individual basis and doesn’t present an overall view of retention across the tenant.

Finding Policies

Because there’s no GUI option to present a global view of how a set of retention policies apply to a workload like SharePoint, we have to roll our own solution. PowerShell is often the best tool in these circumstances because it’s reasonably quick to develop in and Office 365 publishes a very large set of cmdlets, albeit spread over multiple modules.

In this case, the first step is form a collection of the retention policies in the tenant by running the Get-RetentionCompliancePolicy cmdlet. This cmdlet is part of the compliance set, which are available when you connect to that endpoint. The easiest way to do this is by running the Connect-IPPSSession cmdlet from the Exchange Online Management module. Your account must hold either the Exchange Online administrator or Global administrator role to run the commands described in this article.

The critical point when working with retention locations is to include the DistributionDetail parameter when calling Get-RetentionCompliancePolicy as this forces the SCC to return details of the locations to which each policy applies. The set of returned policies is further refined by excluding those that don’t process SharePoint and any defined for Teams (retention policies for Teams only process Teams locations).

Interpreting Policies

After figuring out the set of retention policies for SharePoint, we can examine the policies to extract details of the SharePoint locations that they process. A policy will tell us that the location is:

• Null: SharePoint is not processed by the policy.
• All: The policy processes all SharePoint sites.
• All with exclusions: The policy processes all SharePoint sites except those listed in the SharePointLocationException property.
• Some: The policy processes only the SharePoint sites listed in the SharePointLocation property.

The only slightly tricky thing is to handle when sites are individually included or included. This is done by expanding the property to extract all the listed sites and then processing the details for site.

Putting everything together, we end up with a script.

Connect-IPPSSession
$Report = [System.Collections.Generic.List[Object]]::new() 
[array]$Policies = (Get-RetentionCompliancePolicy -ExcludeTeamsPolicy -DistributionDetail | ? {$_.SharePointLocation -ne $Null})
ForEach ($P in $Policies) {
        If ($P.SharePointLocation.Name -eq "All") {
            $ReportLine = [PSCustomObject]@{
              PolicyName = $P.Name
              SiteName   = "All SharePoint Sites"
              SiteURL    = "All SharePoint Sites" }
            $Report.Add($ReportLine) } 
            If ($P.SharePointLocationException -ne $Null) {
               $Locations = ($P | Select -ExpandProperty SharePointLocationException)
               ForEach ($L in $Locations) {
                  $Exception = "*Exclude* " + $L.DisplayName
                  $ReportLine = [PSCustomObject]@{
                    PolicyName = $P.Name
                    SiteName   = $Exception
                    SiteURL    = $L.Name }
               $Report.($ReportLine) }
        }
        ElseIf ($P.SharePointLocation.Name -ne "All") {
           $Locations = ($P | Select -ExpandProperty SharePointLocation)
           ForEach ($L in $Locations) {
               $ReportLine = [PSCustomObject]@{
                  PolicyName = $P.Name
                  SiteName   = $L.DisplayName
                  SiteURL    = $L.Name }
               $Report.Add($ReportLine)  }                    
          }
}

Much the same approach can be used to extract information about the other locations supported by Office 365 retention policies (Exchange, Office 365 Groups, OneDrive for Business).

The output is an ordered array, which we can look at in different ways. Here’s how to list it by policy order:

$Report | Sort PolicyName, SiteUrl

PolicyName                               SiteName                     SiteURL
----------                               --------                     -------
Company Confidential Policy              All SharePoint Sites         All SharePoint Sites
Formal Company Records                   All SharePoint Sites         All SharePoint Sites
GDPR Personal Data                       All SharePoint Sites         All SharePoint Sites
GDPR Personal Data                       *Exclude* PL Test Group      https://office365itpros.sharep
Management Preservation Policy           Projects                     https://office365itpros.sharep
Office 365 for IT Pros eBook Content     All SharePoint Sites         All SharePoint Sites
Preservation Lock - Mailboxes and Sites  PL Test Group                https://office365itpros.sharep
Preserve Office 365 for IT Pros Files    Company Communications       https://office365itpros.sharep
Preserve Office 365 for IT Pros Files    GDPR Planning Mark II        https://office365itpros.sharep
Preserve Office 365 for IT Pros Files    Office 365 for IT Pros       https://office365itpros.sharep
Senior Leadership Team (SLT) Policy      SLT                          https://office365itpros.sharep
SharePoint Online Retention Policy       All SharePoint Sites         All SharePoint Sites

Of course, we can export the array to a CSV file and look at the data with Excel or import it into Power BI for more heavy-duty analysis and graphing.

$Report | Export-Csv -NoTypeInformation c:\temp\RetentionSites.csv

PowerShell to the rescue once again!

---

For more information about Office 365 retention policies, read Chapter 19 of the Office 365 for IT Pros eBook.