OneDrive File Sharing Report using PnP PowerShell

Tracking file sharing in OneDrive for Business is crucial for security, compliance, and auditing. With PnP PowerShell, administrators can generate a detailed file-sharing report, including information on:
Files shared internally and externally
Users who shared the files
Permissions assigned to recipients
Expiration details of shared links

This guide provides a step-by-step approach to extracting OneDrive file-sharing reports using PnP PowerShell.

Prerequisites

1️⃣ Install PnP PowerShell

Ensure PnP PowerShell is installed on your system:

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

2️⃣ Connect to SharePoint Admin Center

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

Replace "yourtenant" with your actual SharePoint tenant name.

Use Global Administrator or SharePoint Administrator credentials.

Step 1: Retrieve OneDrive File Sharing Information

To extract file-sharing details, run the following command:

# Define User OneDrive URL
$UserPrincipalName = "user@yourtenant.com"
$OneDriveURL = Get-PnPUserProfileProperty -Account $UserPrincipalName | Select-Object PersonalUrl

# Connect to OneDrive
Connect-PnPOnline -Url $OneDriveURL -Interactive

# Get the Document Library
$LibraryItems = Get-PnPListItem -List "Documents" -Fields "FileRef", "FileLeafRef", "SharedWithUsers", "SharedWithDetails"

# Display file-sharing details
$LibraryItems | ForEach-Object {
    Write-Host "File Name: " $_["FileLeafRef"]
    Write-Host "File URL: " $_["FileRef"]
    Write-Host "Shared With: " $_["SharedWithUsers"]
    Write-Host "-----------------------------------------"
}

Retrieves file names, URLs, and shared users from the OneDrive Documents library.

Step 2: Export the Report to CSV

To save the report as a CSV file, use:

# Define report file path
$ReportPath = "C:\OneDrive_Sharing_Report.csv"

# Extract sharing details
$SharingData = $LibraryItems | Select-Object FileLeafRef, FileRef, SharedWithUsers, SharedWithDetails

# Export to CSV
$SharingData | Export-Csv -Path $ReportPath -NoTypeInformation -Encoding UTF8

Write-Host "OneDrive file-sharing report exported to $ReportPath"

Generates a CSV report containing file-sharing details.

Step 3: Generate a Report for All OneDrive Users

For multiple users, automate the process with a CSV-based bulk report:

# Import CSV file with a list of OneDrive users
$Users = Import-Csv "C:\OneDriveUsers.csv"

# Define output file
$ReportPath = "C:\OneDrive_File_Sharing_Report.csv"
$ReportData = @()

foreach ($User in $Users) {
    $UserPrincipalName = $User.UserPrincipalName

    # Get OneDrive URL
    $OneDriveURL = Get-PnPUserProfileProperty -Account $UserPrincipalName | Select-Object PersonalUrl

    # Connect to OneDrive
    Connect-PnPOnline -Url $OneDriveURL -Interactive

    # Get file-sharing data
    $LibraryItems = Get-PnPListItem -List "Documents" -Fields "FileRef", "FileLeafRef", "SharedWithUsers", "SharedWithDetails"

    # Process each file
    foreach ($Item in $LibraryItems) {
        $ReportData += [PSCustomObject]@{
            User            = $UserPrincipalName
            FileName        = $Item["FileLeafRef"]
            FileURL         = $Item["FileRef"]
            SharedWithUsers = $Item["SharedWithUsers"]
        }
    }
}

# Export report
$ReportData | Export-Csv -Path $ReportPath -NoTypeInformation -Encoding UTF8

Write-Host "Bulk OneDrive file-sharing report saved at $ReportPath"

CSV Format for User List (OneDriveUsers.csv):

UserPrincipalName
user1@yourtenant.com
user2@yourtenant.com

Extracts sharing reports for all OneDrive users in the organization.

Step 4: Automate the Report Generation

To schedule the script weekly or monthly, use Windows Task Scheduler:

1️⃣ Open Task Scheduler (taskschd.msc).
2️⃣ Click Create Basic Task → Name it OneDrive Sharing Report.
3️⃣ Set Trigger to Weekly or Monthly.
4️⃣ Choose Action → Start a Program.
5️⃣ Select PowerShell (C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe).
6️⃣ Add script path in Arguments:

-ExecutionPolicy Bypass -File "C:\Scripts\OneDriveSharingReport.ps1"

7️⃣ Click Finish.

Now the OneDrive sharing report will generate automatically!

Step 5: Send Report via Email

To email the report, use:

# Define email details
$SMTPServer = "smtp.office365.com"
$SMTPPort = 587
$From = "admin@yourtenant.com"
$To = "securityteam@yourtenant.com"
$Subject = "OneDrive File Sharing Report"
$Body = "Please find attached the latest OneDrive file-sharing report."
$Attachment = "C:\OneDrive_File_Sharing_Report.csv"
$Credential = Get-Credential

# Send email
Send-MailMessage -From $From -To $To -Subject $Subject -Body $Body -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl -Credential $Credential -Attachments $Attachment

Write-Host "Report sent to $To"

Emails the OneDrive file-sharing report to the security team.