Post
Ask Question

How To Get External User Report In SharePoint Online Site Collection

Introduction

 
 
At some point, we may need to review how many external users are there in our Site Collection. Using the below script using powershell we can get the required details. Please note you can achieve this using either Global Admin Rights or SharePoint Admin Rights or having Site Collection Admin rights to the desired site.
 

Steps

 
Step 1
 
Import the required powershell module
 
Import-Module -Name PnP.PowerShell
 
Please note that PnP.PowerShell is the latest module where the PnP team is updating the command lets. The other PnP version SharePointPnPPowershellOnline is considered as legacy and no longer getting updated. You can go to references section to install the latest PnP.Powershell module 
 
Step 2
 
Initiate a variable $adminUrl where the tenant admin site url is stored.
 
The url will be in format like https://companydomain-admin.sharepoint.com where ‘companydomain’ is your company name provided by MSFT during the tenant setup.
 
$adminUrl = “https://companydomain-admin.sharepoint.com”
 
Step 3
 
Initiate a variable $siteUrl where the external user reports needs to be retrieved.
 
$siteUrl = “https://companydomain.sharepoint.com/sites/demosite” 
 
Step 4
 
connect to admin portal using Global Admin or SharePoint service admin rights.
 
Please note that in the below command it is encouraged to use -interactive parameter, as this forces to choose the account to login. This works well in MFAed (Multi-Factor Authenticated) accounts.
 
Connect-PnPOnline -Url $adminUrl -Interactive
 
You will be getting a pop-up to select the account to login.
 
How To Get External User Report in SharePoint Online Site Collection
 
Step 5
 
connect to the site using the same admin account. If you are site collection admin already to the site, you can directly connect to site using your account that has site collection admin rights and then get the users report. 
 
Connect-PnPOnline $siteUrl -Interactive 
 
Step 6
 
Run the PowerShell command
 
To get the external users in the specified site collection run the below PS command
 
Get-PnPExternalUser -SiteUrl $siteUrl
 
How To Get External User Report in SharePoint Online Site Collection
 
Step 7
 
Filter/modify the output to your needs 
 
If you want to get the results in a tabular format you can use following parameters
  • Position - To get from which record to display the result. Default will be 0.
  • PageSize - how many records to display per page
How To Get External User Report in SharePoint Online Site Collection
 
You can still apply filtering by using select and where conditions. We will look into these now.
 
You want to get completed details of external user run the below command.
 
Get-PnPExternalUser -SiteUrl $siteUrl | where {$_.AcceptedAs -eq "USEREMAIL"} | select *
 
Please note that external user email can be found by above steps. I have put * to get all the avaialble details. You can get specific details such as DisplayName, InivitedBy, UniqueID etc. Here I have used where to filter the result set for particular user and used ‘Select’ to get the properties of the user.
 
How To Get External User Report in SharePoint Online Site Collection
 
Complete Script (for Global Admin or SharePoint Admin rights)
 
  1. #step1: Import Pnp.PowershellModule  
  2. Import-Module -Name PnP.PowerShell  
  3. #step2: Initialize varaibles with values. Replace adminUrl and siteUrl values with your tenant and site values respectively  
  4. $adminUrl= "https://yourcompany-admin.sharepoint.com"  
  5. $siteUrl="https://yourcompany.sharepoint.com/teams/SiteName"  
  6. #step3: Connect to SharePoint admin site using SharePoint or global admin rights  
  7. Connect-PnPOnline -Url $adminUrl -Interactive  
  8. #step4: Connect to SharePoint site where the external user reports needs to be extracted  
  9. Connect-PnPOnline $siteUrl -Interactive  
  10. #step5: Get the report  
  11. Get-PnPExternalUser -Position 0  -siteurl $siteUrl -PageSize 10   
Complete Script (for site collection Admin)
 
you do not need to connect to admin portal and you can directly connect to the site collection. Below is the complete script for that. Please replace values in $siteUrl and $outputFile to your needs.
 
  1. #step1: Import the PnP Powershell Module.   
  2. Import-Module -Name PnP.PowerShell  
  3. #step2: Define the siteUrl where you need to connect. Please note you need to have site collection admin rights for this site. Also define path embedded with current date and time to get   
  4. #unique ID and append to file  
  5. $siteUrl="https://yourcompany.sharepoint.com/sites/sitename"  
  6. $currentTime = $(Get-Date).ToString("yyyymmddhhmmss");    
  7. $outputFile="C:\temp\ExternalUsersInfo-"+$currentTime+".csv";  
  8. #step3: Connect to SharePoint site where the external user reports needs to be extracted  
  9. Connect-PnPOnline $siteUrl -Interactive  
  10. #step4: Get the report  
  11. Get-PnPExternalUser -Position 0  -siteurl $siteUrl -PageSize 10 | Export-Csv -Path $outputFile  
Outputs
 
How To Get External User Report in SharePoint Online Site Collection
 
 
Update: Recently came to know that Get-PnPExternalUser / Get-SPOExternalUser has a limitation of 50 items which means they can get upto 50 items only. To over come this limitation, i have used get-pnpuser command and then filttered the users if the account name contains 'EXT' or 'Guest'. Below is the line of script for the same. 
$ExternalUsers = Get-PnPUser -Connection $CurrentConnection | Where{$_.LoginName -like "*#ext#*" -or $_.LoginName -like "*urn:spo:guest*"}

The complete script for the users more than 50 external users. Don't forget to change the '$SiteUrl' variable at step2. 

#step1: Import the PnP Powershell Module. 
Import-Module -Name PnP.PowerShell
#step2: Define the siteUrl where you need to connect. Please note you need to have site collection admin rights for this site. Also define path embedded with current date and time to get 
#unique ID and append to file
$siteUrl="https://contoso.sharepoint.com/teams/MyTeam"
$currentTime = $(Get-Date).ToString("yyyymmddhhmmss");  
$outputFile="C:\temp\ExternalUsersInfo-"+$currentTime+".csv";
Add-Content -Path $outputFile -Value "Display Name, Email, UserID, AcceptedAsEmail, InvitedBy, CreatedDate";
#step3: Connect to SharePoint site where the external user reports needs to be extracted
$CurrentConnection = Connect-PnPOnline $siteUrl -Interactive
#step3 Get all external users
$ExternalUsers = Get-PnPUser -Connection $CurrentConnection | Where{$_.LoginName -like "*#ext#*" -or $_.LoginName -like "*urn:spo:guest*"} 
if($ExternalUsers.count -gt 0){
    Write-Host "`tFound '$($ExternalUsers.count)' External users" -f Green
    $ExternalUsers | Export-Csv -Path $outputFile
   
}else{
   Write-Host "No External Users found" -f Yellow
}

 

Conclusion

 
Thus in this blog we have seen
  • How to connect to Admin Portal using latest PnP Powershell?
  • How to connect to SharePoint online site collection?
  • How to get the external user report?
  • How to modify/filter the desired output? 
References
  • https://docs.microsoft.com/en-us/powershell/module/sharepoint-pnp/get-pnpexternaluser?view=sharepoint-ps
  • https://docs.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?view=sharepoint-ps
  • https://pnp.github.io/powershell/articles/authentication.html
  • https://www.sharepointdiary.com/2017/11/sharepoint-online-find-all-external-users-using-powershell.html


Similar Articles