theme-sticky-logo-alt

Create a report of SharePoint Online External Users with PowerShell

11 Comments

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:

Create a report of SharePoint Online External Users with PowerShell

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!

Create a report of SharePoint Online External Users with PowerShell

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:

Create a report of SharePoint Online External Users with PowerShell

Leave a comment and don’t forget to like the Vlad Talks Tech Page   on Facebook and to follow me on Twitter here  for the latest news and technical articles on SharePoint.  I am also a Pluralsight author, and you can view all the courses I created on my author page.

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

Previous Post
Getting More Than 50 External Users With the Get-SPOExternalUser PowerSell cmdlet
Next Post
Bug in Get-SPOExternalUser PowerShell : Not all external users are returned

11 Comments

  • March 8, 2018 at 12:37 pm
    KT

    This only appears to return one new external user even when two were added on the previous day. When I modify the days to -14 it returns the second user from yesterday as well as some of the others within that 14 day period but not all.Shouldn’t it be returning all new external users?

    Reply
  • July 19, 2018 at 1:34 pm
    Joe Fischer

    Hi Vlad,
    I am very new to PowerShell and I must be doing something wrong or missing a first step.
    I dont see where/how you are connecting to SPO.

    I am trying to get your second script to work, but I keep getting the below error:

    Get-SPOSite : The term ‘Get-SPOSite’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path
    was included, verify that the path is correct and try again.
    At line:17 char:21
    + $SiteCollections = Get-SPOSite -Limit All
    + ~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Get-SPOSite:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    TIA,
    Joe

    Reply
  • July 20, 2018 at 11:03 am
    Joe Fischer

    Hi Vlad,
    Great article.
    Do you know if there is a way for me to get the items an exteranl user have rights to as well as the actual rights?

    Thanks,
    Joe

    Reply
    • July 22, 2018 at 1:02 pm

      You cannot do it using the Microsoft Provided module.. you should look in the PnP PowerShell module! I am on the road right now so can’t try stuff, but will try to look in more detail if there is anything we could do!

      Reply
  • December 4, 2018 at 8:40 pm
    Dean Gross

    Vlad, when I run this, I don’t get any data for the InvitedBy attribute and I don’t see this in the Get-SPOExternalUsre documentation at https://docs.microsoft.com/en-us/powershell/module/sharepoint-online/get-spoexternaluser?view=sharepoint-ps. Why did you include it? Has MS changed the cmdlet?

    Reply
  • April 30, 2019 at 9:48 pm
    John R.

    Hi Vlad,

    Thank you very much for this – it will be very useful.

    How can I also get the list of ‘pending’ external user invitations (ones still valid but not yet accepted by the invitee)?

    Reply
    • May 7, 2019 at 7:37 pm

      Really glad I could help! You could get it from the hidden lists using PnP … I don’t have them by heart right now and currently on a plane to Australia! Let me know if you don’t find the list and I will look more into it!

      Reply
  • April 1, 2020 at 12:45 pm
    Mark Rainbird

    I had the same trouble. All I had to do was declare the $ExternalUsers as an array before the loop which solved that problem.

    I also had an issue where the position could not be higher than the number returned by the query for the number of external users on a site so I had to get around that one too.

    In general though the script provided worked well and provided the information I needed.

    Reply
  • May 5, 2020 at 2:31 pm
    David Shumate

    Hi Vlad,

    Hope all is going well, always enjoy catching your work online when I am looking for a hard to find solution. I have been asked to find all external users who are pending. External users requests are cataloged in the Access Requests list, but no data on the state of the external users request is present that I can tell. Any ideas?

    Reply
    • May 7, 2020 at 10:23 am

      I am not infront of a work computer right now but each SharePoint site has a hidden list where thsoe are stored (you should be able to get them using the PnP PowerShell module). That’s the only way I am thinking right now of getting them! Hope this helps!

      Reply

Leave a Reply

15 49.0138 8.38624 1 0 4000 1 https://vladtalkstech.com 300 1