Generating SharePoint Site Activity Reports using PnP PowerShell

Monitoring SharePoint site activity is crucial for tracking user interactions, document usage, and security compliance. PnP PowerShell allows administrators to automate site activity reporting efficiently.

This guide provides a step-by-step process to generate SharePoint Site Activity Reports using PnP PowerShell.

---

Prerequisites

Before running the script, ensure you have:

PnP PowerShell Installed
Install it using:

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

Admin Access to SharePoint Online

• You must be a SharePoint Admin or Global Admin to retrieve full site activity reports.

Connection to SharePoint Online

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

---

Step 1: Retrieve SharePoint Site Activity

Get a List of All SharePoint Sites

Get-PnPTenantSite | Select Title, Url, StorageUsage, LastContentModifiedDate

• This retrieves all sites with their storage usage and last modified date.

Get Activity for a Specific SharePoint Site

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

• Lists all document libraries and lists in the site.

---

Step 2: Generate User Activity Report

Get a List of All Users Accessing a Site

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

• Retrieves a list of users with access to the site.

Get Recent File Activity in a Library

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

• Fetches recent file additions and modifications.

---

Step 3: Export SharePoint Site Activity Report to CSV

PowerShell Script to Generate a Full Activity Report

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://yourtenant-admin.sharepoint.com" -Interactive

# Get All Sites
$sites = Get-PnPTenantSite

# Create an Empty Array for Report Data
$report = @()

# Loop Through Each Site and Get Activity Data
foreach ($site in $sites) {
    $siteUrl = $site.Url
    Write-Host "Processing: $siteUrl"

    # Get Site Storage Usage and Last Modified Date
    $siteData = @{
        SiteName      = $site.Title
        SiteUrl       = $site.Url
        StorageUsage  = $site.StorageUsage
        LastModified  = $site.LastContentModifiedDate
    }

    # Get Recent Files Modified in the Site
    $lists = Get-PnPList -Web $site.Url | Where-Object {$_.BaseType -eq "DocumentLibrary"}
    
    foreach ($list in $lists) {
        $items = Get-PnPListItem -List $list.Title -Web $site.Url | Select Title, Created, Modified, Author, Editor
        
        foreach ($item in $items) {
            $report += [PSCustomObject]@{
                SiteName      = $site.Title
                SiteUrl       = $site.Url
                StorageUsage  = $site.StorageUsage
                LastModified  = $site.LastContentModifiedDate
                FileName      = $item.Title
                Created       = $item.Created
                Modified      = $item.Modified
                CreatedBy     = $item.Author
                ModifiedBy    = $item.Editor
            }
        }
    }
}

# Export Report to CSV
$report | Export-Csv -Path "C:\SharePoint_Site_Activity_Report.csv" -NoTypeInformation

Write-Host "SharePoint Site Activity Report generated successfully!"

---

Step 4: Schedule the Script to Run Automatically

To automate report generation:

1. Open Task Scheduler in Windows.
2. Click Create Basic Task → Name it SharePoint Site Activity Report.
3. Set the Trigger to Daily.
4. Set the Action to Start a Program → Browse to powershell.exe.
5. In the Arguments field, enter: -ExecutionPolicy Bypass -File "C:\YourScriptPath\SharePointReport.ps1"
6. Click Finish.

This will generate a report daily and save it as a CSV file.

---

Step 5: Send the Report via Email (Optional)

To email the report after generation:

# Send Email with Report Attachment
$SMTPServer = "smtp.office365.com"
$SMTPPort = "587"
$EmailFrom = "admin@yourdomain.com"
$EmailTo = "report@yourdomain.com"
$EmailSubject = "SharePoint Site Activity Report"
$EmailBody = "Attached is the latest SharePoint activity report."
$Attachment = "C:\SharePoint_Site_Activity_Report.csv"
$Credential = Get-Credential

Send-MailMessage -To $EmailTo -From $EmailFrom -Subject $EmailSubject -Body $EmailBody -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl -Credential $Credential -Attachments $Attachment

---

Step 6: Verify and Analyze the Report

After running the script, check:

C:\SharePoint_Site_Activity_Report.csv for the generated report.
Open it in Excel to analyze:

• Site Names & URLs
• Storage Usage
• Last Modified Dates
• User Activities

---

Troubleshooting Common Issues

Issue 1: PnP PowerShell Commands Not Found

Run:

Update-Module -Name PnP.PowerShell

Issue 2: Connection Error

Use:

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

Issue 3: Report Not Generating Data

• Ensure you have SharePoint Admin permissions.
• Verify the site URLs and document library names are correct.