Private/Export-SPMExcel.ps1
|
function Get-SPMSiteRiskFacts { <# .SYNOPSIS Risk facts of one site: anonymous/org-wide sharing links, Everyone assignments and external users (guests found anywhere in the resolved member trees). #> [CmdletBinding()] param([Parameter(Mandatory)]$Site) $anon = [System.Collections.Generic.List[string]]::new() $org = [System.Collections.Generic.List[string]]::new() $everyone = [System.Collections.Generic.List[string]]::new() $external = @{} $extMark = '#ext#|urn:spo:guest' foreach ($p in @($Site.principals)) { if ($p.type -eq 'SharingLink') { if ([string]$p.name -match '\.Anonymous(View|Edit)\.') { $anon.Add('Anonymous ' + $Matches[1].ToLowerInvariant()) } elseif ([string]$p.name -match '\.Organization(View|Edit)\.') { $org.Add('Organization ' + $Matches[1].ToLowerInvariant()) } } elseif ($p.type -in 'Everyone', 'EveryoneExceptExternal') { foreach ($n in @($Site.nodes)) { if (@($n.assignments | Where-Object { [string]$_.principalId -eq [string]$p.id }).Count) { $everyone.Add([string]$p.name); break } } } if ($p.type -eq 'User') { if ((([string]$p.loginName) + ' ' + ([string]$p.upn)) -match $extMark) { $external[([string]($p.upn ?? $p.name)).ToLowerInvariant()] = @{ Name = [string]$p.name; Upn = [string]$p.upn } } continue } if (-not $p.members) { continue } $stack = [System.Collections.Generic.Stack[object]]::new() foreach ($m in @($p.members)) { $stack.Push($m) } while ($stack.Count) { $m = $stack.Pop() if ($null -eq $m) { continue } if ($m.type -eq 'User') { if ((([string]$m.upn) + ' ' + ([string]$m.name)) -match $extMark) { $external[([string]($m.upn ?? $m.name)).ToLowerInvariant()] = @{ Name = [string]$m.name; Upn = [string]$m.upn } } } elseif ($m.PSObject.Properties['members'] -and $m.members) { foreach ($c in @($m.members)) { $stack.Push($c) } } } } @{ Anonymous = $anon.ToArray(); Organization = $org.ToArray() Everyone = @($everyone | Sort-Object -Unique); External = @($external.Values) } } function Add-SPMStatsSheet { <# .SYNOPSIS One overview row per site: structure, principals, links and external counts. #> [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param([Parameter(Mandatory)]$Package, [Parameter(Mandatory)]$Scan) $ws = Add-Worksheet -ExcelPackage $Package -WorksheetName 'Site Stats' $headers = @('Site', 'URL', 'Rows', 'Unique permissions', 'Principals', 'Sharing links', 'Anonymous links', 'Org-wide links', 'External users') for ($c = 0; $c -lt $headers.Count; $c++) { $cell = $ws.Cells[1, ($c + 1)] $cell.Value = $headers[$c] $cell.Style.Font.Bold = $true $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $cell.Style.Fill.BackgroundColor.SetColor((ConvertTo-SPMColor 'F1F5F9')) } $r = 2 foreach ($site in @($Scan.sites)) { $facts = Get-SPMSiteRiskFacts -Site $site $ws.Cells[$r, 1].Value = [string]$site.title $ws.Cells[$r, 2].Value = [string]$site.url $ws.Cells[$r, 3].Value = [int]$site.stats.nodeCount $ws.Cells[$r, 4].Value = [int]$site.stats.uniqueNodeCount $ws.Cells[$r, 5].Value = [int]$site.stats.principalCount $ws.Cells[$r, 6].Value = [int]$site.stats.sharingLinkCount $ws.Cells[$r, 7].Value = @($facts.Anonymous).Count $ws.Cells[$r, 8].Value = @($facts.Organization).Count $ws.Cells[$r, 9].Value = @($facts.External).Count if (@($facts.Anonymous).Count) { $ws.Cells[$r, 7].Style.Font.Color.SetColor((ConvertTo-SPMColor 'B91C1C')); $ws.Cells[$r, 7].Style.Font.Bold = $true } if (@($facts.External).Count) { $ws.Cells[$r, 9].Style.Font.Color.SetColor((ConvertTo-SPMColor 'B45309')); $ws.Cells[$r, 9].Style.Font.Bold = $true } $r++ } $ws.Column(1).Width = 34 $ws.Column(2).Width = 60 for ($c = 3; $c -le 9; $c++) { $ws.Column($c).Width = 16 } $ws.View.FreezePanes(2, 1) } function Add-SPMRiskSheet { <# .SYNOPSIS Risk findings across all sites: anonymous/org links, Everyone assignments, external users. #> [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param([Parameter(Mandatory)]$Package, [Parameter(Mandatory)]$Scan) $ws = Add-Worksheet -ExcelPackage $Package -WorksheetName 'Risk' $row = [ref]1 $section = { param($Ws, [ref]$R, [string]$Title, [string[]]$Headers, [object[]]$Rows) $Ws.Cells[$R.Value, 1].Value = $Title $Ws.Cells[$R.Value, 1].Style.Font.Bold = $true $Ws.Cells[$R.Value, 1].Style.Font.Size = 12 $R.Value++ if (-not $Rows -or -not @($Rows).Count) { $Ws.Cells[$R.Value, 1].Value = 'none' $Ws.Cells[$R.Value, 1].Style.Font.Color.SetColor((ConvertTo-SPMColor '94A3B8')) $R.Value += 2 return } for ($c = 0; $c -lt $Headers.Count; $c++) { $cell = $Ws.Cells[$R.Value, ($c + 1)] $cell.Value = $Headers[$c] $cell.Style.Font.Bold = $true $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $cell.Style.Fill.BackgroundColor.SetColor((ConvertTo-SPMColor 'F1F5F9')) } $R.Value++ foreach ($line in @($Rows)) { for ($c = 0; $c -lt @($line).Count; $c++) { $Ws.Cells[$R.Value, ($c + 1)].Value = [string]@($line)[$c] } $R.Value++ } $R.Value++ } $anonRows = [System.Collections.Generic.List[object]]::new() $orgRows = [System.Collections.Generic.List[object]]::new() $evRows = [System.Collections.Generic.List[object]]::new() $extRows = [System.Collections.Generic.List[object]]::new() foreach ($site in @($Scan.sites)) { $facts = Get-SPMSiteRiskFacts -Site $site foreach ($k in $facts.Anonymous) { $anonRows.Add(@([string]$site.title, $k)) } foreach ($k in $facts.Organization) { $orgRows.Add(@([string]$site.title, $k)) } foreach ($k in $facts.Everyone) { $evRows.Add(@([string]$site.title, $k)) } foreach ($e in $facts.External) { $extRows.Add(@($e.Name, $e.Upn, [string]$site.title)) } } & $section $ws $row 'Anonymous sharing links ("Anyone with the link")' @('Site', 'Link type') $anonRows.ToArray() & $section $ws $row 'Organization-wide sharing links' @('Site', 'Link type') $orgRows.ToArray() & $section $ws $row '"Everyone" assignments' @('Site', 'Principal') $evRows.ToArray() & $section $ws $row 'External users (guests)' @('Name', 'UPN', 'Site') $extRows.ToArray() $ws.Column(1).Width = 40 $ws.Column(2).Width = 44 $ws.Column(3).Width = 34 } $script:SPMPermStyle = @{ FC = @{ Font = 'B91C1C'; Fill = 'FDECEC'; Pale = 'E8B4B4' } W = @{ Font = '1D4ED8'; Fill = 'EAF1FD'; Pale = 'AFC6EE' } C = @{ Font = '6D28D9'; Fill = 'F1EBFB'; Pale = 'CDBBE7' } R = @{ Font = '15803D'; Fill = 'EAF5EE'; Pale = 'AFD4BB' } X = @{ Font = '475569'; Fill = 'EEF1F4'; Pale = 'BFC7D1' } } $script:SPMTypeOrder = @{ SharePointGroup = 0; EntraGroup = 1; M365Group = 1; SecurityGroup = 1; User = 2; Everyone = 3; EveryoneExceptExternal = 3; Other = 3; SharingLink = 4 } function ConvertTo-SPMColor { [CmdletBinding()] param([Parameter(Mandatory)][string]$Hex) $r = [Convert]::ToInt32($Hex.Substring(0, 2), 16) $g = [Convert]::ToInt32($Hex.Substring(2, 2), 16) $b = [Convert]::ToInt32($Hex.Substring(4, 2), 16) return [System.Drawing.Color]::FromArgb(255, $r, $g, $b) } function Set-SPMPermCell { <# .SYNOPSIS Writes a permission code into a worksheet cell with the report's color scheme (hue = level, intensity = inheritance). #> [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Worksheet, [Parameter(Mandatory)][int]$Row, [Parameter(Mandatory)][int]$Column, [Parameter(Mandatory)][string]$Permission, [Parameter(Mandatory)][bool]$Unique ) $styleKey = if ($script:SPMPermStyle.ContainsKey($Permission)) { $Permission } else { 'X' } $style = $script:SPMPermStyle[$styleKey] $cell = $Worksheet.Cells[$Row, $Column] $cell.Value = $Permission $cell.Style.HorizontalAlignment = [OfficeOpenXml.Style.ExcelHorizontalAlignment]::Center $cell.Style.Font.Size = 9 if ($Unique) { $cell.Style.Font.Bold = $true $cell.Style.Font.Color.SetColor((ConvertTo-SPMColor $style.Font)) $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $cell.Style.Fill.BackgroundColor.SetColor((ConvertTo-SPMColor $style.Fill)) } else { $cell.Style.Font.Color.SetColor((ConvertTo-SPMColor $style.Pale)) } } function Get-SPMWorksheetName { [CmdletBinding()] param( [Parameter(Mandatory)][string]$Title, [Parameter(Mandatory)][string]$Suffix, [Parameter(Mandatory)][AllowEmptyCollection()][System.Collections.Generic.HashSet[string]]$UsedNames ) $clean = ($Title -replace '[\[\]\*\?/\\:]', ' ').Trim() $max = 31 - $Suffix.Length - 1 if ($clean.Length -gt $max) { $clean = $clean.Substring(0, $max).Trim() } $name = "$clean $Suffix" $i = 2 while (-not $UsedNames.Add($name)) { $name = "$($clean.Substring(0, [Math]::Min($clean.Length, $max - 2))) $Suffix$i" $i++ } return $name } function Add-SPMMatrixSheet { <# .SYNOPSIS Writes one matrix worksheet (rows = hierarchy, columns = the given column set). .DESCRIPTION $Columns is an array of @{ Header = <string>; CellFor = <scriptblock node -> @{Permission;Unique} or $null> }. #> [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Package, [Parameter(Mandatory)][string]$SheetName, [Parameter(Mandatory)]$Site, [Parameter(Mandatory)][object[]]$Columns ) $ws = Add-Worksheet -ExcelPackage $Package -WorksheetName $SheetName $ws.Cells[1, 1].Value = 'Hierarchy (site > library > folder)' $ws.Cells[1, 1].Style.Font.Bold = $true $ws.Cells[1, 1].Style.VerticalAlignment = [OfficeOpenXml.Style.ExcelVerticalAlignment]::Bottom for ($c = 0; $c -lt $Columns.Count; $c++) { $cell = $ws.Cells[1, ($c + 2)] $cell.Value = [string]$Columns[$c].Header $cell.Style.TextRotation = 90 $cell.Style.Font.Size = 9 $cell.Style.VerticalAlignment = [OfficeOpenXml.Style.ExcelVerticalAlignment]::Bottom $cell.Style.HorizontalAlignment = [OfficeOpenXml.Style.ExcelHorizontalAlignment]::Center $ws.Column($c + 2).Width = 4.5 } $ws.Row(1).Height = 115 $ws.Column(1).Width = 58 $uniqueFill = ConvertTo-SPMColor 'FFF7ED' $row = 2 foreach ($n in @($Site.nodes)) { $cell = $ws.Cells[$row, 1] $cell.Value = (' ' * (2 * [int]$n.level)) + [string]$n.title $cell.Style.Font.Size = 10 if ($n.hasUniquePermissions -and $n.level -gt 0) { $cell.Style.Font.Bold = $true $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $cell.Style.Fill.BackgroundColor.SetColor($uniqueFill) } for ($c = 0; $c -lt $Columns.Count; $c++) { $v = & $Columns[$c].CellFor $n if ($v) { Set-SPMPermCell -Worksheet $ws -Row $row -Column ($c + 2) -Permission $v.Permission -Unique ([bool]$n.hasUniquePermissions) } } $row++ } $row++ $ws.Cells[$row, 1].Value = 'FC = Full Control, W = Write (Edit), C = Contribute, R = Read, X* = custom levels. Bold/tinted = assigned here (unique), pale = inherited. Highlighted hierarchy rows = inheritance broken.' $ws.Cells[$row, 1].Style.Font.Size = 8 $ws.Cells[$row, 1].Style.Font.Italic = $true if ($Site.customRoles) { foreach ($prop in $Site.customRoles.PSObject.Properties) { $row++ $ws.Cells[$row, 1].Value = "$($prop.Name) = $($prop.Value)" $ws.Cells[$row, 1].Style.Font.Size = 8 } } $ws.View.FreezePanes(2, 2) return $ws } function Add-SPMGroupSheet { [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Package, [Parameter(Mandatory)]$Site, [Parameter(Mandatory)][AllowEmptyCollection()][System.Collections.Generic.HashSet[string]]$UsedNames, [switch]$IncludeSharingLinks ) $principals = @($Site.principals | Where-Object { $IncludeSharingLinks -or $_.type -ne 'SharingLink' } | Sort-Object { $script:SPMTypeOrder[[string]$_.type] ?? 3 }, name) $cellMaps = @{} foreach ($n in @($Site.nodes)) { $m = @{} foreach ($a in @($n.assignments)) { $m[[string]$a.principalId] = $a } $cellMaps[[string]$n.id] = $m } $columns = @(foreach ($p in $principals) { $principalId = [string]$p.id @{ Header = [string]$p.name CellFor = { param($n) $a = $cellMaps[[string]$n.id][$principalId]; if ($a) { @{ Permission = [string]$a.permission } } }.GetNewClosure() } }) $name = Get-SPMWorksheetName -Title ([string]$Site.title) -Suffix 'G' -UsedNames $UsedNames $null = Add-SPMMatrixSheet -Package $Package -SheetName $name -Site $Site -Columns $columns } function Add-SPMPeopleSheet { [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Package, [Parameter(Mandatory)]$Site, [Parameter(Mandatory)][AllowEmptyCollection()][System.Collections.Generic.HashSet[string]]$UsedNames, [switch]$IncludeSharingLinks ) $personMap = Get-SPMPersonMap -Site $Site $principalById = @{} foreach ($p in @($Site.principals)) { $principalById[[string]$p.id] = $p } # effective best permission per node/person $cells = @{} $personHasAny = [System.Collections.Generic.HashSet[string]]::new() foreach ($n in @($Site.nodes)) { $nodeCells = @{} foreach ($a in @($n.assignments)) { $p = $principalById[[string]$a.principalId] if (-not $p) { continue } if (-not $IncludeSharingLinks -and $p.type -eq 'SharingLink') { continue } $keys = @() if ($p.type -eq 'User') { $k = ([string]($p.upn ?? $p.name)).Trim().ToLowerInvariant() if ($k) { $keys = @($k) } } elseif ($personMap.PerPrincipal.ContainsKey([string]$p.id)) { $keys = @($personMap.PerPrincipal[[string]$p.id]) } foreach ($k in $keys) { [void]$personHasAny.Add($k) $existing = $nodeCells[$k] if (-not $existing -or (Get-SPMPermRank $a.permission) -lt (Get-SPMPermRank $existing)) { $nodeCells[$k] = [string]$a.permission } } } $cells[[string]$n.id] = $nodeCells } $persons = @($personMap.Persons | Where-Object { $personHasAny.Contains($_.key) }) $specials = @($Site.principals | Where-Object { $_.type -in 'Everyone', 'EveryoneExceptExternal' }) $columns = @() $columns += @(foreach ($person in $persons) { $key = $person.key @{ Header = [string]$person.name CellFor = { param($n) $v = $cells[[string]$n.id][$key]; if ($v) { @{ Permission = $v } } }.GetNewClosure() } }) $columns += @(foreach ($p in $specials) { $principalId = [string]$p.id @{ Header = [string]$p.name CellFor = { param($n) $a = @($n.assignments) | Where-Object { [string]$_.principalId -eq $principalId } | Select-Object -First 1; if ($a) { @{ Permission = [string]$a.permission } } }.GetNewClosure() } }) $name = Get-SPMWorksheetName -Title ([string]$Site.title) -Suffix 'P' -UsedNames $UsedNames $null = Add-SPMMatrixSheet -Package $Package -SheetName $name -Site $Site -Columns $columns } |