Overcoming SharePoint Online’s Reporting Limitation: Tracking User File Uploads with PowerShell

When working with SharePoint Online, one of the challenges administrators face is the inability to generate detailed reports on file uploads by individual users over a specific timeframe, such as the last 60 days. Out-of-the-box, SharePoint Online lacks this functionality, even at the admin center level, and needs to provide a built-in reporting feature to track file uploads across document libraries by users.

Site collection Storage Metrics

M365 Compliance Admin center Audit Report

However, with the help of PnP PowerShell, we can bypass this limitation and generate a report that provides insight into the files uploaded by specific users. This script is highly customizable and capable of exporting the results into a CSV or Excel file for easier analysis.

The PowerShell Solution

The script below demonstrates how to collect all uploaded files from document libraries within a SharePoint site, gather file metadata (such as file size and MD5 hash for detecting duplicates), and export the information into a CSV file.

Prerequisites

Before running this script, ensure you have the following in place:

• PnP PowerShell Module: If not already installed, you can install it via PowerShell:

Install-Module -Name "PnP.PowerShell"

$SiteURL = "https://tenant.sharepoint.com/sites/sitename"
$LibraryName = "DocumentLibraryName"  # Specify the document library name here
$Pagesize = 2000
$ReportOutput = "C:\path-to-output-folder"
 
#Connect to SharePoint Online site
Connect-PnPOnline -Url $SiteURL -ClientId $ClientID -ClientSecret $ClientSecret
 
#Array to store results
$DataCollection = @()

#Get the specified Document Library
$DocumentLibrary = Get-PnPList | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -eq $LibraryName}

#Check if the library exists
If ($DocumentLibrary) {
    #Get All documents from the library
    $Documents = Get-PnPListItem -List $DocumentLibrary -PageSize $Pagesize -Fields ID, File_x0020_Type | Where {$_.FileSystemObjectType -eq "File"}
   
    #Iterate through each document
    Foreach($Document in $Documents) {
        #Get the File from Item
        $File = Get-PnPProperty -ClientObject $Document -Property File

        #Get The File Hash
        $Bytes = $File.OpenBinaryStream()
        Invoke-PnPQuery
        $MD5 = New-Object -TypeName System.Security.Cryptography.MD5CryptoServiceProvider
        $HashCode = [System.BitConverter]::ToString($MD5.ComputeHash($Bytes.Value))
  
        #Collect data        
        $Data = New-Object PSObject 
        $Data | Add-Member -MemberType NoteProperty -name "FileName" -value $File.Name
        $Data | Add-Member -MemberType NoteProperty -Name "HashCode" -value $HashCode
        $Data | Add-Member -MemberType NoteProperty -Name "URL" -value $File.ServerRelativeUrl
        $Data | Add-Member -MemberType NoteProperty -Name "FileSize" -value $File.Length        
        $DataCollection += $Data
    }

    #Export the results to CSV
    $DataCollection | Export-Csv "$ReportOutput\UploadedFilesReport.csv" -NoTypeInformation

    Write-Host "Report generated successfully!"
} else {
    Write-Host "Document Library not found!"
}

Script Breakdown

1. Connect to SharePoint Online: The script connects to the specified SharePoint Online site using Connect-PnPOnline. To authenticate, you’ll need a Client ID and Client Secret. I’ve explained how to generate these in my SharePoint Online REST API Authentication guide using Postman.

2. Document Libraries: The script fetches all non-hidden document libraries with more than zero items, filtering out irrelevant libraries like “Site Pages” or “Preservation Hold Library.”

3. File Metadata: For each file, it retrieves important details such as the File Name, File Size, and MD5 hash. The MD5 hash allows you to detect duplicate files based on their content.

4. Report Generation: All the collected data is stored in an array and later exported into a CSV file for further analysis.

Please note that the file sizes in the report will be displayed in bytes. You can convert them to KB, MB, GB, or TB as needed and then generate a visual report based on the Excel data.

Conclusion

Although SharePoint Online doesn’t provide an out-of-the-box solution for generating user-upload file reports, this PowerShell script offers a powerful alternative. By leveraging the PnP PowerShell library, administrators can create detailed reports on user activity and track file uploads efficiently. Feel free to reach out if you encounter any challenges or need further customization!