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

4 Replies

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

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

4 Replies

Resources