Generating a Post-Migration SharePoint Audit Report using PnP PowerShell

After migrating data to SharePoint Online, it is critical to audit the migration and verify data integrity, permissions, and overall site performance. A post-migration audit report helps validate that all content, users, and configurations have been successfully migrated and meet compliance requirements.

Using PnP PowerShell, you can automate the generation of a post-migration audit report, ensuring that no critical data is lost and that security settings are correctly applied.

---

Key Aspects of a Post-Migration Audit

✔ Site and Library Verification – Ensure all sites, lists, and libraries exist after migration.
✔ File and Folder Count – Compare pre-migration vs. post-migration file counts.
✔ Permissions Review – Validate that user access levels remain unchanged.
✔ External Sharing Links – Check if any external sharing links are still active.
✔ Version History Validation – Ensure version history is preserved where required.

---

Step 1: Connect to SharePoint Online

To generate an audit report, first connect to your SharePoint Online tenant.

$siteUrl = "https://yourtenant.sharepoint.com/sites/MigratedSite"
Connect-PnPOnline -Url $siteUrl -Interactive

✔ Ensures secure authentication to access SharePoint data.

---

Step 2: Verify All Sites Have Migrated

Retrieve a list of migrated sites and compare with the original list.

$sites = Get-PnPTenantSite | Select Title, Url, StorageUsage, Template, LastContentModifiedDate

$sites | Export-Csv -Path "C:\SharePoint_Audit\Sites_Audit.csv" -NoTypeInformation
Write-Host " Migrated Sites Report Generated"

✔ Confirms that all planned sites exist in SharePoint Online.

---

Step 3: Compare File and Folder Counts

To ensure all files and folders migrated successfully, compare counts from source and destination.

$libraryName = "Documents"
$files = Get-PnPListItem -List $libraryName

Write-Host " Total files in '$libraryName': $($files.Count)"

$files | Select FieldValues | Export-Csv -Path "C:\SharePoint_Audit\FileCount_Audit.csv" -NoTypeInformation

✔ Ensures no data loss during migration.

---

Step 4: Verify User Permissions and Roles

Audit user permissions to confirm that access levels match pre-migration settings.

$permissions = Get-PnPList -Includes RoleAssignments | ForEach-Object {
    $_.RoleAssignments | ForEach-Object {
        $user = Get-PnPUser -Identity $_.Member.Title
        [PSCustomObject]@{
            ListName = $_.List.Title
            User = $user.LoginName
            Role = $_.RoleDefinitionBindings.Name
        }
    }
}

$permissions | Export-Csv -Path "C:\SharePoint_Audit\Permissions_Audit.csv" -NoTypeInformation
Write-Host " Permissions Audit Report Generated"

✔ Prevents unauthorized access issues post-migration.

---

Step 5: Identify External Sharing Links

Audit external sharing links to ensure secure data access.

$extLinks = Get-PnPListItem -List $libraryName | Where-Object { $_.HasUniqueRoleAssignments -eq $true }

$extLinks | Export-Csv -Path "C:\SharePoint_Audit\ExternalSharing_Audit.csv" -NoTypeInformation
Write-Host " External Sharing Report Generated"

✔ Prevents data leaks by monitoring external access.

---

Step 6: Validate Version History Preservation

Ensure document version history was retained during migration.

$versions = Get-PnPListItem -List $libraryName -Fields FileRef, FileLeafRef, ows_Modified_x0020_By, ows_Modified | `
    Select FileRef, FileLeafRef, ows_Modified_x0020_By, ows_Modified

$versions | Export-Csv -Path "C:\SharePoint_Audit\VersionHistory_Audit.csv" -NoTypeInformation
Write-Host " Version History Audit Report Generated"

✔ Confirms data consistency post-migration.

---

Step 7: Audit Storage Usage

Check how much storage each migrated site is consuming.

$storageReport = Get-PnPTenantSite | Select Title, Url, StorageUsageCurrent

$storageReport | Export-Csv -Path "C:\SharePoint_Audit\Storage_Audit.csv" -NoTypeInformation
Write-Host " Storage Audit Report Generated"

✔ Ensures efficient space utilization post-migration.

---

Step 8: Generate a Summary Report

Create a comprehensive audit summary in one report.

$auditSummary = @()
$auditSummary += [PSCustomObject]@{
    Report = "Migrated Sites"
    File = "Sites_Audit.csv"
}
$auditSummary += [PSCustomObject]@{
    Report = "File Count"
    File = "FileCount_Audit.csv"
}
$auditSummary += [PSCustomObject]@{
    Report = "Permissions"
    File = "Permissions_Audit.csv"
}
$auditSummary += [PSCustomObject]@{
    Report = "External Sharing"
    File = "ExternalSharing_Audit.csv"
}
$auditSummary += [PSCustomObject]@{
    Report = "Version History"
    File = "VersionHistory_Audit.csv"
}
$auditSummary += [PSCustomObject]@{
    Report = "Storage Usage"
    File = "Storage_Audit.csv"
}

$auditSummary | Export-Csv -Path "C:\SharePoint_Audit\PostMigration_AuditSummary.csv" -NoTypeInformation
Write-Host " Post-Migration Audit Summary Report Generated"

✔ Provides an overview of migration success.

---

Step 9: Automate the Audit Process (Scheduled Task)

To schedule the audit monthly, follow these steps:

1. Save the script as PostMigrationAudit.ps1.
2. Open Task Scheduler → Create Basic Task.
3. Set Trigger → Monthly.
4. Action → Start a program → powershell.exe -File C:\Scripts\PostMigrationAudit.ps1.
5. Ensure execution policy allows scripts:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

✔ Automates regular audit checks.