Forum Discussion
Jonathan Nunez
Nov 18, 2019Brass Contributor
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...
- 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
Jonathan Nunez
Nov 19, 2019Brass Contributor
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
Nov 20, 2019Iron Contributor
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