_overview.md

Overview

This Windows PowerShell script identifies site collections, subsites, folders or files which may have been inadvertaly overshared with either the Everyone or Everyone Except External Users (EEEU) claim, or any security group in which your content user account is a member.

The PowerShell script reports shared content types by executing a search query. Running this script as a domain or cloud based user account with no explicit access to any content, results in findings that inhertiently are shared with Everyone or EEEU.

Prequisites

• A user account (domain or cloud) with no explicit access to any content (except read access to root site) in SharePoint Online and OneDrive for Business.
• Account must have read (or higher) access to the SharePoint root site and the OD4B root site.
• Windows PowerShell 5.1 or higher
• PnP.PowerShell module version 1.12.0 or higher

Script Outputs

Find-SharedWithEveryoneOrEEEUContent.ps1

• The following content properties will be exported in the CSV file:
  • SiteUrl
  • WebUrl
  • FileName
  • FileExtension
  • Path
  • Created
  • LastModifiedTime
  • ViewableByExternalUsers
  • ContentClass
  • IsDocument (Added on 04/01/2024)
  • IsContainer (Added on 04/01/2024)

Get-SharedWithEveryoneOrEEEUContentSiteProperties.ps1

• The following site properties will be exported in the CSV file:
  • Url
  • WebsCount
  • Template
  • GroupId - only populated on M365 Group connected sites
  • Visibility - only populated on M365 Group connected sites
  • SensitivityLabel - only populated on sites with a Sensitivity Label applied
  • LastContentModifiedDate
  • OwnerEmail - the primary owner value, else M365 Group owners for M365 Group connected sites
  • HasTeam - true/false if connected to a Microsoft Team
  • SharedWithEveryoneOrEEEUObjectCount - Number of result rows for site in the input csv

Export-SiteCollection.ps1

• The function serves as a mechanism to export all rows matching a specific SiteUrl to a new file.

Required Permissions

Permissions for Find-SharedWithEveryoneOrEEEUContent.ps1

• To find overshared content, execute Find-SharedWithEveryoneOrEEEUContent.ps1 as a domain or cloud account with no access to content in SharePoint or OneDrive.

Permissions for Get-SharedWithEveryoneOrEEEUContentSiteProperties.ps1

• You can optionally collect additional site properties for sites identified as containing overshared content, which can be useful remediation and reporting activities. To use this script, you must authenticate to SharePoint Online using either delegatd or application (app-only) credentials, both of which require the PnP.Powershell application to be consented to by a global administrator.

  • Delegated
    • SharePoint Tenant Admin Role
    • Microsoft Graph > Delegated > Groups.Read.All
  • Application
    • SharePoint > Application > Sites.FullControl.All
    • Microsoft Graph > Application > Groups.Read.All

Execution

Find-SharedWithEveryoneOrEEEUContent.ps1

• Update line #222 & #223 with your environment specific values.
• Execute Find-SharedWithEveryoneOrEEEUContent.ps1. When prompted, enter credentails for the user account with no access to content.
• Wait patiently, the script will take several hours to complete on large tenants. It will intermittently flush results to the provided output path to avoid excessive memory pressure.

Get-SharedWithEveryoneOrEEEUContentSiteProperties.ps1

• Update line #98 & #99 with your environment specific values.
• Execute Get-SharedWithEveryoneOrEEEUContentSiteProperties.ps1. When prompted, enter credentails for the user account with no access to content.
• Wait patiently, the script may also take several hours to complete on large tenants.

Find-SharedWithEveryoneOrEEEUContent.ps1

#requires -Modules @{ ModuleName="PnP.PowerShell"; ModuleVersion="1.12.0" }

function Find-SharedWithEveryoneOrEEEUContent
{
    [CmdletBinding(DefaultParameterSetName="SharePoint")]
    param
    (
        [Parameter(Mandatory=$true,ParameterSetName="SharePoint")]
        [switch]
        $IncludeSharePointOnline,

        [Parameter(Mandatory=$true,ParameterSetName="OneDrive")]
        [switch]
        $IncludeOneDrive,

        [Parameter(Mandatory=$false)]
        [switch]
        $ExcludeFiles,

        [Parameter(Mandatory=$false)]
        [switch]
        $ExcludeFolders,

        [Parameter(Mandatory=$false)]
        [switch]
        $ExcludeWebs,

        [Parameter(Mandatory=$false)]
        [switch]
        $ExcludeSite,

        [Parameter(Mandatory=$false)]
        [switch]
        $ExcludeLists,

        [Parameter(Mandatory=$false)]
        [string]
        $OutputPath,

        [Parameter(Mandatory=$false)]
        [ValidateRange(1,500)]
        [int]
        $BatchSize = 500,

        [Parameter(Mandatory=$false)]
        [ValidateRange(1,5000)]
        [int]
        $BufferRowCount = 5000
    )

    begin
    {
        $context = Get-PnPContext -ErrorAction Stop

        $tenant = [System.Uri]::new($context.Url).Host.Split(".")[0]

        if( $PSCmdlet.ParameterSetName -eq "SharePoint" )
        {
            $tenantUrl = "https://$tenant.sharepoint.com"
        }
        else
        {
            $tenantUrl = "https://$tenant-my.sharepoint.com"
        } 

        $selectProperties = "SPSiteUrl", "SPWebUrl", "Filename", "FileExtension", "Path", "Created", "LastModifiedTime", "ViewableByExternalUsers", "ContentClass"

        $filters = @()

        if( -not $ExcludeFiles.IsPresent )
        {
            $filters += "IsDocument:true"

            $selectProperties += "IsDocument"
        }

        if( -not $ExcludeFolders.IsPresent )
        {
            $filters += "IsContainer:true"

            $selectProperties += "IsContainer"
        }

        if( -not $ExcludeLists.IsPresent )
        {
            $filters += "contentclass:STS_List_*"
        }

        if( -not $ExcludeWebs.IsPresent )
        {
            $filters += "contentclass:STS_Web"
        }

        if( -not $ExcludeSite.IsPresent )
        {
            $filters += "contentclass:STS_Site"
        }

        $query = "path:{0} ({1})" -f $tenantUrl, ($filters -join " OR ")

        Write-Verbose "$(Get-Date) - Executing query: '$query'"

        $columns = @($selectProperties | ForEach-Object { @{ Name="$_"; Expression=[ScriptBlock]::Create("`$_['$_']") }})

        $counter = 1

        $lastDocumentId = 0

        $fileCount = 0

        $objects = New-Object System.Collections.Generic.List[object]

        $startTime = [DateTime]::Now
    }
    process
    {
        if( $filters.Count -eq 0 )
        {
            Write-Error "You must include one or more of the files, folders, web or sites option"
            return
        }

        $stopwatch = [System.Diagnostics.Stopwatch]::StartNew()

        $attempts = 1

        while( $true )
        {
            $pagedQuery = "$Query AND IndexDocId > $lastDocumentId"

            try
            {
                $results = Invoke-PnPSearchQuery `
                                -Query            $pagedQuery `
                                -SortList         @{ "[DocId]" = "ascending" } `
                                -StartRow         0 `
                                -MaxResults       $BatchSize `
                                -TrimDuplicates   $true `
                                -SelectProperties $SelectProperties `
                                -ErrorAction      Stop
            }
            catch
            {
                if( $attempts -le 10 )
                {
                    $seconds = (5 + $attempts)
                    
                    if( $attempts -eq 10 )
                    {
                        $seconds = 120
                    }

                    Write-Warning "Failed to process page $counter on attempt $attempts, retrying in $seconds seconds."

                    Write-Verbose "$(Get-Date) - Error detail: $($_)"
                    Write-Verbose "$(Get-Date) - Exception detail: $($_.Exception.ToString())"

                    Start-Sleep -Seconds $seconds

                    $attempts++

                    continue
                }

                throw "Failed to process page: $($_)"
            }

            $attempts = 1

            if( $null -ne $results -and $null -ne $results.ResultRows -and $results.RowCount -gt 0 )
            {
                $fileCount += $results.RowCount

                if( $lastDocumentId -gt 0 -and $counter % 10 -eq 0 -and $fileCount -lt $totalRows )
                {
                    $average = [Math]::Round( $stopwatch.Elapsed.TotalSeconds / $counter, 2 )
                    
                    $totalseconds = $estimatedbatches * $average

                    Write-Verbose "$(Get-Date) - Estimated Completion: $($startTime.AddSeconds($totalseconds))"
                }

                if( $lastDocumentId -eq 0 )
                {
                    $totalRows        = $results.TotalRows
                    $estimatedbatches = [Math]::Max( [Math]::Round( $totalRows/$BatchSize, 0), 1)

                    Write-Verbose "$(Get-Date) - Estimated Page Count: $estimatedbatches"
                }

                Write-Verbose "$(Get-Date) - Processing Page: $($counter), Page Result Count: $($results.RowCount)"

                $lastDocumentId = $results.ResultRows[-1]["DocId"].ToString()
                
                if( $PSBoundParameters.ContainsKey( "OutputPath" ) )
                {
                    if( $counter -eq 1 )
                    {
                        # need header on the first row
                        $rows = ($results.ResultRows | Select-Object $columns | ConvertTo-Csv -NoTypeInformation) -as [System.Collections.Generic.List[object]]
                    }
                    else
                    {
                        # no header on subsequent exports
                        $rows = ($results.ResultRows | Select-Object $columns | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1) -as [System.Collections.Generic.List[object]]
                    }

                    $objects.AddRange($rows)

                    if( $objects.Count -ge $BufferRowCount )
                    {
                        $sw = Measure-Command -Expression { $objects | Out-File -FilePath $OutputPath -Append -Confirm:$false }

                        Write-Verbose "$(Get-Date) - `t`tFlushed $($objects.Count) rows in $([Math]::Round( $sw.TotalMilliseconds, 0))ms"

                        $objects.Clear()
                    }
                }
                else
                {
                    $results.ResultRows | Select-Object -Property $columns 
                }
            }
            else
            {
                break
            }

            $counter++
        }
    
        if( $objects.Count -gt 0 )
        {
            Write-Verbose "$(Get-Date) - Flushing remaining $($objects.Count) rows to disk."

            if( $PSVersionTable.PSVersion.Major -le 5 )
            {
                $objects | Out-File -FilePath $OutputPath -Append -Confirm:$false -Encoding ascii # allows for no fuss opening in Excel
            }
            else
            {
                $objects | Out-File -FilePath $OutputPath -Append -Confirm:$false
            }
            
            $objects.Clear()
        }
        
        Write-Verbose "$(Get-Date) - Exported $($fileCount) search results in $([Math]::Round( $stopwatch.Elapsed.TotalMinutes, 2)) minutes."
    }
    end
    {
    }
}

$outputPath = "C:\_temp"
$tenantName = "contoso" 

# Permissoins Required: RUN AS ACCOUNT WITH NO ACCESS TO SHAREPIONT OR ONEDRIVE CONTENT
Connect-PnPOnline `
        -Url "https://$tenantName.sharepoint.com" `
        -Interactive `
        -ForceAuthentication

$timestamp = Get-Date -Format FileDateTime

Find-SharedWithEveryoneOrEEEUContent `
        -IncludeSharePointOnline `
        -OutputPath "$outputPath\SharePoint_EveryoneOrEEEUSharedItems_$timestamp.csv" `
        -Verbose

Find-SharedWithEveryoneOrEEEUContent `
        -IncludeOneDrive `
        -OutputPath "$outputPath\OneDrive_EveryoneOrEEEUSharedItems_$timestamp.csv" `
        -Verbose

Get-SharedWithEveryoneOrEEEUContentSiteProperties.ps1

#requires -Modules @{ ModuleName="PnP.PowerShell"; ModuleVersion="1.12.0" }

function Get-SharedWithEveryoneOrEEEUContentSiteProperties
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory=$true)]
        [string]
        $Path
    )

    begin
    {
        $dictionary = [System.Collections.Generic.Dictionary[string, int]]::new()
    }
    process
    {
        $counter = 0

        try
        {
            Write-Verbose "$(Get-Date) - Parsing file: $Path"

            $streamReader = [System.IO.File]::OpenText( $Path )
        
            while ( $line = $streamReader.ReadLine() )
            {
                if( $counter -eq 0 )
                {
                    $counter++
                    
                    continue # skip header row
                }
        
                $url = ($line -split ",")[0]
                
                $key = $url -replace "`"", ""
                $key = $key.ToLower() # 04/01/2024 - fix for duplicate site results
        
                if( -not $dictionary.ContainsKey($key ) )
                {
                    $null = $dictionary.Add( $key, 1 )
                }
                else
                {
                    $dictionary[$key]++
                }
        
                $counter++
            }
        }
        finally
        {
            if( $null -ne $streamReader )
            {
                $streamReader.Close()
                $streamReader.Dispose()
            }
        }

        Write-Verbose "$(Get-Date) - Looking up site properties"

        $sites = foreach( $key in $dictionary.Keys )
        {
            Write-Verbose "$(Get-Date) - Looking up site properties for $key"

            $site = Get-PnPTenantSite -Identity $key -Detailed | Select-Object Url, WebsCount, Template, GroupId, Visibility, SensitivityLabel, LastContentModifiedDate, OwnerEmail, HasTeam, SharedWithEveryoneOrEEEUObjectCount
            $site.SharedWithEveryoneOrEEEUObjectCount = $dictionary[$key]

            if( $site.GroupId -ne "00000000-0000-0000-0000-000000000000" )
            {
                try
                {
                    Write-Verbose "$(Get-Date) - Looking up M365 Group properties"

                    $group = Get-PnPMicrosoft365Group -Identity $site.GroupId -IncludeOwners -Verbose:$false -ErrorAction Stop

                    $site.Visibility = $group.Visibility
                    $site.HasTeam    = $group.HasTeam
                    $site.OwnerEmail = $group.Owners.UserPrincipalName -join ","
                }
                catch
                {
                    Write-Warning "Failed to lookup group: $($site.GroupId). Error: $($_)"
                }
            }

            $site
        }

        return $sites
    }
    end
    {
    }
}

$outputPath = "C:\_temp"
$tenantName = "contoso" 

# Permissoins Required
#    Delegated Option:   SharePoint Tenant Admin + Microsoft Graph > Delegated > Directory.All
#    Application Option: SharePoint > Application > Sites.FullControl.All + Microsoft Graph > Application > Directory.All
Connect-PnPOnline `
        -Url "https://$($tenantName)-admin.sharepoint.com" `
        -Interactive `
        -ForceAuthentication

$timestamp = Get-Date -Format FileDateTime
        
Get-SharedWithEveryoneOrEEEUContentSiteProperties -Path "$outputPath\SharePoint_EveryoneOrEEEUSharedItems_20231013T1313371108.csv" -Verbose | 
    Export-Csv -Path "$outputPath\SharePointProperties_EveryoneOrEEEUSharedItems_$timestamp.csv" -NoTypeInformation

Get-SharedWithEveryoneOrEEEUContentSiteProperties -Path "$outputPath\OneDrive_EveryoneOrEEEUSharedItems_20231013T1313371108.csv" -Verbose | 
    Export-Csv -Path "$outputPath\OneDriveProperties_EveryoneOrEEEUSharedItems_$timestamp.csv" -NoTypeInformation

Get-SiteCollectionRows.ps1

<#
    .SYNOPSIS
    Exports all matching rows from the input .csv and writes matches to a new .csv file

    .DESCRIPTION
    Exports all rows having a matching SPSiteUrl value from the provided input file to a new CSV file

    .PARAMETER InputPath
    Path to the input csv file.  This is expected to be the output file from Find-SharedWithEveryoneOrEEEUContent.ps1

    .PARAMETER Path
    Path to write the .csv results

    .PARAMETER SiteUrl
    Url of the SharePoint site to match

    .OUTPUTS
    None

    .EXAMPLE
    Export-SiteCollection -InputPath "C:\_temp\SharePoint_EveryoneOrEEEUSharedItems_20231027T0925132119.csv" -SiteUrl "https://contoso.sharepoint.com/sites/teamsite" -Path"C:\_temp\rootsitematches.csv"

#>
function Export-SiteCollection
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory=$true)]
        [string]
        $InputPath,

        [Parameter(Mandatory=$true)]
        [string]
        $Path,

        [Parameter(Mandatory=$true)]
        [string]
        $SiteUrl
    )

    begin
    {
        $SiteUrl = $SiteUrl.TrimEnd("/")
    }
    process
    {
        try
        {
            $streamReader = [System.IO.File]::OpenText( $InputPath )

            $rows = while ( $line = $streamReader.ReadLine() )
            {
                $chunks = $line -split ","

                $url = $chunks[0]
            
                $url = $url -replace "`"", ""

                if( $url -eq $SiteUrl )
                {
                    [PSCustomObject] @{
                        SPSiteUrl               = $chunks[0].TrimStart('"').TrimEnd('"')
                        SPWebUrl                = $chunks[1].TrimStart('"').TrimEnd('"')
                        Filename                = $chunks[2].TrimStart('"').TrimEnd('"')
                        FileExtension           = $chunks[3].TrimStart('"').TrimEnd('"')
                        Path                    = $chunks[4].TrimStart('"').TrimEnd('"')
                        Created                 = $chunks[5].TrimStart('"').TrimEnd('"')
                        LastModifiedTime        = $chunks[6].TrimStart('"').TrimEnd('"')
                        ViewableByExternalUsers = $chunks[7].TrimStart('"').TrimEnd('"')
                        ContentClass            = $chunks[8].TrimStart('"').TrimEnd('"')
                    }
                }
            }

            if( $rows )
            {
                $rows | Sort-Object Path | Export-Csv -Path $Path -NoTypeInformation
            }
            else
            {
                Write-Warning "No matches found for $SiteUrl in $InputPath"
            }
        }
        catch
        {
            Write-Error "Failed to parse input file: $_"
        }
        finally
        {
            if( $null -ne $streamReader )
            {
                $streamReader.Close()
                $streamReader.Dispose()
            }            
        }
    }
    end
    {
    }
}

Export-SiteCollection `
    -InputPath "C:\_temp\SharePoint_EveryoneOrEEEUSharedItems_20231027T0925132119.csv" `
    -SiteUrl   "https://contoso.sharepoint.com/sites/teamsite" `
    -Path      "C:\_temp\SharePoint_EveryoneOrEEEUSharedItems_20231027T0925132119_root.csv"

I'm new to PnP powershell, and noticed when installing either through Enterprise App or as an App Registration, it assigns 'Sites.FullControl.All' as 'Application' and 'AllSites.FullControl' as 'Delegated'. So with those level of permissions, how is this script supposed to work if the account running this script is using an app registration with so much permissions? Since the Enterprise App is not configurable, i deleted it and created the App Registration and removed the application permissions 'Sites.FullControl.All', but left the delegated permissions 'AllSites.FullControl', and reran script. However this time it errored with "The current principal does not have permission to execute queries on behalf of other users.". So far, i either get a list of all files due to 'Sites.FullControl.All' or script errors out when i remove those permissions. Any guidance? Thanks in advance. By the way, cool script.

I'm new to PnP powershell, and noticed when installing either through Enterprise App or as an App Registration, it assigns 'Sites.FullControl.All' as 'Application' and 'AllSites.FullControl' as 'Delegated'. So with those level of permissions, how is this script supposed to work if the account running this script is using an app registration with so much permissions? Since the Enterprise App is not configurable, i deleted it and created the App Registration and removed the application permissions 'Sites.FullControl.All', but left the delegated permissions 'AllSites.FullControl', and reran script. However this time it errored with "The current principal does not have permission to execute queries on behalf of other users.". So far, i either get a list of all files due to 'Sites.FullControl.All' or script errors out when i remove those permissions. Any guidance? Thanks in advance. By the way, cool script.

Hi @tinojr,

The Find-SharedWithEveryoneOrEEEUContent.ps1 script is using a delegated context, so the script only has access to content that the authenticated user has access to in the tenant.

The Get-SharedWithEveryoneOrEEEUContentSiteProperties.ps1 script can run in either a delegated or application (app-only) context. If you run in the application context, the script would have access to all OD/SP content that has been granted to the app registration used during authentication.

An global administrator will need to consent to the PnP.PowerShell Enterprise Application in Entra ID by prior to running Find-SharedWithEveryoneOrEEEUContent.ps1. This enables the PnP.PowerShell app to act on behalf of a user, using only the permissions the user already has, nothing more.