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

Greetings,

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

Connect-AzureAD

before running the "Get-AzureADGroupMember" command.

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

However, if I use this snippet...it 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 '-my.sharepoint.com/personal/'" | select Owner, Url, SharingCapability

NOTE: Run

Connect-SPOService -url <a href="<a href="https://domain-admin.sharepoint.com" target="_blank">https://domain-admin.sharepoint.com</a>" target="_blank"><a href="https://domain-admin.sharepoint.com</a" target="_blank">https://domain-admin.sharepoint.com</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.

azure

Azure Active Directory

Try the below script :

Connect-AzureAD
Connect-SPOService -url https://domain-admin.sharepoint.com

$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 '-my.sharepoint.com/personal/'" | 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

Iron Contributor

Nov 19, 2019

Jonathan Nunez

Try the below script :

Connect-AzureAD
Connect-SPOService -url https://domain-admin.sharepoint.com

$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 '-my.sharepoint.com/personal/'" | 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

Jonathan Nunez
Brass Contributor
Nov 19, 2019
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?
- Kevin_Morgan
  Iron Contributor
  Nov 20, 2019
  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 = "https://graph.microsoft.com/v1.0/reports/getOneDriveActivityUserDetail(period='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 VasilMichev 's useful posts :
  https://practical365.com/clients/onedrive/reporting-on-onedrive-for-business-shared-files/
  https://gallery.technet.microsoft.com/OneDrive-for-Business-35e81b0b

VasilMichev
MVP
Nov 19, 2019
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 'vasil@michev.info' -and Url -like '-my.sharepoint.com/personal/'" | select Owner, Url, SharingCapability

Forum Discussion

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

Share

Resources