Reporting SharePoint Online External Users with PowerShell

SharePoint External Users From Guest Members and Sharing

A SharePoint external user is someone who doesn’t have an account in your tenant. Because of the influence of Teams, most SharePoint Online external users are guest accounts, created when external people join the membership of Microsoft 365 Groups (teams). If the organization uses the SharePoint Online integration with Azure AD B2B collaboration, SharePoint also creates guest accounts when people share files or folders with external people.

As discussed in this article, it’s reasonably easy to generate a report of the membership of all Microsoft 365 groups in a tenant. The report includes guest accounts and can be used to figure out if guests from the wrong places (like competitors) have access to information in your tenant. However, the script that creates the report relies on cmdlets like Get-UnifiedGroupLinks or Graph API requests to return details of group members, and these exclude any mention of guest accounts in a SharePoint site that aren’t members of the group which owns the site.

PnP Samples Repository

This brings me neatly to a script to report external users posted in the PnP Samples repository (a useful place to go for SharePoint-centric code examples). Reflecting that there are usually multiple ways to solve a problem, three versions are available (CLI for Microsoft 365, SharePoint Online PowerShell module, and PnP PowerShell).

Unhappily, there doesn’t appear to be a good way to retrieve the external users for a site using a Graph API request. You can certainly find the set of all guest accounts in a tenant, or the guest accounts for a team/group, but these methods exclude the guest accounts added for sharing purposes.

The Oddness of Get-SPOExternalUser

The lack of a better method is why the scripts found on the internet use the Get-SPOExternalUser cmdlet. It’s an odd cmdlet in some ways.

For example, Get-SPOExternalUser has a PageSize parameter to limit the number of external users returned. The maximum is 50, which means that if more than this number of external users exist for a site, you must continue fetching until all are retrieved (the Position parameter controls the start of the page of users to fetch). You end up with commands like:

[array]$users = Get-SPOExternalUser -SiteUrl $SiteId -PageSize 50 -Position 50

And after fetching a page of user data, you must combine it with the other pages to get a complete set. Although pagination is common with Graph API requests, it’s unusual to see it used like this with a cmdlet that could surely benefit from a parameter to fetch all matching items, like:

Get-SPOExternalUser -Limit All

Moving onto the output, here’s an example of the data returned for an external user (guest account):

Email         : vasil@michevxx.com
DisplayName   : Vasil Michev (MVP)
UniqueId      : 1003BFFD9AF15B76
AcceptedAs    : vasil@michevxx.com
WhenCreated   : 05/11/2018 18:46:40
InvitedBy     :
LoginName     :
IsCrossTenant : False

As far as I can tell, the InvitedBy and LoginName properties are not used. Across all the sites in my tenant, I found one instance of the InvitedName property being populated. In that case, the property held the user principal name of the guest account, and I couldn’t figure out how this happened.

The AcceptedBy property holds the name of the account that accepted the invitation to the site (to share a document or as a guest member). This property is not populated for sites belonging to shared Teams channels. Instead, a LoginName property captures the account used to connect to the channel site.

The WhenCreated property also deserves some comment. It seems like Microsoft reset this value for many accounts at around 18:46 UTC on 5 November 2018. Many accounts across multiple sites have this creation date. It’s an unnatural concentration of external users created at a specific time on that date. I can’t explain it.

Creating a SharePoint External Users Report

Your account needs to hold the Global tenant administrator or SharePoint administrator role to run this script and generate a SharePoint external users report. The steps are straightforward, which is probably why so many versions are available online. This version captures some extra information about the channel-connected sites used by Teams.

• Find all sites.
• For each site, get its external members.
• Create a report file.

Here’s the script:

$Sites = Get-SPOSite -Limit All | Sort-Object Title

$ExternalSPOUsers = [System.Collections.Generic.List[Object]]::new() 

#Iterate through each site and retrieve external users
$Counter = 0
ForEach ($Site in $Sites) {
    $Counter++
    Write-Host ("Checking Site {0}/{1}: {2}" -f $Counter, $Sites.Count, $Site.Title)
    [array]$SiteUsers = $Null
    $i = 0; $Done = $False
    Do {
      [array]$SUsers = Get-SPOExternalUser -SiteUrl $Site.Url -PageSize 50 -Position $i
      If ($SUsers) { 
        $i = $i + 50
        $SiteUsers = $SiteUsers + $SUsers }
      If ($SUsers.Count -lt 50) {$Done = $True}   
    }  While ($Done -eq $False)

    ForEach ($User in $SiteUsers) {
       $ReportLine    = [PSCustomObject] @{  
         Email        = $User.Email 
         Name         = $User.DisplayName
         Accepted     = $User.AcceptedAs
         Created      = $User.WhenCreated
         SPOUrl       = $Site.Url
         TeamsChannel = $Site.IsTeamsChannelConnected
         ChannelType  = $Site.TeamsChannelType
         CrossTenant  = $User.IsCrossTenant
         LoginName    = $User.LoginName }
        $ExternalSPOUsers.Add($ReportLine) }
} #End ForEach Site

Playing with PSWriteHTML

Now that we have some data to report, I’ll reveal that the real reason for this article is to mention the PSWriteHTML module. The module is maintained by Przemyslaw Klys and its job is to make HTML output easier to generate for PowerShell scripts. The ImportExcel module is another example of a community-created module to help people generate nicer output.

In any case, to create a HTML report, I used these commands:

Import-Module PSWriteHTML.psd1 -Force
$ExternalSPOUsers | Sort Email | Out-HtmlView -HideFooter -Title "SharePoint Online External Users Report"

Figure 1 shows the output, which at first glance looks like a nicer version of the output generated by the Out-GridView cmdlet. The important difference is that you can export the HTML report in different formats, including a nice PDF file.

Having different options to share information is a nice thing. If you create reports from PowerShell, consider having a look at the PSWriteHTML module. It might solve some problems for you. After all, it created a prettier SharePoint External Users report for me!

---

Learn more about how the Office 365 applications really work on an ongoing basis by subscribing to the Office 365 for IT Pros eBook. Our monthly updates keep subscribers informed about what’s important across the Office 365 ecosystem.