Using Power Automate and Azure Automation to Manage the Lifecycle of SharePoint Sites

SharePoint Sprawl is a Real Problem

A key aspect of a well-managed Microsoft 365 environment is to ensure that objects such as SharePoint sites (and by extension, Microsoft Teams) are removed when they are no longer needed. I’ve spoken to many organizations who have deployed their Microsoft 365 tenant without any consideration about how to manage the lifecycle of sites. The end result is usually regret. While the problem doesn’t usually appear for the first couple of years, over time tenant admins begin to notice that the proliferation of objects in their tenant makes day-to-day management very difficult. For example, environments lacking appropriate governance tend to have large amounts of obsolete data spread across large numbers of SharePoint sites, leading to admin and user confusion.

In this article, I explore how to combine relatively basic PowerShell and Azure Automation skills with Power Automate to create a clean, robust way to keep your Microsoft 365 tenant free from excessive sprawl.

Why Group Expiration Policies Might Not Work for You

Microsoft’s answer to group/team sprawl is the group expiration policy. At a basic level, the expiration works fine as a simple way to remove older or inactive Groups within a Microsoft 365 tenant, although limited signals from the Microsoft Graph can make activity-based expiration unreliable. Expiration policies allow Group owners to approve the removal of a group or to renew the group to prevent deletion, sending multiple reminders to users to attempt to ensure renewals aren’t missed.

The problem with the expiration policy is when your requirements don’t neatly fit into Microsoft’s vision for group expiration. For example, only a single Group expiration policy is supported per tenant. All groups that come within the scope of the expiration policy have the same expiration period. Another example is when you have Groups that you don’t want to include in the expiration policy. An expiration policy has two settings for assignment: either all groups or selected groups (up to a maximum of 500). Additionally, group expiration policies (as you can tell by the name) focus exclusively on group-connected team sites and do not cover any other SharePoint Online sites, such as Communication sites. Group expiration policies also require Entra ID Premium licensing which can be a blocker for some organizations.

While expiration policies are useful in some situations, the lack of flexibility and customization to control group expiration often convinces tenants to explore some form of custom solution.

Combining Azure Automation and Power Automate

Numerous articles on Practical 365 show the value of Azure Automation when faced with the need to automate administrative operations. For example, I previously wrote on a similar topic of keeping Teams tidy with Automation. For repeatable, programmatic tasks, Azure Automation is a fantastic tool for any tenant admin to have in their toolbox. Azure Automation can do much of the heavy lifting for this solution but one thing it can’t deliver (due to the nature of Azure Automation running simple scripts on a hosted Linux box) is the approvals process for object deletion.

That’s where Power Automate comes in. Specifically, the approvals functionality in Power Automate is an easy way to create user-friendly approval flows. Approval notifications can be managed in both Teams and Outlook for users, making responding to expiration notifications relatively painless.

Finally, a list of sites to include and their associated expiration times needs to be maintained. Multiple options exist to store a list of groups subject to expiration checks such as a CSV file in SharePoint or Azure Storage or a Dataverse table. For this example, I use a SharePoint list as it is easy to maintain and supports basic version history and audit logging. With everything in place, the process flow follows the outline in Figure 1.

SharePoint Tracking list

To track approval status, I use a SharePoint list to hold the key details required to manage expiration for sites. The list contains the columns shown in Table 1.

The tracking list (Figure 2) holds the details and current status of each site within the scope of the expiration process. If a site is not on the list then it is not subject to expiration.

When creating the tracking list, make sure to index the “Deletedon”, “MarkedforDeletion” and “SiteURL” columns to make them searchable from Graph API requests. The index settings for this list are shown in Figure 3.

Power Automate Flow

Power Automate is very flexible and the flow to process approvals is customizable to meet organizational requirements. I won’t delve into the low-level details of creating Power Automate flows here but I recommend reading Teams and Power Automate: Practical Examples to Automate Tasks if you are unfamiliar with Power Automate.

The Power Automate flow for the expiration process starts with a scheduled trigger to run once per day. Each day, the flow will check the tracking list and, if the “RemainingDays” value is not already 0, it is reduced by. The trigger process is outlined in Figure 4.

If the “RemainingDays” value is already at 0, then the site is expired, and the approval process is run. The “MarkedForDeletion” column is updated with the value “PendingApproval” and an approval is sent to the users listed in the Approver column. The configuration for the flow is shown in Figure 5.

The approval is then sent to the list of approvers through both Teams and Outlook. The approvers have the option to approve the deletion or to reject and reset the lifetime of the group again (Figure 6).

Finally, if one of the approvers approves the request, the flow updates the “MarkedforDeletion” field to “Yes”. If they reject the request, the flow resets the “RemainingDays” to match the “RetentionDays” value, restarting the expiration time (Figure 7).

Azure Automation Runbook

The Automation Account and runbook is the final piece of configuration needed for the solution. The code I use in this example is available on GitHub. The runbook uses four PowerShell modules, which need to be added as resources for the automation account:

• Microsoft.Graph.Authentication is needed to authenticate to the Microsoft Graph API.
• Microsoft.Graph.Groups is needed to process the deletion of Group connected sites.
• Microsoft.Graph.Sites is needed to gather data on each site.
• PnP.PowerShell is required to process the deletion of Communication sites as this is not yet supported by the Microsoft Graph API.

For more information on installing and updating modules for Azure Automation check out this article.

The Automation Account uses a system-assigned managed identity to authenticate to the Microsoft Graph and PnP PowerShell – similar to the explanation in this article. The permissions required to be assigned to the service principal of the managed identity are:

• Microsoft Graph – Group.ReadWrite.All – Required to delete Group connected sites
• Microsoft Graph – Sites.FullControl.All – Required to retrieve site information and manage sites
• Office 365 SharePoint Online – Sites.Manage.All – Required to delete communication sites through PNP PowerShell

This sample code shows how to add Graph and SharePoint permissions to an Azure Automation account. The code first lists the Graph permissions required, gets the Automation Account service principal ID, gets the service principal of the Microsoft Graph application, and then assigns the permissions to the Automation Account. This process is then repeated for the Office 365 SharePoint Online app. This process is detailed further in this article on Using Azure Automation Managed Identities with RBAC for Applications.

##List Required Permissions
$Permissions = @(
    "Group.ReadWrite.All"
    "Sites.FullControl.All"
)
##Add Managed Identity ID
$MIID = "<Replace with Managed Identity Service Principal ID>"”
##Get Roles for Permissions
$GraphApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0000-c000-000000000000'" # Microsoft Graph
[array]$Roles = $GraphApp.AppRoles | Where-Object {$Permissions -contains $_.Value}
##Loop through and add each role
foreach($role in $roles){
    $AppRoleAssignment = @{
        "PrincipalId” = $MIID
        "ResourceId" = $GraphApp.Id
        "AppRoleId" = $Role.Id
    }
    # Assign the Graph permission
    New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $MIID -BodyParameter $AppRoleAssignment
}
##List Required Permissions
$Permissions = @(
    "Sites.Manage.All"
)
##Get Roles for Permissions
$SPApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0ff1-ce00-000000000000'" # SharePoint Online
[array]$Roles = $SPApp.AppRoles | Where-Object {$Permissions -contains $_.Value}
##Loop through and add each role
foreach($role in $roles){
    $AppRoleAssignment = @{
        "PrincipalId" = $MIID
        "ResourceId" = $SPApp.Id
        "AppRoleId" = $Role.Id
    }
    # Assign the SharePoint permission
    New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $MIID -BodyParameter $AppRoleAssignment
}

When the script runs, it detects list items that are marked for deletion and processes the deletion of the associated site. Once the object is processed for deletion, the deletion date is added to the “DeletedOn” field in the tracking list. If there are no objects marked for deletion, the script exits.

A Flexible Solution

In this article, I’ve outlined one way to use Power Automate and Azure Automation to solve a real-world problem. There is also a lot that can be done to improve this process, for example, looking at recent activity on sites as a metric for expiration would be nice. I’ve also included minimal error handling here to keep things relatively on point.

Combining the Microsoft tools together as I’ve shown here can help tenant admins complete some surprisingly complex tasks with a relatively simple automated process. In the future, Microsoft might deliver a better solution to manage the expiration of sites and Teams. Until then, administrators can always build their own solutions and share them with the community.