SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1015688%22%20slang%3D%22en-US%22%3ECreating%20script%20to%20export%20reports%20on%20users%20and%20their%20OneDrive%20for%20external%20sharing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015688%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20anyone%20has%20an%20idea%20of%20how%20to%20make%20a%20script%20that%20allows%20me%20to%20see%20who%20are%20the%20members%20in%20an%20Azure%20AD%20Security%20Group%20and%20see%20if%20they%20have%20External%20Sharing%20Capabilities%20enabled%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20have%20this%20snippet%20that%20returns%20list%20of%20users%20in%20a%20designated%20security%20group%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EGet-AzureADGroupMember%20-ObjectId%20%22%3CSECURITY%20group%3D%22%22%20objectid%3D%22%22%3E%22%3C%2FSECURITY%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%20You%20have%20to%20run%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EConnect-AzureAD%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebefore%20running%20the%20%22Get-AzureADGroupMember%22%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20doesn't%20tell%20me%20the%20sharing%20options%20for%20those%20users.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20I%20use%20this%20snippet...it%20returns%20all%20of%20the%20OneDrive%20in%20the%20tenant%20with%20owner%20and%20sharing%20capabilities.%20The%20thing%20is%2C%20I%20don't%20want%20to%20see%20all%20of%20them%2C%20just%20the%20ones%20that%20I%20move%20to%20the%20security%20group%20in%20AD.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EGet-SPOSite%20-IncludePersonalSite%20%24true%20-Limit%20all%20-Filter%20%22Url%20-like%20'-my.sharepoint.com%2Fpersonal%2F'%22%20%7C%20select%20Owner%2C%20Url%2C%20SharingCapability%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSTRONG%3ENOTE%3A%3C%2FSTRONG%3E%20Run%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EConnect-SPOService%20-url%20%3CA%20href%3D%22%26lt%3Ba%20href%3D%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Fdomain-admin.sharepoint.com%3C%2FA%3E%22%20target%3D%22_blank%22%26gt%3B%3CA%20href%3D%22https%3A%2F%2Fdomain-admin.sharepoint.com%26lt%3B%2Fa%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdomain-admin.sharepoint.com%3C%2FA%3E%26gt%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3Bbefore%20the%20%22Get-SPOSite%22%20command.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20at%20the%20end%20of%20it%20all%20is%20to%20have%20a%20list%20of%20users%20that%20are%20inside%20the%20security%20group%20and%20tell%20if%20they%20have%20external%20sharing%20capabilities%20or%20not.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1015688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Active%20Directory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016583%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20script%20to%20export%20reports%20on%20users%20and%20their%20OneDrive%20for%20external%20sharing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016583%22%20slang%3D%22en-US%22%3E%3CP%3ESimply%20get%20the%20list%20of%20members%20of%20the%20group%20and%20then%20run%20the%20Get-SpoSite%20cmdlet%20for%20each%20member%20by%20adjusting%20the%20filter.%20Here's%20how%20to%20do%20it%20for%20a%20given%20user%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%20%0AGet-SPOSite%20-IncludePersonalSite%20%24true%20-Limit%20all%20-Filter%20%22Owner%20-eq%20'vasil%40michev.info'%20-and%20Url%20-like%20'-my.sharepoint.com%2Fpersonal%2F'%22%20%7C%20select%20Owner%2C%20Url%2C%20SharingCapability%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017132%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20script%20to%20export%20reports%20on%20users%20and%20their%20OneDrive%20for%20external%20sharing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53673%22%20target%3D%22_blank%22%3E%40Jonathan%20Nunez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20the%20below%20script%20%3A%3C%2FP%3E%3CPRE%3EConnect-AzureAD%0AConnect-SPOService%20-url%20https%3A%2F%2Fdomain-admin.sharepoint.com%0A%0A%24Result%20%3D%20%40()%0A%24GroupName%20%3D%20%22YourSecurityGroup%22%0A%24GroupObj%20%3D%20Get-AzureADGroup%20-SearchString%20%24GroupName%0A%24GroupMembers%20%3D%20Get-AzureADGroupMember%20-ObjectId%20%24GroupObj.ObjectId%20%7C%20Select%20DisplayName%2C%20UserPrincipalName%0A%0A%24OneDriveSites%20%3D%20Get-SPOSite%20-IncludePersonalSite%20%24true%20-Limit%20all%20-Filter%20%22Url%20-like%20'-my.sharepoint.com%2Fpersonal%2F'%22%20%7C%20Select%20Owner%2C%20Url%2C%20SharingCapability%0A%0AForEach%20(%24User%20in%20%24GroupMembers)%0A%7B%0A%24Site%20%3D%20(%24OneDriveSites%20%7C%20Where-Object%20%7B%20%24_.Owner%20-eq%20%24User.UserPrincipalName%20%7D)%0A%0A%24Result%20%2B%3D%20New-Object%20PSObject%20-property%20%40%7B%20%0AUserName%20%3D%20%24User.DisplayName%0AUserPrincipalName%20%3D%20%24User.UserPrincipalName%0ASharingCapability%20%3D%20if%20(%24Site%20-ne%20%24null)%20%7B%20%24Site.SharingCapability%20%7D%20else%20%7B%20%24null%20%7D%0AURL%20%3D%20if%20(%24Site%20%20-ne%20%24null)%20%7B%20%24Site.Url%20%7D%20else%20%7B%20%24null%20%7D%0A%7D%0A%7D%0A%0A%24Result%20%7C%20Select%20UserName%2C%20SharingCapability%2C%20URL%20%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017615%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20script%20to%20export%20reports%20on%20users%20and%20their%20OneDrive%20for%20external%20sharing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F38365%22%20target%3D%22_blank%22%3E%40Kevin%20Morgan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20worked%20great!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20returns%20list%20of%20users%20within%20the%20security%20group%20and%20its%20sharing%20capabilities.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20know%20is%20if%20I%20can%20display%20the%20sharing%20activity%20as%20well.%20If%20anything%2C%20what%20kind%20of%20information%20can%20I%20extract%20from%20besides%20Sharing%20Capability%2C%20Owner%20and%20URL%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1020136%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20script%20to%20export%20reports%20on%20users%20and%20their%20OneDrive%20for%20external%20sharing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1020136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53673%22%20target%3D%22_blank%22%3E%40Jonathan%20Nunez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20what%20kind%20of%20report%20you%20are%20expecting.%20You%20can%20get%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fgraph%2Fapi%2Freportroot-getonedriveactivityuserdetail%3Fview%3Dgraph-rest-1.0%26amp%3Btabs%3Dhttp%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EOneDrive%20Activity%3C%2FA%3E%20report%20(Includes%20Internally%20and%20Externally%20Shared%20File%20Count)%20using%20Microsoft%20Graph%20API.%20This%20API%20requires%20the%20permission%20%22Reports.Read.All%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20script%20I%20have%20used%20PnP%20Powershell%20module%20to%20acquire%20required%20access%20token.%20Before%20proceed%20you%20have%20to%20install%20SharePointPnPPowerShellOnline%20module.%3C%2FP%3E%3CPRE%3EConnect-PnPOnline%20-Scopes%20%22Reports.Read.All%22%0A%24Accesstoken%20%3DGet-PnPAccessToken%0A%0A%24ApiUrl%20%3D%20%22https%3A%2F%2Fgraph.microsoft.com%2Fv1.0%2Freports%2FgetOneDriveActivityUserDetail(period%3D'D180')%22%0A%24Result%20%3D%20Invoke-RestMethod%20-Headers%20%40%7BAuthorization%20%3D%20%22Bearer%20%24Accesstoken%22%7D%20-Uri%20%24ApiUrl%20-Method%20Get%0A%23Remove%20special%20chars%20from%20header%0A%24Result%20%3D%20%24Result.Replace('%C3%AF%C2%BB%C2%BFReport%20Refresh%20Date'%2C'Report%20Refresh%20Date')%0A%23Convert%20the%20stream%20result%20to%20an%20array%0A%24ResultArray%20%3D%20ConvertFrom-Csv%20-InputObject%20%24Result%0A%24ResultArray%20%7C%20%20Select%20'User%20Principal%20Name'%2C'Shared%20Internally%20File%20Count'%2C'Shared%20Externally%20File%20Count'%2C'Last%20Activity%20Date'%0A%0A%23Export%20result%20to%20CSV%0A%24ResultArray%20%7C%20Export-Csv%20%22C%3A%5COneDriveActivity.csv%22%20-NoTypeInformation%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20refer%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F58%22%20target%3D%22_blank%22%3E%40Vasil%20Michev%3C%2FA%3E%26nbsp%3B's%20useful%20posts%20%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fpractical365.com%2Fclients%2Fonedrive%2Freporting-on-onedrive-for-business-shared-files%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fpractical365.com%2Fclients%2Fonedrive%2Freporting-on-onedrive-for-business-shared-files%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgallery.technet.microsoft.com%2FOneDrive-for-Business-35e81b0b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgallery.technet.microsoft.com%2FOneDrive-for-Business-35e81b0b%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Jonathan Nunez
Contributor

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. 

4 Replies
Highlighted

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

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

Highlighted

@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

Related Conversations
Report for Videoconferencing quality
Thomas_Steibl in Microsoft Teams on
3 Replies
Suggestion - Teams Screen Sharing
dumut in Microsoft Teams on
4 Replies
Azure Reporting
Christian Taveras in Azure on
1 Replies
Intune Scripting
jesusleon in Microsoft Intune on
2 Replies