This document describes how to run the SharePoint column inventory before deploying the eSHARE classification columns. The inventory is read-only: it lists every site, library and column in the tenant, and flags existing columns that look like classification columns, so you know what the deployment will find. The following steps will be reviewed:
PREREQUISITES:
The person performing these steps should be a SharePoint Administrator using the computer and certificate set up in Create eSHARE Classify SPO Columns Entra ID App Registration.
Save the Inventory Script
Go to Inventory Script at the end of this article, expand the script block and select the copy button in the top-right corner of the code.
In PowerShell 7, save what you copied as Get-SPOColumnInventory.ps1:
Get-Clipboard -Raw | Set-Content -Path C:\eSHARE-Classification\Get-SPOColumnInventory.ps1 -Encoding utf8Confirm the script saved correctly. This shows the script's description:
Get-Help C:\eSHARE-Classification\Get-SPOColumnInventory.ps1Run the Inventory
Open PowerShell 7 and run the inventory for SharePoint sites and OneDrive. To scan SharePoint sites only, for example when doing sites first, leave out
-IncludeOneDriveand run it again with it before the OneDrive rollout:
Set-Location C:\eSHARE-Classification
Import-Module PnP.PowerShell
.\Get-SPOColumnInventory.ps1 -ConfigFile .\Contoso\contoso.tenant.json -IncludeOneDriveWait for the run to finish. The same command works in Commercial, GCC and GCC High tenants; the script reads the cloud from the configuration file. As a guide, about 500 sites take around 10 minutes. The script slows down and retries automatically if SharePoint throttles it.
Open the report: C:\eSHARE-Classification\Contoso\SPO-Inventories\SPO-Inventory-contoso-[date]-[time]\SPO-Column-Inventory.xlsx.
Review the Inventory
The report has one sheet per result:
Sheet | What it shows |
|---|---|
Summary | Counts of sites, lists and columns, and the options used |
Look-alikes | Columns whose name contains sensitiv, classif, attribute, label, marking, eshare or guid, or a space (_x0020_). Microsoft's built-in columns are left out. |
Name conflicts | Display names used with more than one internal name, and the reverse |
Managed properties | The tenant's custom search properties and what they are mapped to |
All columns | Every column found, on every site and library |
Errors | Sites that couldn't be read, e.g. locked or read-only sites. The deployment skips these too. |
On the 'Look-alikes' sheet, look for columns with the internal names DataSensitivity, DataClassification, DataAttributes, eSHARE_GUID or DataSensitivityId that were created on a single library. The deployment replaces these with the standard site column and keeps their values.
Look for columns with a classification display name but a different internal name, e.g. a Data Sensitivity column stored as eShareLabel. eSHARE doesn't write to these, and the deployment doesn't add the standard column to a library that already has one. Note them and contact eSHARE support if you find any.
On the 'Managed properties' sheet, note any property mapped to eSHARE_GUID. This is needed later for the Purview DLP rule.
Check the 'Errors' sheet and decide whether any locked site needs the columns. If it does, unlock it before the deployment.
Note:
In a large tenant, most look-alikes are ordinary columns whose names contain a space. To narrow the list, run the inventory again with
-Pattern 'sensitiv|classif|eshare|guid'.
Inventory Script
Copy the script below with the copy button and save it as C:\eSHARE-Classification\Get-SPOColumnInventory.ps1. It is read-only and changes nothing in the tenant.
Expand this block and copy the Get-SPOColumnInventory.ps1 script below
<#
.SYNOPSIS
Tenant-wide inventory of SharePoint columns and search managed properties, with a look-alike report
for classification-style columns (e.g. "Data Sensitivity", "Data Classification", "Data Attributes", "eSHARE_GUID", "Data Sensitivity Id").
Works against any Microsoft 365 tenant, including US Government clouds.
.DESCRIPTION
1. Microsoft Graph (app-only, certificate): enumerates every site and subsite, every site column,
and every column on every visible list/library.
2. PnP PowerShell (app-only with the same certificate, or signed in as an admin as a fallback):
exports the tenant-level managed properties and their crawled-property mappings.
3. Joins the two: for each column, the crawled property and managed property it is mapped to, if any.
4. Flags look-alikes (name pattern match) and naming conflicts (one display name used with several
internal names, or the reverse).
Read-only. Nothing in the tenant is changed.
Crawled properties cannot be exported in SharePoint Online, so only crawled properties that are
mapped to a managed property are known. MappedCrawledProperty shows those actual names from the
tenant search schema; no crawled property names are guessed or derived.
.PARAMETER ConfigFile
A <name>.tenant.json file holding TenantName, TenantId, ClientId, CertificatePath or
CertificateThumbprint, and Cloud (see .NOTES and example.tenant.json.sample). With no -ConfigFile,
the script looks for *.tenant.json in its own folder and in the tenant subfolders one level down
(e.g. Contoso\): one is used automatically, several give a menu. The folder holding the
chosen file is the TENANT FOLDER: the output defaults to it and a relative CertificatePath is resolved
against it. Values passed on the command line override the file.
.PARAMETER TenantName
The tenant's SharePoint name: the part before .sharepoint.com (e.g. "contoso" for
contoso.sharepoint.com). Used to build the admin URL and the default tenant ID.
.PARAMETER ClientId
Application (client) ID of the inventory app registration in Entra ID. Usually set in the
tenant config file.
.PARAMETER CertificateThumbprint
Windows only. Thumbprint of the app's certificate in the current user's certificate store
(Cert:\CurrentUser\My). Only the thumbprint is stored; the private key stays in the store.
Ignored when CertificatePath is set.
.PARAMETER CertificatePath
Path to the app's certificate as a .pfx file (private key included). Works on Windows, macOS and
Linux, and is required on macOS and Linux. "~/" means your home folder; other relative paths are
resolved against the tenant folder. Keep the .pfx OUTSIDE the synced SharePoint folder (it holds
the app's private key).
.PARAMETER CertificatePassword
Password of the .pfx file, as a SecureString. If omitted, the script prompts for it (press Enter
for a .pfx without a password). Never stored in the config file.
.PARAMETER Cloud
Commercial (default), GCC, GCCHigh or DoD. Sets the Graph endpoint, SharePoint domain and sign-in
environment. GCC uses the commercial endpoints. GCCHigh and DoD have not been tested.
.PARAMETER TenantId
Optional. Tenant GUID or initial domain. Defaults to <TenantName>.onmicrosoft.com
(.onmicrosoft.us for GCCHigh/DoD). Pass it explicitly if the initial domain differs.
.PARAMETER AdminUrl
Optional. Overrides the admin URL built from TenantName (and can be used instead of TenantName).
.PARAMETER OutputFolder
Parent folder for the run output. Each run creates SPO-Inventory-<tenant>-<yyyyMMdd-HHmm> inside it.
Default: SPO-Inventories in the tenant folder (next to the script when no config file is used).
.PARAMETER Pattern
Case-insensitive regular expression matched against internal and display names to fill the
Look-alikes sheet. Default: 'sensitiv|classif|attribute|label|marking|eshare|guid|_x0020_'.
Example: -Pattern 'confidential|retention' to look for other column families.
.PARAMETER IncludeBuiltIn
Keep Microsoft built-in columns (internal names starting with "_", and a few others such as WSGUID
and VideoRenditionLabel) on the Look-alikes sheet. They are always on the All columns sheet.
.PARAMETER IncludeOneDrive
Also scan OneDrive (personal, -my) sites. Off by default; it can add a lot of sites and time.
.PARAMETER IncludeHiddenColumns
Include hidden system columns. Off by default.
.PARAMETER IncludeHiddenLists
Include hidden system lists and libraries. Off by default.
.PARAMETER SkipManagedProperties
Skip the PnP step that exports managed properties and their crawled-property mappings. The Managed
properties sheet is then empty and the Mapped* columns are blank.
.PARAMETER InteractiveManagedProperties
Export the managed properties by signing in as an admin instead of app-only. By default the script
first tries app-only with the same certificate (needs SharePoint Sites.FullControl.All, application)
and only falls back to an interactive sign-in if that fails. The interactive sign-in needs an ACTIVE
SharePoint Administrator role in the tenant and the delegated SharePoint AllSites.FullControl permission.
.OUTPUTS
<OutputFolder>\SPO-Inventory-<tenant>-<yyyyMMdd-HHmm>\SPO-Column-Inventory.xlsx, with one worksheet per result:
Summary run details and counts
Look-alikes columns whose internal or display name matches -Pattern, excluding Microsoft
built-in columns (including the "Sensitivity" label column) unless -IncludeBuiltIn
Name conflicts display names with more than one internal name, and the reverse
Managed properties tenant managed properties and their mapped crawled properties
All columns every column found (site columns and list/library columns)
Errors sites or lists that could not be read (only if any)
Uses the ImportExcel module (installed automatically for the current user if missing); Excel itself
is not required.
.EXAMPLE
.\Get-SPOColumnInventory.ps1
Uses the single *.tenant.json file in the script folder or its tenant subfolders (or offers a menu).
.EXAMPLE
.\Get-SPOColumnInventory.ps1 -ConfigFile .\Contoso\contoso.tenant.json
.EXAMPLE
.\Get-SPOColumnInventory.ps1 -TenantName contoso -ClientId <app-id> -CertificateThumbprint <thumbprint>
.EXAMPLE
.\Get-SPOColumnInventory.ps1 -TenantName contoso -Cloud GCCHigh -ClientId <app-id> -CertificateThumbprint <thumbprint>
.NOTES
Requires PowerShell 7.4+, PnP.PowerShell, Microsoft.Graph.Authentication, ImportExcel.
Runs on Windows, macOS and Linux; on macOS and Linux use CertificatePath (.pfx), because
thumbprints need the Windows certificate store.
Each tenant needs its own app registration: Graph Sites.Read.All (application), SharePoint
Sites.FullControl.All (application) for the app-only search-schema export, and optionally SharePoint
AllSites.FullControl (delegated) for the interactive fallback; certificate uploaded, admin consent
granted. See the guide section
"Tenant-wide inventory of columns, crawled properties and managed properties".
Tenant config file (<name>.tenant.json, in the tenant's subfolder). Command-line values override it:
{
"TenantName": "contoso",
"TenantId": "00000000-0000-0000-0000-000000000000",
"ClientId": "00000000-0000-0000-0000-000000000000",
"CertificatePath": "~/SPO-Certs/contoso-inventory.pfx",
"CertificateThumbprint": "",
"Cloud": "Commercial"
}
Use CertificatePath (any OS) or CertificateThumbprint (Windows). Cloud and TenantId are optional;
AdminUrl can also be set. The file holds no secrets: the .pfx password is prompted for at run time,
and with a thumbprint the private key stays in the Windows certificate store.
#>
[CmdletBinding()]
param(
[string]$ConfigFile, # optional; a <name>.tenant.json file (see .NOTES)
[string]$TenantName, # e.g. contoso (from contoso.sharepoint.com)
[string]$ClientId,
[string]$CertificateThumbprint, # Windows only
[string]$CertificatePath, # .pfx file; any OS
[SecureString]$CertificatePassword,
[ValidateSet('Commercial', 'GCC', 'GCCHigh', 'DoD')]
[string]$Cloud = 'Commercial',
[string]$TenantId, # optional; defaults to <TenantName>.onmicrosoft.com/.us
[string]$AdminUrl, # optional; overrides the URL built from TenantName
[string]$OutputFolder, # parent folder; default <tenant folder>\SPO-Inventories
[string]$Pattern = 'sensitiv|classif|attribute|label|marking|eshare|guid|_x0020_', # regex, case-insensitive
[switch]$IncludeBuiltIn, # keep Microsoft built-in columns on the Look-alikes sheet
[switch]$IncludeOneDrive, # include personal (-my) sites
[switch]$IncludeHiddenColumns, # include hidden system columns
[switch]$IncludeHiddenLists, # include hidden system lists
[switch]$SkipManagedProperties, # skip the PnP step
[switch]$InteractiveManagedProperties # skip the app-only attempt; sign in as an admin instead
)
$ErrorActionPreference = 'Stop'
# ---------- 0a. Tenant config file ----------
# If the connection values aren't all on the command line, read them from a <name>.tenant.json file.
# Folder layout: scripts at the root, one subfolder per tenant holding its config, certificate and output.
# With no -ConfigFile: *.tenant.json in the script folder or one level down; one is used, several give a menu.
$haveCliValues = $ClientId -and ($CertificateThumbprint -or $CertificatePath) -and ($TenantName -or $AdminUrl)
if (-not $ConfigFile -and -not $haveCliValues) {
$found = @(Get-ChildItem -Path $PSScriptRoot -Filter '*.tenant.json' -File -Recurse -Depth 1 -ErrorAction SilentlyContinue | Sort-Object FullName)
if ($found.Count -eq 1) {
$ConfigFile = $found[0].FullName
} elseif ($found.Count -gt 1) {
Write-Host "Tenant config files found:" -ForegroundColor Cyan
for ($k = 0; $k -lt $found.Count; $k++) { Write-Host (" [{0}] {1}" -f ($k + 1), [System.IO.Path]::GetRelativePath($PSScriptRoot, $found[$k].FullName)) }
$pick = Read-Host "Select a tenant (1-$($found.Count))"
if ($pick -notmatch '^\d+$' -or [int]$pick -lt 1 -or [int]$pick -gt $found.Count) { throw "Invalid selection '$pick'." }
$ConfigFile = $found[[int]$pick - 1].FullName
}
}
$TenantFolder = $PSScriptRoot
if ($ConfigFile) {
$hit = @($ConfigFile, (Join-Path $PSScriptRoot $ConfigFile)) | Where-Object { Test-Path -LiteralPath $_ -PathType Leaf } | Select-Object -First 1
if (-not $hit) { throw "Config file not found: $ConfigFile" }
$ConfigFile = (Resolve-Path -LiteralPath $hit).Path
$TenantFolder = Split-Path -Parent $ConfigFile
Write-Host "Using tenant config: $ConfigFile" -ForegroundColor Cyan
Write-Host "Tenant folder: $TenantFolder" -ForegroundColor Gray
$cfg = Get-Content -LiteralPath $ConfigFile -Raw | ConvertFrom-Json
# Values given on the command line win over the file.
foreach ($key in 'TenantName', 'TenantId', 'ClientId', 'CertificateThumbprint', 'CertificatePath', 'Cloud', 'AdminUrl') {
if (-not $PSBoundParameters.ContainsKey($key) -and $cfg.PSObject.Properties[$key] -and $cfg.$key) {
Set-Variable -Name $key -Value ([string]$cfg.$key)
}
}
}
if (-not $ClientId) { throw "ClientId is missing. Pass -ClientId or add it to a <name>.tenant.json file." }
if (-not $OutputFolder) { $OutputFolder = Join-Path $TenantFolder 'SPO-Inventories' }
# Certificate: a .pfx file (any OS) or a thumbprint in the Windows certificate store (Windows only).
$graphCert = $null
if ($CertificatePath) {
if ($CertificatePath -match '^~[\\/]') { $CertificatePath = Join-Path $HOME $CertificatePath.Substring(2) }
if (-not [System.IO.Path]::IsPathRooted($CertificatePath)) { $CertificatePath = Join-Path $TenantFolder $CertificatePath }
if (-not (Test-Path -LiteralPath $CertificatePath -PathType Leaf)) { throw "Certificate file not found: $CertificatePath" }
if (-not $CertificatePassword) {
$CertificatePassword = Read-Host "Password for certificate $(Split-Path $CertificatePath -Leaf) (press Enter if none)" -AsSecureString
}
$graphCert = [System.Security.Cryptography.X509Certificates.X509Certificate2]::new($CertificatePath, $CertificatePassword)
} elseif ($CertificateThumbprint) {
if (-not $IsWindows) { throw "CertificateThumbprint works only on Windows (certificate store). On macOS or Linux, set CertificatePath to a .pfx file." }
} else {
throw "No certificate. Set CertificatePath (.pfx, any OS) or CertificateThumbprint (Windows) in the tenant config, or pass it on the command line."
}
# ---------- 0b. Tenant and cloud settings ----------
$cloudSettings = @{
Commercial = @{ Graph = 'https://graph.microsoft.com'; MgEnv = 'Global'; PnPEnv = 'Production'; SpoDomain = 'sharepoint.com'; InitialDomain = 'onmicrosoft.com' }
GCC = @{ Graph = 'https://graph.microsoft.com'; MgEnv = 'Global'; PnPEnv = 'Production'; SpoDomain = 'sharepoint.com'; InitialDomain = 'onmicrosoft.com' }
GCCHigh = @{ Graph = 'https://graph.microsoft.us'; MgEnv = 'USGov'; PnPEnv = 'USGovernmentHigh'; SpoDomain = 'sharepoint.us'; InitialDomain = 'onmicrosoft.us' }
DoD = @{ Graph = 'https://dod-graph.microsoft.us'; MgEnv = 'USGovDoD'; PnPEnv = 'USGovernmentDoD'; SpoDomain = 'sharepoint-mil.us'; InitialDomain = 'onmicrosoft.us' }
}[$Cloud]
if (-not $TenantName -and $AdminUrl) {
$TenantName = ([uri]$AdminUrl).Host.Split('.')[0] -replace '-admin$', ''
}
if (-not $TenantName) { throw "Provide -TenantName (e.g. 'contoso' for contoso.sharepoint.com) or -AdminUrl." }
if (-not $AdminUrl) { $AdminUrl = "https://$TenantName-admin.$($cloudSettings.SpoDomain)" }
if (-not $TenantId) { $TenantId = "$TenantName.$($cloudSettings.InitialDomain)" }
$TenantUrl = "https://$TenantName.$($cloudSettings.SpoDomain)"
$graphV1 = "$($cloudSettings.Graph)/v1.0"
Write-Host "Tenant: $TenantUrl (cloud: $Cloud, tenant ID: $TenantId)" -ForegroundColor Cyan
$OutputFolder = Join-Path (Resolve-Path -LiteralPath (New-Item -ItemType Directory -Path $OutputFolder -Force).FullName) `
("SPO-Inventory-$TenantName-" + (Get-Date -Format 'yyyyMMdd-HHmm'))
New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null
$errors = [System.Collections.Generic.List[object]]::new()
# ---------- Graph helpers ----------
function Invoke-Graph([string]$Uri) {
for ($attempt = 1; $attempt -le 6; $attempt++) {
try {
return Invoke-MgGraphRequest -Method GET -Uri $Uri -OutputType PSObject
} catch {
$status = $_.Exception.Response.StatusCode.value__
if ($status -in 429, 503, 504 -and $attempt -lt 6) {
$wait = [math]::Min(60, [math]::Pow(2, $attempt))
Write-Warning "Throttled ($status). Waiting $wait s..."
Start-Sleep -Seconds $wait
} else { throw }
}
}
}
function Get-GraphAll([string]$Uri) {
$items = [System.Collections.Generic.List[object]]::new()
while ($Uri) {
$page = Invoke-Graph $Uri
if ($page.value) { $items.AddRange([object[]]$page.value) }
$Uri = $page.'@odata.nextLink'
}
return $items
}
$columnTypes = 'text','choice','number','dateTime','boolean','lookup','personOrGroup','term',
'calculated','currency','hyperlinkOrPicture','geolocation','thumbnail','contentApprovalStatus'
function Get-ColumnType($col) {
foreach ($t in $columnTypes) { if ($col.PSObject.Properties[$t] -and $null -ne $col.$t) { return $t } }
return 'other'
}
function New-ColumnRow($site, $listName, $listUrl, $listTemplate, $scope, $col, $siteColumnIds) {
[pscustomobject]@{
SiteUrl = $site.webUrl
SiteName = $site.displayName
Scope = $scope # SiteColumn | ListColumn
List = $listName
ListTemplate = $listTemplate
ListUrl = $listUrl
DisplayName = $col.displayName
InternalName = $col.name
ColumnId = $col.id
Type = Get-ColumnType $col
Group = $col.columnGroup
IsFromSiteColumn = if ($scope -eq 'ListColumn') { $siteColumnIds.Contains($col.id) } else { $null }
Hidden = $col.hidden
ReadOnly = $col.readOnly
MappedCrawledProperty = $null # crawled property actually used by a managed-property mapping
MappedManagedProperties = $null # filled in after the PnP step
}
}
# ---------- 1. Graph: sites, site columns, list columns ----------
Write-Host "Connecting to Microsoft Graph (app-only)..." -ForegroundColor Cyan
if ($graphCert) {
Connect-MgGraph -ClientId $ClientId -TenantId $TenantId -Certificate $graphCert -Environment $cloudSettings.MgEnv -NoWelcome
} else {
Connect-MgGraph -ClientId $ClientId -TenantId $TenantId -CertificateThumbprint $CertificateThumbprint -Environment $cloudSettings.MgEnv -NoWelcome
}
Write-Host "Enumerating sites..." -ForegroundColor Cyan
$rootSites = Get-GraphAll "$graphV1/sites/getAllSites?`$select=id,webUrl,displayName&`$top=999"
if (-not $IncludeOneDrive) { $rootSites = $rootSites | Where-Object { $_.webUrl -notmatch '-my\.sharepoint' } }
Write-Host " $($rootSites.Count) site collections found." -ForegroundColor Gray
$queue = [System.Collections.Generic.Queue[object]]::new()
$visited = [System.Collections.Generic.HashSet[string]]::new()
$rootSites | ForEach-Object { $queue.Enqueue($_) }
$rows = [System.Collections.Generic.List[object]]::new()
$n = 0
while ($queue.Count -gt 0) {
$site = $queue.Dequeue()
if (-not $visited.Add($site.id)) { continue }
$n++
Write-Host ("[{0}] {1}" -f $n, $site.webUrl)
$base = "$graphV1/sites/$($site.id)"
try {
# subsites
Get-GraphAll "$base/sites?`$select=id,webUrl,displayName" | ForEach-Object { $queue.Enqueue($_) }
# site columns
$siteCols = Get-GraphAll "$base/columns"
$siteColumnIds = [System.Collections.Generic.HashSet[string]]::new()
foreach ($c in $siteCols) {
[void]$siteColumnIds.Add($c.id)
if ($IncludeHiddenColumns -or -not $c.hidden) {
$rows.Add((New-ColumnRow $site $null $null $null 'SiteColumn' $c $siteColumnIds))
}
}
# lists and libraries
$lists = Get-GraphAll "$base/lists?`$select=id,displayName,webUrl,list"
foreach ($l in $lists) {
if (-not $IncludeHiddenLists -and $l.list.hidden) { continue }
try {
foreach ($c in (Get-GraphAll "$base/lists/$($l.id)/columns")) {
if ($IncludeHiddenColumns -or -not $c.hidden) {
$rows.Add((New-ColumnRow $site $l.displayName $l.webUrl $l.list.template 'ListColumn' $c $siteColumnIds))
}
}
} catch {
$errors.Add([pscustomobject]@{ SiteUrl = $site.webUrl; List = $l.displayName; Error = $_.Exception.Message })
}
}
} catch {
$errors.Add([pscustomobject]@{ SiteUrl = $site.webUrl; List = $null; Error = $_.Exception.Message })
}
}
Write-Host " $n sites scanned, $($rows.Count) columns collected." -ForegroundColor Gray
# ---------- 2. PnP: tenant managed properties ----------
$mpRows = @()
$crawledToMp = @{}
if (-not $SkipManagedProperties) {
# A failure here must not lose the column inventory already collected: record it and carry on.
# 1st choice: app-only with the same certificate (needs SharePoint Sites.FullControl.All, application).
# Fallback: interactive sign-in (needs an ACTIVE SharePoint Administrator and delegated AllSites.FullControl).
$mps = $null
$mpErrors = @()
if (-not $InteractiveManagedProperties) {
Write-Host "Reading the tenant search schema with PnP (app-only, certificate)..." -ForegroundColor Cyan
try {
$pnpCert = if ($CertificatePath) { @{ CertificatePath = $CertificatePath; CertificatePassword = $CertificatePassword } } else { @{ Thumbprint = $CertificateThumbprint } }
Connect-PnPOnline -Url $AdminUrl -ClientId $ClientId -Tenant $TenantId -AzureEnvironment $cloudSettings.PnPEnv @pnpCert
$mps = @(Get-PnPSearchConfiguration -Scope Subscription -OutputFormat ManagedPropertyMappings)
} catch {
$mpErrors += "App-only: $($_.Exception.Message)"
Write-Warning "App-only search-schema export failed ($($_.Exception.Message)). The app likely lacks SharePoint Sites.FullControl.All (application). Falling back to an interactive sign-in."
}
}
if ($null -eq $mps) {
Write-Host "Connecting to SharePoint admin with PnP (sign in as an active SharePoint Administrator)..." -ForegroundColor Cyan
try {
Connect-PnPOnline -Url $AdminUrl -ClientId $ClientId -Tenant $TenantId -AzureEnvironment $cloudSettings.PnPEnv -Interactive
$mps = @(Get-PnPSearchConfiguration -Scope Subscription -OutputFormat ManagedPropertyMappings)
} catch {
$mpErrors += "Interactive: $($_.Exception.Message)"
Write-Warning "Interactive search-schema export failed: $($_.Exception.Message)"
Write-Warning "Usual causes: the signed-in account is not an ACTIVE SharePoint Administrator in this tenant (activate it in PIM), or the app lacks the delegated SharePoint AllSites.FullControl permission."
}
}
if ($null -eq $mps) {
$mps = @()
Write-Warning "Managed properties were not exported. The column inventory is still written."
$errors.Add([pscustomobject]@{ SiteUrl = $AdminUrl; List = 'Managed properties (Get-PnPSearchConfiguration)'; Error = ($mpErrors -join ' | ') })
}
$mpRows = foreach ($mp in $mps) {
foreach ($cp in @($mp.Mappings)) {
if ($cp) {
if (-not $crawledToMp.ContainsKey($cp)) { $crawledToMp[$cp] = [System.Collections.Generic.List[string]]::new() }
$crawledToMp[$cp].Add($mp.Name)
}
}
[pscustomobject]@{
ManagedProperty = $mp.Name
Type = $mp.Type
Aliases = (@($mp.Aliases) -join '; ')
CrawledProperties = (@($mp.Mappings) -join '; ')
}
}
Write-Host " $(@($mpRows).Count) tenant managed properties found." -ForegroundColor Gray
}
# ---------- 3. Join ----------
# A managed property can be mapped to the column's ows_ crawled property, or to the raw crawled property
# created from a Word custom document property with the same name as the column (e.g. eSHARE_GUID).
# Record which one actually matched, using the name as it appears in the search schema.
$crawledNames = @{}
foreach ($k in $crawledToMp.Keys) { $crawledNames[$k] = $k }
foreach ($r in $rows) {
$hits = @(); $matched = @()
foreach ($cp in @("ows_$($r.InternalName)", $r.InternalName)) { # look up only; the name shown comes from the schema
if ($crawledToMp.ContainsKey($cp)) {
$hits += $crawledToMp[$cp]
$matched += $crawledNames[$cp]
}
}
$r.MappedCrawledProperty = ($matched | Select-Object -Unique) -join '; '
$r.MappedManagedProperties = ($hits | Select-Object -Unique) -join '; '
}
# ---------- 4. Look-alikes and conflicts ----------
# Microsoft built-in columns are left out of the look-alike report (they stay in columns.csv):
# internal names starting with "_" (including the "Sensitivity" label column, _DisplayName) plus a
# few known system columns.
$keepBuiltIn = @()
$builtInNames = @('TagEventDate', 'Created_x0020_Date', 'VideoRenditionLabel', 'WSGUID', 'WSEventSourceGUID')
function Test-BuiltIn($r) {
if ($r.InternalName -in $keepBuiltIn) { return $false }
return ($r.InternalName -like '_*') -or ($r.InternalName -in $builtInNames)
}
$lookalikes = $rows | Where-Object {
($_.InternalName -match $Pattern -or $_.DisplayName -match $Pattern) -and
($IncludeBuiltIn -or -not (Test-BuiltIn $_))
}
$conflicts = @()
$conflicts += $rows | Group-Object DisplayName | Where-Object { ($_.Group.InternalName | Select-Object -Unique).Count -gt 1 } |
ForEach-Object { [pscustomobject]@{ Kind = 'SameDisplayName_DifferentInternalNames'; Name = $_.Name
Variants = (($_.Group.InternalName | Select-Object -Unique) -join '; '); Occurrences = $_.Count } }
$conflicts += $rows | Group-Object InternalName | Where-Object { ($_.Group.DisplayName | Select-Object -Unique).Count -gt 1 } |
ForEach-Object { [pscustomobject]@{ Kind = 'SameInternalName_DifferentDisplayNames'; Name = $_.Name
Variants = (($_.Group.DisplayName | Select-Object -Unique) -join '; '); Occurrences = $_.Count } }
# ---------- 5. Output: one Excel workbook ----------
if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
Write-Host "Installing the ImportExcel module (current user)..." -ForegroundColor Cyan
Install-Module ImportExcel -Scope CurrentUser -Force
}
Import-Module ImportExcel
$xlsx = Join-Path $OutputFolder 'SPO-Column-Inventory.xlsx'
if (Test-Path $xlsx) { Remove-Item $xlsx }
function Add-Sheet($Items, [string]$Sheet, [string]$Table, [string[]]$Props) {
$data = @($Items)
if ($data.Count -eq 0) { $data = @([pscustomobject]@{ Result = 'None found' }) }
elseif ($Props) { $data = $data | Select-Object $Props }
$data | Export-Excel -Path $xlsx -WorksheetName $Sheet -TableName $Table -TableStyle Medium2 `
-AutoSize -MaxAutoSizeRows 500 -FreezeTopRow
}
$colProps = 'SiteName','SiteUrl','Scope','List','ListTemplate','DisplayName','InternalName','ColumnId','Type','Group',
'IsFromSiteColumn','Hidden','ReadOnly','MappedCrawledProperty','MappedManagedProperties','ListUrl'
$summary = @(
[pscustomobject]@{ Item = 'Tenant'; Value = $TenantUrl }
[pscustomobject]@{ Item = 'Cloud'; Value = $Cloud }
[pscustomobject]@{ Item = 'Run'; Value = (Get-Date -Format 'yyyy-MM-dd HH:mm') }
[pscustomobject]@{ Item = 'Sites scanned'; Value = $n }
[pscustomobject]@{ Item = 'Columns'; Value = $rows.Count }
[pscustomobject]@{ Item = 'Look-alikes'; Value = @($lookalikes).Count }
[pscustomobject]@{ Item = 'Name conflicts'; Value = @($conflicts).Count }
[pscustomobject]@{ Item = 'Tenant managed properties'; Value = @($mpRows).Count }
[pscustomobject]@{ Item = 'Errors'; Value = $errors.Count }
[pscustomobject]@{ Item = 'Look-alike pattern'; Value = $Pattern }
[pscustomobject]@{ Item = 'Built-in columns in look-alikes'; Value = $(if ($IncludeBuiltIn) { 'Included (-IncludeBuiltIn)' } else { 'Excluded' }) }
[pscustomobject]@{ Item = 'OneDrive sites'; Value = $(if ($IncludeOneDrive) { 'Included' } else { 'Excluded' }) }
[pscustomobject]@{ Item = 'Note'; Value = 'MappedCrawledProperty and MappedManagedProperties come from the tenant search schema (managed-property mappings). Crawled properties that are not mapped cannot be exported, so they are not listed; check the Search Schema page for those.' }
)
Add-Sheet $summary 'Summary' 'Summary'
Add-Sheet $lookalikes 'Look-alikes' 'Lookalikes' $colProps
Add-Sheet $conflicts 'Name conflicts' 'NameConflicts'
Add-Sheet $mpRows 'Managed properties' 'ManagedProperties'
Add-Sheet $rows 'All columns' 'AllColumns' $colProps
if ($errors.Count) { Add-Sheet $errors 'Errors' 'Errors' }
Write-Host ""
Write-Host "Done. Output in $OutputFolder" -ForegroundColor Green
Write-Host (" Columns: {0} Look-alikes: {1} Name conflicts: {2} Managed properties: {3} Errors: {4}" -f `
$rows.Count, @($lookalikes).Count, @($conflicts).Count, @($mpRows).Count, $errors.Count)
Write-Host ""
Write-Host "Look-alikes:" -ForegroundColor Cyan
$lookalikes | Select-Object SiteName, List, DisplayName, InternalName, Type, MappedCrawledProperty, MappedManagedProperties | Format-Table -AutoSize