Generating a OneDrive Activity Report using PnP PowerShell

Monitoring OneDrive activity is crucial for security, compliance, and storage management in an organization. Using PnP PowerShell, you can generate a OneDrive Activity Report that provides details on user activity, storage usage, file modifications, and last access times.

This guide covers the step-by-step process to generate a OneDrive Activity Report using PnP PowerShell.

Step 1: Install and Update PnP PowerShell

Ensure PnP PowerShell is installed on your system. Open PowerShell as Administrator and run:

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

To update the module:

Update-Module -Name PnP.PowerShell

Verify the installation:

Get-Module -Name PnP.PowerShell -ListAvailable

Step 2: Connect to SharePoint Online

OneDrive for Business is managed through SharePoint Online, so we need to authenticate:

$adminUrl = "https://yourtenant-admin.sharepoint.com"
Connect-PnPOnline -Url $adminUrl -Scopes "Sites.FullControl.All" -Interactive

For App-based authentication, use:

$clientId = "your-client-id"
$tenantId = "your-tenant-id"
$clientSecret = "your-client-secret"

Connect-PnPOnline -Url $adminUrl -ClientId $clientId -ClientSecret $clientSecret -Tenant $tenantId

Once connected, proceed to retrieve OneDrive activity data.

Step 3: Retrieve OneDrive Sites

To list all OneDrive sites in the organization:

$oneDriveSites = Get-PnPTenantSite -IncludeOneDriveSites | Where-Object { $_.Url -like "*-my.sharepoint.com/personal/*" }

$oneDriveSites | Select-Object Url, Owner, StorageUsageCurrent, StorageQuota | Format-Table -AutoSize

This command lists all OneDrive sites with their URL, owner, and storage details.

Step 4: Generate OneDrive Activity Report

To get detailed activity data for OneDrive users, run:

$reportData = @()

foreach ($site in $oneDriveSites) {
    $activity = Get-PnPListItem -List "Documents" -Connection (Connect-PnPOnline -Url $site.Url -Interactive)
    
    foreach ($item in $activity) {
        $reportData += [PSCustomObject]@{
            OneDriveUrl      = $site.Url
            Owner           = $site.Owner
            FileName        = $item.FieldValues["FileLeafRef"]
            LastModified    = $item.FieldValues["Modified"]
            ModifiedBy      = $item.FieldValues["Editor"]
        }
    }
}

$reportData | Format-Table -AutoSize

This script extracts file names, last modified dates, and the user who modified them.

Step 5: Export the Report to CSV

To save the OneDrive activity report:

$reportPath = "C:\Reports\OneDrive_Activity_Report.csv"

$reportData | Export-Csv -Path $reportPath -NoTypeInformation

Write-Host "OneDrive Activity Report saved to $reportPath"

Step 6: Schedule Automatic Report Generation

To automate this process, schedule the script using Task Scheduler.

1. Open Task Scheduler

Click Start and search for Task Scheduler.
Click Create Basic Task.
Name it “OneDrive Activity Report”.

2. Set Trigger

Choose Daily or any required frequency.
Set the execution time.

3. Set Action

Select Start a Program.
In Program/Script, enter: powershell.exe
In Arguments, enter: -File "C:\Scripts\OneDriveActivityReport.ps1"
Click Finish.

This will automate the OneDrive activity report generation.

Step 7: Verify Report Data

To check the generated report:

Import-Csv -Path "C:\Reports\OneDrive_Activity_Report.csv" | Format-Table -AutoSize