Creating script to export reports on users and their OneDrive for external sharing




I was wondering if anyone has an idea of how to make a script that allows me to see who are the members in an Azure AD Security Group and see if they have External Sharing Capabilities enabled or not.


So far I have this snippet that returns list of users in a designated security group:



Get-AzureADGroupMember -ObjectId "<Security Group ObjectId>"


Note: You have to run 




before running the "Get-AzureADGroupMember" command.


But it doesn't tell me the sharing options for those users.


However, if I use this returns all of the OneDrive in the tenant with owner and sharing capabilities. The thing is, I don't want to see all of them, just the ones that I move to the security group in AD.



Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like ''" | select Owner, Url, SharingCapability



 NOTE: Run 


Connect-SPOService -url <a href="<a href="" target="_blank"></a>" target="_blank"><a href="</a" target="_blank"></a</a>>


 before the "Get-SPOSite" command.


What I want at the end of it all is to have a list of users that are inside the security group and tell if they have external sharing capabilities or not. 

4 Replies

Simply get the list of members of the group and then run the Get-SpoSite cmdlet for each member by adjusting the filter. Here's how to do it for a given user:

Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Owner -eq '' -and Url -like ''" | select Owner, Url, SharingCapability
best response confirmed by Jonathan Nunez (Contributor)

@Jonathan Nunez 


Try the below script :

Connect-SPOService -url

$Result = @()
$GroupName = "YourSecurityGroup"
$GroupObj = Get-AzureADGroup -SearchString $GroupName
$GroupMembers = Get-AzureADGroupMember -ObjectId $GroupObj.ObjectId | Select DisplayName, UserPrincipalName

$OneDriveSites = Get-SPOSite -IncludePersonalSite $true -Limit all -Filter "Url -like ''" | Select Owner, Url, SharingCapability

ForEach ($User in $GroupMembers)
$Site = ($OneDriveSites | Where-Object { $_.Owner -eq $User.UserPrincipalName })

$Result += New-Object PSObject -property @{ 
UserName = $User.DisplayName
UserPrincipalName = $User.UserPrincipalName
SharingCapability = if ($Site -ne $null) { $Site.SharingCapability } else { $null }
URL = if ($Site  -ne $null) { $Site.Url } else { $null }

$Result | Select UserName, SharingCapability, URL 

@Kevin Morgan 


This worked great! 


It returns list of users within the security group and its sharing capabilities.


What I would like to know is if I can display the sharing activity as well. If anything, what kind of information can I extract from besides Sharing Capability, Owner and URL?

@Jonathan Nunez 


Not sure what kind of report you are expecting. You can get OneDrive Activity report (Includes Internally and Externally Shared File Count) using Microsoft Graph API. This API requires the permission "Reports.Read.All".


In this script I have used PnP Powershell module to acquire required access token. Before proceed you have to install SharePointPnPPowerShellOnline module.

Connect-PnPOnline -Scopes "Reports.Read.All"
$Accesstoken =Get-PnPAccessToken

$ApiUrl = "'D180')"
$Result = Invoke-RestMethod -Headers @{Authorization = "Bearer $Accesstoken"} -Uri $ApiUrl -Method Get
#Remove special chars from header
$Result = $Result.Replace('Report Refresh Date','Report Refresh Date')
#Convert the stream result to an array
$ResultArray = ConvertFrom-Csv -InputObject $Result
$ResultArray |  Select 'User Principal Name','Shared Internally File Count','Shared Externally File Count','Last Activity Date'

#Export result to CSV
$ResultArray | Export-Csv "C:\OneDriveActivity.csv" -NoTypeInformation


You can also refer @Vasil Michev 's useful posts :