Table of Contents
Combine Information Sources to Identify Obsolete SharePoint Online Sites
A Reddit post described the challenge of identifying and removing obsolete SharePoint Online sites from a Microsoft 365 tenant. The poster identified the manual steps to find and remove a site. All that was needed was some automation to process potentially thousands of sites.
There’s a bunch of stuff to deal with here. The first issue is to identify the obsolete sites. The poster indicated that in their situation., obsolete sites are created by “obsolete users” that contain less than 1 GB of information and have not been modified since 2024.
I don’t really know what an obsolete user is, but let’s try to deal with some of the technical issues involved in scanning for obsolete SharePoint sites with PowerShell.
On the surface, finding sites that haven’t been modified in a while is easy. The SharePoint admin center displays a last activity (UTC) date for each site, and the Get-SPOSite cmdlet returns as LastModifiedDateTime property. Unfortunately, the two values are not the same and the LastModifiedDateTime property cannot be depended upon as a reliable indicator of user activity within a site because the timestamp is altered by background processing. (This post explains the multiple “when the site was last modified” properties that exist in SharePoint Online). The result is that you can’t depend on the last modified date time reported for a site. Another approach is required.
Possible Approaches
When investigating the solution, I considered:
- Using the Microsoft Search Query API to find modified files. This approach works but working with the API is not as easy as it might be. It also means that an individual search request must be run for each site to find modified files.
- Fetch the site activity usage data and combine it with the information fetched with the Get-SPOSite cmdlet. Site usage data is at least two days behind real time, but it has the advantage of including information that might be relevant, such as the total number of files in a site. Also, the last activity date shown in the SharePoint Online admin center appears to use the same date as in the usage data. Get-SPOSite fetches all sorts of interesting information about sites that isn’t available (for example) to the Sites Graph API or the Get-MgSite cmdlet.
- Fetching files from sites to check the last modified date time for individual files. This approach works, but it requires far too much processing and wouldn’t scale.
Given all the considerations, I decided to use the combination of site activity data plus site information.
Solving the SharePoint Usage Data Problem
Rich data for a site is available by combining the site activity data from the usage report API and the information fetched for each site by the Get-SPOSite cmdlet. This is despite a problem with the site activity data that goes back a few years. For whatever reason, Microsoft doesn’t output the site URL in the usage data, so can’t use the site URL as an identifier.
To fetch the usage data, we define a temporary CSV file to accept the output, run the request, and load the output into an array:
$TempDownLoadFile = "c:\temp\download.csv" $Uri = "https://graph.microsoft.com/v1.0/reports/getSharePointSiteUsageDetail(period='D180')" Invoke-MgGraphRequest -Uri $Uri -Method GET -OutputFilePath $TempDownloadFile [array]$SitesActivityData = Import-CSV $TempDownloadFile
Examining the information or a site, we see that the site URL is missing. A “Site Id” property is present, but you have to convert the identifier into a site URL before you can combine the usage data with the basic site information.
Report Refresh Date : 2026-04-13 Site Id : 006453d7-91a7-401c-9fa5-c3f302ecc2af Site URL : Owner Display Name : DG Executive Committee Mountaineering Is Deleted : False Last Activity Date : File Count : 2 Active File Count : 0 Page View Count : 0 Visited Page Count : 0 Storage Used (Byte) : 1667836 Storage Allocated (Byte) : 27487790694400 Root Web Template : Group Owner Principal Name : Mountaineering Report Period : 180
Fortunately, it’s possible to use the site identifier with the Get-MgSite cmdlet to return site details, including its URL. A simple loop processes the usage data and loads it into a hash table in a form that we can use later.
An App Combines and Outputs the Data
I then created an Entra ID app to run with application permissions to process every site in the tenant (excluding sites like those owned by Teams private and shared channels and redirect sites) and download the usage data. Also, the app reads the owner information for sites belonging to Microsoft 365 groups.
I put together a script (available from GitHub) to show what can be done, using the script to report SharePoint Online site storage as a starting point.
The script uses the Microsoft Graph PowerShell SDK and the SharePoint Online management modules and signs into SharePoint Online and the Microsoft Graph to fetch the site and usage data. Because the script needs to access all sites, it runs in app-only mode, meaning that it signs in with an application identifier, tenant identifier, and X.509 certificate thumbprint. The app uses the Sites.Read.All permission to access site information. This is a high-priority permission in terms of its attractiveness to attackers. However, when you need to process all sites in a tenant, the app needs that level of permission.
After fetching the site and usage data, the script loops through the sites to create a report detailing the attributes that might be used to determine if a site is active, such as the last active data, number of files, storage quota consumed, and so on. The rules used to figure out whether a site is obsolete are easily changed to meet organizational requirements. The output is either a CSV file or Excel worksheet containing details of the processed sites. Figure 1 shows some example output.
The intention is that administrators would use the output files to review the sites identified as obsolete and confirm whether the sites should be removed.
Next Step
Following a review, the next step is to delete the obsolete sites. I haven’t coded that bit up yet, but the way I’d approach it is to use the CSV file (edited to remove the sites to keep) as input and process each site in the list. Things get complicated when a site is connected to a Microsoft 365 group because you should remove the group and let the site be deleted as part of that process. Retention policies can also get in the way and block site removal. More challenges to solve!
Need help to write and manage PowerShell scripts for Microsoft 365, including Azure Automation runbooks? Get a copy of the Automating Microsoft 365 with PowerShell eBook, available standalone or as part of the Microsoft 365 for IT Pros eBook bundle.