How to Consolidate SharePoint Hub Site Usage Reports
Question details
The user needs a way to automatically download and merge site usage analytics from multiple SharePoint hub sites into a single location in OneDrive.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Automating the extraction and consolidation of site usage analytics across multiple distinct SharePoint hub sites.
- Observed behavior
- SharePoint does not offer a native, built-in feature to automatically combine and export usage reports from multiple hub sites to OneDrive.
Ensure you have SharePoint Administrator permissions and an active Microsoft Power Automate license to create custom data extraction workflows.
Use Power Automate to Collect and Consolidate Reports
Create a custom automated flow to retrieve site usage data from SharePoint via API, combine the results, and save the final report to OneDrive.
Because SharePoint lacks a native one-click export for consolidating reports across multiple hub sites, Power Automate serves as the ideal bridge. By scheduling a flow, you can query SharePoint's usage APIs, parse the retrieved data, and automatically save it as a consolidated CSV or Excel file into your OneDrive account.
Open Microsoft Power Automate, click on 'Create' in the left menu, and select 'Scheduled cloud flow'. Set the frequency (e.g., weekly or monthly) for when you want the usage reports to be generated.
Add the 'Send an HTTP request to SharePoint' action. Configure the site address and use the appropriate REST API endpoint (such as the SiteUsage endpoint) to fetch the analytics data for your target hub sites.
Use 'Data Operations' actions, such as 'Parse JSON' and 'Select', to filter the relevant usage metrics from the API response and map them into a structured table format.
Add the 'Create file' action under the OneDrive for Business connector. Specify your destination folder path, name the file with a .csv extension, and map the output from your data operation into the 'File Content' field.
Analyze Your Consolidated Reports with WPS Office
Once your SharePoint usage data is safely exported to OneDrive as CSV or Excel files, use WPS Office as a fast, highly compatible, and free alternative to Microsoft Office to analyze your metrics.
- 1. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' from the home screen.
- 2. Import Your Report: Click 'Open', navigate to your local OneDrive sync folder, and select the consolidated CSV or Excel usage report you generated.
- 3. Visualize Your Data: Highlight your data and use the 'Insert' tab to add PivotTables or Charts to easily spot trends in your SharePoint site usage.
Frequently Asked Questions
Can I download usage reports directly from the SharePoint admin center?
Yes, you can download usage reports for individual sites directly from the Microsoft 365 Admin Center by navigating to Reports > Usage. However, natively combining data from multiple hub sites at once still requires manual processing or automated scripts.
Does SharePoint support native consolidated analytics for multiple hub sites?
No, SharePoint natively tracks usage on a per-site or per-hub basis but does not feature a built-in dashboard or export tool that aggregates analytics from multiple unconnected hub sites into a single downloadable file.
What permissions are needed to automate SharePoint reports via Power Automate?
To successfully query site usage APIs and compile tenant-wide reports, the account running the Power Automate flow typically requires SharePoint Administrator or Global Administrator privileges.
