One of the recent solutions I worked on is a Sharepoint site audit solution. For the requirements of being able to extract the Sharepoint site collections in the tenant and its Site owners to tag them accordingly to their Organization, department, unit, sensitive data, PII data. To continuously check for newly created sites I used scheduled runbook on Azure with Powershell.
Automated Data Extraction: A weekly Azure Runbook executes a PowerShell script using PnP PowerShell and Microsoft Graph API to scan the tenant. It automatically retrieves all SharePoint site collections, Teams channels, associated hub sites, and site owners, updating a central SharePoint list.
Owner Tagging & Governance: Site owners use a custom Canvas Power App connected to the SharePoint list to review their sites, assign departmental and organizational ownership (Department, Unit, Contact Person), and flag whether the site contains PII or sensitive data.
Reporting & Visibility: A Power BI dashboard connects directly to the tagged inventory, providing real-time compliance reporting and auditing insights for leadership.
Challenges and learnings:
When you are dealing with thousands of sharepoint sites you would want to be using batching (batch update, delete).
The PnP powershell doesnt return some properties such as Hubsite Id, site guid and few others from the Get-PnPTenantSite and instead you will be forced to get it for each Sharepoint site (causing multiple requests instead of single request)