The story in short: one of my customers asked me if it is possible to generate a report on all content in Office365 shared externally. Doing some searches I found the following solutions:
– Run the sharing reports on each site and each OneDrive (link, link)
– Run reports based on audit logs (link)

While these reports may seem adequate for most of the time, I have some issues with them:
– Native reporting capabilities require opening each site manually, these reports contain internal sharings too
– Some info may be missing from these native reports (for example: expiration date for date limited sharing links, password protection property, email address of tenant guests who haven’t opened the link yet)
– Audit log based reports’ capabilities are limited by audit log retention

So these issues gave me the intention to write a script that fits my needs and I hope others will benefit from it too.

Important note: Sharepoint sharing will soon default to Azure B2B Invitation Manager for External Sharing (link). You may want to review your affected settings (link).

TL;DR

• create an app registration in your tenant with the following application permissions for Graph API:
  • Sites.Read.All (will be needed for accessing every SPO site)
• give the app a client secret which will be used to authenticate
  • it is more secure to opt for certificate-based auth (great article on this here) but I did not have the occasion to test it, so I stay with client secret now
• Copy the following script below
• $tenantID, $appID, $appSecret variables need to be declared
• The script has one required parameter (-ReportFile) which should be the path of the HTML report (parent directory must exist) and two mutually exclusive parameters: -All if you want a report on all document libraries in your tenant (SharePoint sites and OneDrive for Business too) OR -SiteUrl <string[]> which will report only on the site specified. Example:
  • PS C:\temp> .\Get-SPExternalSharingReport.ps1 -ReportFile C:\temp\reportDaniel1.html -SiteUrl “https://ftwelvehu-my.sharepoint.com/personal/daniel_f12_hu”
  • PS C:\temp> .\Get-SPExternalSharingReport.ps1 -ReportFile C:\temp\reportAll.html -All

[CmdletBinding(DefaultParametersetName="default")]
Param(
    [Parameter(Mandatory=$true)][string]$ReportFile,
    [parameter(ParameterSetName="seta")][string]$SiteUrl,
    [parameter(ParameterSetName="setb")][switch]$All
)

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$tenantID = '<tenantID>'
$appID = '<application (client) ID>'
$appSecret = '<client secret>'
$scope = 'https://graph.microsoft.com/.default'
$oAuthUri = "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token"
$body = [Ordered] @{
    scope = "$scope"
    client_id = "$appId"
    client_secret = "$appSecret"
    grant_type = 'client_credentials'
}
$headers = $null
$tokenexpiration = $null
function Check-AccessToken {
    if (((Get-date).AddMinutes(5)) -gt $tokenExpiration){
     #   Write-host "Token expires in 5 minutes, refreshing" -ForegroundColor Yellow
        $response = Invoke-RestMethod -Method Post -Uri $oAuthUri -Body $body -ErrorAction Stop
        $AccessToken = $response.access_token
        $script:tokenExpiration = (Get-date).AddSeconds($response.expires_in)
    
#Define headers
    $script:headers = @{ 
    'Content-Type' = 'application/json'
    'Accept' = 'application/json'
    'Authorization' = "Bearer $AccessToken" 
    }
 }
}
Check-AccessToken
#Get all SP sites
$url = 'https://graph.microsoft.com/v1.0/sites/'
$spSites = $null
while ($url -ne $null){
        Check-AccessToken
        $json_response =  (Invoke-WebRequest -Method Get -Uri $url -Headers $headers -ErrorAction Stop | ConvertFrom-Json) 
        $spSites += $json_response.value
        $url = $json_response.'@odata.nextLink'
    }


function Get-SPSiteSharedDocuments ($siteID){
    $url = "https://graph.microsoft.com/v1.0/sites/$($siteid)/lists"
    $obj_DocumentsList = $null
   # Write-host -ForegroundColor Yellow "Querying site lists $url"
    while ($url -ne $null){
        Check-AccessToken
        $json_response =  (Invoke-WebRequest -Method Get -Uri $url -Headers $headers -ErrorAction Stop | ConvertFrom-Json) 
        $obj_DocumentsList += $json_response.value
        $url = $json_response.'@odata.nextLink'
    }
    $obj_DocumentsList = $obj_DocumentsList | ? {$_.list.template -match "DocumentLibrary"} #mySiteDocumentLibrary for OneDrive, documentLibrary for SP
    foreach ($doclib in $obj_DocumentsList){
        $url = "https://graph.microsoft.com/v1.0/sites/$($siteid)/lists/$($doclib.id)/items?expand=driveitem"
   #     Write-host -ForegroundColor Yellow "Querying documents $url"
        $ListItems = $null
        while ($url -ne $null){
            Check-AccessToken
            Write-host -ForegroundColor Yellow "Querying documents $url"
            $json_response =  (Invoke-WebRequest -Method Get -Uri $url -Headers $headers -ErrorAction Stop | ConvertFrom-Json)
            $ListItems += $json_response.value
            $url = $json_response.'@odata.nextLink'
    }   
    $ListItems | % {$_.driveitem} | ? {$_.shared}
    }
}

function Get-SPSharedDocumentPermission ($driveID,$docID){
    $url = "https://graph.microsoft.com/v1.0/drives/$($driveID)/items/$($docID)/permissions"
   # write-host $url -ForegroundColor Yellow
    $obj_Permissions = $null
        while ($url -ne $null){
        Check-AccessToken
        $json_response =  (Invoke-WebRequest -Method Get -Uri $url -Headers $headers -ErrorAction Stop | ConvertFrom-Json)
        $obj_Permissions += $json_response.value
        $url = $json_response.'@odata.nextLink'
    }   

    $obj_Permissions | ? {$_.link}
    }

function Get-SPSiteDocSharingReport ($siteid){
 foreach ($item in (Get-SPSiteSharedDocuments $siteid)){
    Get-SPSharedDocumentPermission $item.parentreference.driveid $item.id | % {
    [pscustomobject]@{
        WebURL = ($spSites.Where({$_.id -eq $siteid}) ).weburl
        Path = $item.parentReference.path.substring($item.parentReference.path.indexof("root:"))
        ItemName = $item.name
        Role = $_.roles -join ","
        HasPassword = $_.haspassword
        ExpirationDate = $_.expirationDateTime
        Scope = $_.link.scope
        GuestUserMail = ($_.grantedtoidentitiesv2.siteuser.loginname | ? {$_ -match 'guest#'} | % {$_.split('#') | select -Last 1} | select -Unique ) -join ", "
        AADExternaluserUPN = ($_.grantedtoidentitiesv2.siteuser.loginname | ? {$_ -match '#ext#'} | % {$_.split('|') | select -Last 1} | select -Unique ) -join ", "
        } | % {if(($_.scope -eq "users") -and ($_.guestusermail -eq "") -and ($_.AADExternaluserUPN -eq "")){}else{$_}} # filter out entries shared only with org users
    }
   } 
}

$HTMLHeader = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@
 
 if ($All){
$obj_report = foreach ($site in $spSites){Write-host "querying site $($site.weburl)" -ForegroundColor Yellow ;Get-SPSiteDocSharingReport $site.id}
$obj_report | ConvertTo-Html -Head $HTMLHeader | Out-File $ReportFile
}

 if ($SiteUrl){
   $siteToQuery = $spSites.Where({$_.webUrl -eq $SiteUrl})
   if ($siteToQuery){Get-SPSiteDocSharingReport $siteToQuery.id | ConvertTo-Html -Head $HTMLHeader | Out-File $ReportFile }else{Write-Host -ForegroundColor Red "Site not found"} 
 }

Example result:

Explained

The script starts with the authentication process. Nothing new here, except for the Check-AccessToken function which will be used before each webrequest:

Access tokens tipically expire in 1 hour so it needs to be refreshed during script execution (if running for more than 1 hour). This little function will renew the access token 5 minutes before expiration.

First step is to query all sites and store it in a variable (@odata.nextlink logic explained below):

Then we declare the Get-SPSiteSharedDocuments function, which does the following:

• queries the lists for the site specified (document libraries are list items too)
• selects those lists that are based on a document library template (based on my research the template is mySiteDocumentLibrary for OneDrive and documentLibrary for SharePoint sites)
• because it is possible to have multiple document libraries in a site, we loop through each library and query the items in the list (MS doc here)
• if there are more than 200 items in a list the result are paged which is reflected in the response – it contains the url for the next page in @odata.nextLink, so we use a while statement to go through each site until the response does not containt this @odata.nextLink member
• at the end of the function only those driveitems are selected which have a member named “shared”

Next function is the Get-SPSharedDocumentPermission. This function needs the driveID and the documentID as parameter and returns only those items that have a member named “link”. Some things to note:
– MS doc on the permissions API request (here) states the following:
The permissions relationship of DriveItem cannot be expanded as part of a call to get DriveItem or a collection of DriveItems. You must access the permissions property directly.
This is why it is called separately.
– SharePoint content shared externally is always link based (as far as I know) this is why only those items are selected

The last function creates the report itself. The Get-SPSiteDocSharingReport creates a pscustomobject with the information displayed in the HTML. There are some points that are not the most beautiful (this may be due to my lack of hardcore scripting skills), but let me try to explain 🙂
– Path: the original answer didn’t seem to contain a relative path, so this one is derived from the parentreference of the driveItem, example:

– HasPassword: if the document sharing is protected with a password, than this one is reflected here (not visible on the native report)
– ExpirationDate: if the sharing link is valid for a limited time, than expiration is shown here (not visible on the native report)
– Scope: anonymous (anyone with the link can access), user (only the user specified can open), organisation (shared with everyone in the org – in an external sharing report this can be relevant if you have guest users in the tenant)
– GuestUserMail and AADExternalUserUPN: this took me some time to figure out. Link based sharings have the grantedtoidentitiesv2 property (link). This property may contain user and siteuser objects, user is a microsoft.graph identity while siteuser is a sharePointIdentity (link). It means that (I guess) every invitee gets a siteuser identity but those that can be mapped to an AzureAD identity will be represented as a user object too. When B2B Invitation Manager is enabled then these two will contain the same entries. My experience is that the mapped user object doesn’t get its email attribute populated until the invitee opens the link (the native report only shows displayname for these entries). So to include all invitee, I decided to rely on siteuser.loginname which is not too human readable but can be parsed. If it contains ‘guest#’ then the email address is extracted; ‘#ext#’ refers to an AzureAD guest user and its UPN is returned.

Because internal sharing can be based on link too, entries where the scope is user but either GuestUserMail or AADExternalUserUPN is populated (= only shared with org users) are filtered out.

The rest of the script is just the header for the HTML report and the execution of these functions. When using the -All parameter, the script will display the URL of the site being queried. When passing an invalid URL to the -SiteURL parameter, the script will display a “Site not found” message. Invalid here is a URL which is not in the $spSites variable.

I really want to emphasise that many findings here are based on experience and testing – but not based on documentations (except where I refer to MS docs). You may eventually want to countercheck the results against the native reports.