Generating a SharePoint Usage Dashboard using PnP PowerShell

A SharePoint Usage Dashboard provides insights into site activity, storage consumption, user engagement, and document interactions. Using PnP PowerShell, administrators can:

Extract usage analytics from SharePoint
Generate reports on storage, file activity, and user access
Automate dashboard updates for better governance

Prerequisites

Before generating the dashboard, ensure:

PnP PowerShell is installed

Install-Module -Name PnP.PowerShell -Force -AllowClobber

You are connected to SharePoint Online

Connect-PnPOnline -Url "https://yourtenant-admin.sharepoint.com" -Interactive

You have SharePoint Admin permissions

Step 1: Extracting Site Storage Usage Data

To retrieve storage metrics for a SharePoint site:

Get-PnPTenantSite -Url "https://yourtenant.sharepoint.com/sites/YourSite" | Select Url, StorageQuota, StorageQuotaWarningLevel, StorageUsage

Displays storage quota, warning levels, and usage for the site.

To fetch storage data for all sites:

Get-PnPTenantSite | Select Url, StorageQuota, StorageQuotaWarningLevel, StorageUsage | Export-Csv -Path "C:\SharePointStorageReport.csv" -NoTypeInformation

Saves a storage usage report as a CSV file.

Step 2: Analyzing Active Users on a Site

To list users who accessed the site recently:

Get-PnPUser -Web "https://yourtenant.sharepoint.com/sites/YourSite"

Fetches all users with access to the site.

To export user activity data:

Get-PnPUser -Web "https://yourtenant.sharepoint.com/sites/YourSite" | Select LoginName, Email, Title | Export-Csv -Path "C:\UserActivityReport.csv" -NoTypeInformation

Saves user details and activity into a CSV file.

Step 3: Retrieving Document Usage and Activity

To get a list of recently modified documents:

Get-PnPListItem -List "Documents" | Select Title, Modified, Author

Displays recent document edits on the site.

To export document activity data:

Get-PnPListItem -List "Documents" | Select Title, Modified, Author | Export-Csv -Path "C:\DocumentActivityReport.csv" -NoTypeInformation

Saves document activity details into a CSV report.

Step 4: Fetching Page Views and Traffic Data

To track page views, use:

$siteUrl = "https://yourtenant.sharepoint.com/sites/YourSite"
$pages = Get-PnPListItem -List "Site Pages"
foreach ($page in $pages) {
    Write-Host "Page: $($page.FieldValues["FileLeafRef"]) - Modified: $($page.FieldValues["Modified"])" -ForegroundColor Cyan
}

Displays all pages and their last modified date.

To export page view data:

$pages | Select FieldValues | Export-Csv -Path "C:\PageViewsReport.csv" -NoTypeInformation

Saves page activity insights into a CSV file.

Step 5: Automating the Dashboard Report Generation

You can schedule a PowerShell script to generate and email reports:

$storageReport = "C:\SharePointStorageReport.csv"
$userReport = "C:\UserActivityReport.csv"
$documentReport = "C:\DocumentActivityReport.csv"
$pageReport = "C:\PageViewsReport.csv"

$body = "Hello, <br><br> Please find the attached SharePoint usage reports. <br><br> Regards, <br> IT Team"
Send-MailMessage -To "admin@yourcompany.com" -From "noreply@yourcompany.com" -Subject "SharePoint Usage Report" -Body $body -Attachments $storageReport, $userReport, $documentReport, $pageReport -SmtpServer "smtp.yourcompany.com"

Emails the SharePoint Usage Dashboard reports to administrators.

Step 6: Visualizing Data in Power BI

To integrate reports into Power BI:

1️⃣ Open Power BI Desktop
2️⃣ Import the CSV files (SharePointStorageReport.csv, etc.)
3️⃣ Create charts for storage, users, documents, and page views
4️⃣ Schedule auto-refresh for live data updates

Troubleshooting Common Issues

1️⃣ Unable to Fetch Site Data?

Ensure you are connected to SharePoint Admin Center
Verify permissions using Get-PnPUser

2️⃣ Reports Not Generating?

Run Update-Module PnP.PowerShell
Check file paths and permissions