OneDrive Preservation Hold Library Query

[Brinners]

Hi Folks,

I am a total newbie when it comes to scripting, a colleague left my team and was handed over his workload (Sharepoint Online)

I have mainly been using Co-Pilot to help create/troubleshoot my scripts and when I get stuck there, my company has a support agreement with Microsoft.

Currently we have a pilot of about 200 odd users, but when spot checking my results, I am getting a big difference for some users (eg what the script captures VS what's in the Storage metrics section of a users OneDrive)

Below is the code ive been using, and as this is not a MS supported library, I was hoping someone here could me figure out why we are seeing such differences in the data please

EG - user 1: Report shows 71GB in PHL vs. 325GB when checking Storage Metrics (difference of ~250GB)
user 2: Report shows 330GB in PHL vs. 961GB when checking Storage Metrics (difference of ~630GB)

    I am yet to get reports of other users seeing differences, but I want to get my ducks in a row as this pilot will go business wide, so want to make sure nothing is getting missed etc

SCRIPT:

`# Config Variables
$TenantAdminURL = "https://ausxxx-admin.sharepoint.com"
$ReportOutput = "C:\Temp\OneDriveUsage.csv"
$EmailListPath = "C:\Temp\Pilot5.csv" # Path to the CSV file containing email addresses

Import email addresses from CSV

$EmailList = Import-Csv -Path $EmailListPath

$UsageData = @()

Connect to Admin Center using PnP Online

Connect-PnPOnline -Url $TenantAdminURL -Interactive # Use -UseWebLogin or -Interactive as needed

Loop through each email in the list

ForEach($Email in $EmailList) {
Try {
$UserEmail = $Email.Email # Assuming the CSV has a column named 'Email'

    # Format the username and domain parts
    $UserPart = $UserEmail.Split('@')[0].Replace('.', '_')
    $DomainPart = $UserEmail.Split('@')[1] -replace "\.", "_"

    # Construct the OneDrive URL
    $SiteURL = "https://ausxxx-my.sharepoint.com/personal/$UserPart" + "_" + $DomainPart

    Write-host "Processing Site:" $SiteURL -f Yellow

    # Connect to the OneDrive site
    Connect-PnPOnline -Url $SiteURL -Interactive  # Use -UseWebLogin or -Interactive as needed

    # Get the preservation library
    $PreservationLibrary = Get-PnPList -Identity PreservationHoldLibrary
    if ($null -eq $PreservationLibrary) {
        Write-host "PreservationHoldLibrary not found at $SiteURL" -ForegroundColor Red
        continue
    }

    # Retrieve items in batches to avoid the list view threshold
    $TotalSize = 0
    $BatchSize = 1000  # Adjust the batch size as needed
    $BatchIndex = 0

    do {
        $Items = Get-PnPListItem -List $PreservationLibrary -PageSize $BatchSize -Query "<View><Query><Where><Geq><FieldRef Name='ID'/><Value Type='Number'>$BatchIndex</Value></Geq></Where></Query><RowLimit>$BatchSize</RowLimit></View>"
        $BatchIndex += $BatchSize

        # Sum up the size of each file
        foreach ($Item in $Items) {
            $File = Get-PnPProperty -ClientObject $Item -Property File
            if ($null -ne $File) {
                Write-host "File: $($File.Name), Size: $($File.Length)" -ForegroundColor Green
                $TotalSize += $File.Length
            } else {
                Write-host "Item without file: $($Item.Title)" -ForegroundColor Yellow
            }
        }
    } while ($Items.Count -eq $BatchSize)

    $PreservationLibrarySize = $TotalSize / 1024 / 1024  # Convert to MB

    # Collect OneDrive usage data
    $UsageData += [PSCustomObject][ordered]@{
        Email                     = $UserEmail
        URL                       = $SiteURL
        UsedSpaceMB               = (Get-PnPWeb).StorageUsageCurrent
        PreservationLibrarySizeMB = [Math]::Round($PreservationLibrarySize, 2)
    }
}
Catch {
    Write-host "Error: $($_.Exception.Message)" -ForegroundColor Red
}

}

Export the data to CSV Report

$UsageData | Out-GridView
$UsageData | Export-Csv -Path $ReportOutput -NoTypeInformation
`

[Barbarur]

I guess you are missing the versions. The script only gets the size of the last version from each file instead of the full size.

[temporaldoom]

I find Sharepoint Diary has a lot of base scripts that you can tailor to your needs

https://www.sharepointdiary.com/2016/12/sharepoint-online-version-history-report-using-powershell.html

This one gets a report of all files and their actual storage size including version histories