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

[Jonathan Nunez]

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. 

[#2]

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

[#3 · Accepted solution]

@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 

[#4]

@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?

[#5]

@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 @Vasil Michev 's useful posts :

https://practical365.com/clients/onedrive/reporting-on-onedrive-for-business-shared-files/

https://gallery.technet.microsoft.com/OneDrive-for-Business-35e81b0b