Forum Discussion

Brass Contributor

Nov 18, 2019

Solved

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...

azure

azure active directory

Nov 19, 2019

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 https://docs.microsoft.com/en-us/graph/api/reportroot-getonedriveactivityuserdetail?view=graph-rest-1.0&tabs=http 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