Generating a Pre-Migration Report for SharePoint Online using PnP PowerShell

Before migrating content to SharePoint Online, it is crucial to analyze existing data to identify:
✔ Large files that may exceed SharePoint limits
✔ Unsupported file types
✔ Orphaned sites or users
✔ Storage usage
✔ Permissions structure

This PnP PowerShell script will generate a Pre-Migration Report to ensure a smooth migration process.

Step 1: Install and Connect to SharePoint Online

Ensure PnP PowerShell is installed:

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

Update the module if needed:

Update-Module -Name PnP.PowerShell

Now, connect to SharePoint Online:

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

✔ Uses interactive login for authentication.

Step 2: Define the Report Output File

Set the location to save the report:

$reportPath = "C:\PreMigrationReport.csv"

Step 3: Analyze SharePoint Online Sites

1. Retrieve All SharePoint Sites

$sites = Get-PnPTenantSite -Detailed

✔ Fetches all SharePoint sites in the tenant.

Step 4: Identify Large Files & Unsupported File Types

1. Define Unsupported Extensions

$unsupportedExtensions = @(".exe", ".bat", ".msi", ".dll", ".cmd")

2. Scan Document Libraries for Large Files

$largeFiles = @()
$maxSizeMB = 100  # Set size threshold (e.g., 100 MB)

foreach ($site in $sites) {
    Connect-PnPOnline -Url $site.Url -Interactive
    $libraries = Get-PnPList | Where-Object { $_.BaseType -eq "DocumentLibrary" }

    foreach ($lib in $libraries) {
        $files = Get-PnPListItem -List $lib.Title -PageSize 500 | ForEach-Object {
            $fileSizeMB = $_.FieldValues["File_x0020_Size"] / 1MB
            $fileName = $_.FieldValues["FileLeafRef"]
            $fileUrl = $_.FieldValues["FileRef"]

            if ($fileSizeMB -gt $maxSizeMB -or ($unsupportedExtensions -contains [System.IO.Path]::GetExtension($fileName))) {
                $largeFiles += [PSCustomObject]@{
                    SiteURL = $site.Url
                    Library = $lib.Title
                    FileName = $fileName
                    FileSizeMB = [math]::Round($fileSizeMB, 2)
                    FileURL = $fileUrl
                }
            }
        }
    }
}

✔ Identifies large files and unsupported formats.

Step 5: Check Orphaned Users

$orphanedUsers = @()

foreach ($site in $sites) {
    Connect-PnPOnline -Url $site.Url -Interactive
    $users = Get-PnPSiteUser

    foreach ($user in $users) {
        if ($user.IsHiddenInUI -and $user.LoginName -like "*#EXT#*") {
            $orphanedUsers += [PSCustomObject]@{
                SiteURL = $site.Url
                UserEmail = $user.Email
                LoginName = $user.LoginName
            }
        }
    }
}

✔ Finds orphaned external users.

Step 6: Identify Sites with Excessive Storage Usage

$storageThresholdGB = 50  # Set limit for large sites (e.g., 50 GB)
$largeSites = $sites | Where-Object { $_.StorageUsageCurrent -gt ($storageThresholdGB * 1024) } | Select-Object Title, Url, StorageUsageCurrent

✔ Lists sites consuming excessive storage.

Step 7: Export Pre-Migration Report

$report = @()

foreach ($site in $sites) {
    $report += [PSCustomObject]@{
        SiteTitle       = $site.Title
        SiteURL         = $site.Url
        StorageUsageMB  = $site.StorageUsageCurrent
        LastContentMod  = $site.LastContentModifiedDate
        LargeFiles      = ($largeFiles | Where-Object { $_.SiteURL -eq $site.Url }).Count
        OrphanedUsers   = ($orphanedUsers | Where-Object { $_.SiteURL -eq $site.Url }).Count
    }
}

$report | Export-Csv -Path $reportPath -NoTypeInformation -Encoding UTF8
Write-Host "Pre-Migration Report Generated at $reportPath"

✔ Saves the full migration report in a CSV file.

Step 8: Review the Report

Open the CSV file:

Invoke-Item $reportPath

✔ Allows easy review of migration readiness.