Create a report of SharePoint Online External Users with PowerShell

One of the really cool things that we can do with SharePoint Online is to easily share documents, or sites with external users. The cooler thing even is that now my users can securely share stuff, without having to go through IT, so as an Office 365 Admin, I have a lot more time to be productive, rather then create FBA or AD accounts for my external users as I did On-Premises. However, even if I don’t have to do it myself, I still want to stay informed about what external users have access to what, and who got added to what Site Collection, so I have created a few scripts for it!

My first report is one that I run on a weekly basis, and it’s a easy one: What users have been added to my SharePoint Online environment in the last 7 days? The script is simple, I will use the Get-SPOExternalUser cmdlet to get all of my external users, and I am using the loop that I have blogged about here (Getting More than 50 users with the Get-SPOExternalUser PowerShell cmdlet in SharePoint Online) in order to get more than the limit of 50 per query. I am adding a Where statement, where I specify that I only want users that have the WhenCreated property bigger than Today – 7 days, which means they have been created in the last 7 days! Lastly, I am selecting the properties that I want to show and format the result as a table.

try {
    for ($i=0;;$i+=50) {
        $ExternalUsers += Get-SPOExternalUser -PageSize 50 -Position $i -ea Stop | Where {$_.WhenCreated -gt((get-date).adddays(-7))} 
    }}
catch {}
$ExternalUsers

Here is what this looks like in action:

My second report is something that you can either run on a scheduled basis, or only whenever security asks for a more formal report on what is happening in the tenant. What it does is that it will loop trough every Site Collection, and then output every external user that has access to that Site Collection. We will save information about the user in a custom object, as well as the URL of the site they had access to. Lastly, we will export everything to CSV, which as you know allows us to do some nice filtering directly in Excel, or if you want to take it to the next level you can even use it as a data source for a Power BI report let’s say, but we will not get into that topic today. Here is the script:

$SiteCollections  = Get-SPOSite -Limit All
foreach ($site in $SiteCollections)
{
try {
    for ($i=0;;$i+=50) {
        $ExternalUsers += Get-SPOExternalUser -SiteUrl $site.Url -PageSize 50 -Position $i -ea Stop | Select DisplayName,EMail,AcceptedAs,WhenCreated,InvitedBy,@{Name = "Url" ; Expression = { $site.url } }
    }
}
catch {
}
}
$ExternalUsers | Export-Csv -Path "C:\PowerShell\ExternalUsersPerSC.csv" -NoTypeInformation

And here is the result in the CSV File opened in Excel! I could use filtering on the URL, or on the User license if I wanted to get more info!

The last report that I want to show you is a really interesting one that can be really important for your organization: It shows you all the external accounts that have been invited to your tenant using an e-mail address, but they have accepted the invitation and are using a different e-mail address to authenticate to your SharePoint Online. To put it in a more real-life example, let’s say you created a site to work with Contoso, and you invited vlad@contoso.com to your SharePoint Online Site Collection. When receiving the invitation, Vlad has decided to use his personal e-mail address: vlad@hotmail.com to authenticate to your Site Collection. If Vlad decides to leave Contoso, even if his corporate account is disabled, he can still have access to the Site Collection you shared with him since the account that has access is the personal one. If your NDA agreement for example was between Contoso / Your Company and he’s not at Contoso anymore, this can cause you troubles down the road! To get those accounts, you need to use the following parameter, which probably can win an award for one of the longest parameters in PowerShell: ShowOnlyUsersWithAcceptingAccountNotMatchInvitedAccount. Here is the full code:

try 
{
    for ($i=0;;$i+=50)
	{
        $ExternalUsers += Get-SPOExternalUser -ShowOnlyUsersWithAcceptingAccountNotMatchInvitedAccount $true -PageSize 50 -Position $i  -ea Stop
    }
}
catch 
{
}
$ExternalUsers

And here is a screenshot of the result:

Follow me on Social Media and Share this article with your friends!