Every day I like to browse the different Office 365 communities, and I often run into questions like “how do I list all externally shared files”. To date, Office 365 does not offer any functionality to answer this question. With the built-in tools, the closest you can get to an actual list is an exportable report, where you have to run an eDiscovery/Content search with the ViewableByExternalUsers keyword to download. This method, however, will not give you any information about the type of sharing (whether it’s a sharing invitation or anonymous link, for example), and more importantly, it will not give you details as to whom the file was shared with.
An alternative approach is to crawl the Office 365 Unified Audit log for any sharing activities. The additional data exposed in the events can reveal not just the address of the person a given file was shared with, but also whether the person has already accessed the file. However, as the audit logs only retain events for a limited duration, building a comprehensive inventory of all shared items using this method is impossible, unless you’re exporting and storing past events in an external system.
Many articles available online suggest using the Get-SPOExternalUser cmdlet as another alternative, but they clearly misunderstand the goal here. Some more elaborate solutions involve the use of SharePoint CSOM or the SharePoint REST API, but those will only appeal to people with extensive SharePoint dev background. For an IT Pro like myself, solutions based on these approaches can be a bit overwhelming to understand. Which is essential before running any code you’ve found online.
Can the Graph API help?
Enter the Graph API, offering a simple, unified model, where different objects, their attributes, and relations to other objects can be queried via straightforward HTTP URIs from a single endpoint. The Graph can be a very useful tool, even in the hands of a non-developer. More importantly, Graph calls can be easily made via PowerShell, and in turn, the output can be transformed into objects, requiring only basic programming experience to achieve the task at hand. Which is not to say that there isn’t a learning curve here, and before going over the solution we should introduce some basic concepts.
In a nutshell, to be able to execute Graph calls, you need to authenticate first, and you need to be authorized to perform the operations in question. Authentication happens by obtaining an Access token for a given application, which needs to be provisioned in the tenant beforehand. As part of the provisioning process, you need to detail the permissions that this app will get over different Graph resources. Those permissions, in turn, need to be authorized by the user running the app, or in some cases by a tenant administrator. The application can run in the context of a given user and will get access to whichever resources the user has access to, or run as a background service, without a user context. A more detailed description of all these concepts can be found in the official documentation, and this article is a good starting point.
For the task at hand, the approach we’ve chosen is to use the so-called application permissions model. To start with, we need to register the application, generate a secret/key and grant permissions for it to be able to enumerate items in all users’ OneDrive for Business sites. Once this is done, we can request an Access token, and use it to query the Graph. There are various methods to request a token, and if you don’t want to deal with all the little details it’s best to use the authentication libraries Microsoft has released. In my case, I’ve opted to use the ADAL binaries included with the latest Azure AD PowerShell module, which is where the $ADALPath variable points to. Make sure to change this variable to the correct path on your system. If needed, you can download the standalone ADAL binaries here.
Apart from pointing to the correct binaries, you will have to provide some additional details for the script to run as expected. As I mentioned above, we’ll need an app registered with Azure AD, and the identifier of this app needs to be populated in the $appID variable. To be able to request tokens for the app, we’ll also need the “client secret”, which you must populate in the $client_secret variable. The $tenantID variable should be populated with the ID of your tenant or your default onmicrosoft.com domain. The variables section of the script is located between lines 188-191.
We’ll also need a copy of the script, which you can get from the TechNet Gallery or GitHub. Lastly, we need to ensure the correct permissions are assigned to the app, otherwise, the token we obtain is good for nothing. Make sure to read the remaining sections of the article where all the different permissions/scopes are listed. Again, should you need help with any of these steps, refer to the official documentation and the article linked above.
Anatomy of the script
Once all variables are configured, you can run the script. It will try to obtain an access token for the given app and use it to get a list of all users in the tenant. To do this, it needs to have the corresponding permissions granted, at minimum User.Read.All. Unfortunately, there isn’t a way to fetch just the users with ODFB enabled, as the corresponding property (mySite) is only returned when a single user entry is requested. Therefore, the script will fetch all users in the company, then iterate over each user and query the /drive/root endpoint to check whether this user has an ODFB drive. If you want to consider an alternative approach, you can use the “OneDrive Activity details” report to fetch a list of active ODFB users, or just import the list form a CSV file.
After confirming that the user has an ODFB drive, the script will issue a call to fetch any “children” items within. Those include folders, files or notebooks, each of which is represented by a different Graph entity. Folders can contain additional items within them, including other folders. If you want to expand folders and check the sharing status of items, you can use the ExpandFolders switch parameter when invoking the script. One additional parameter, depth, can be used to specify the level up to which to expand folders. By default, only the top-most set of folders will be expanded, any subfolders and items within them will be returned as entries in the output, but no further expanding of subfolders will happen.
For each item found, the script will build an output object. It will use the Shared property (facet) as an indicator of whether the item has been shared with others, and if so, will call the /permissions BETA endpoint. The reason for using /beta instead of the /v1.0 endpoint is that the former returns a lot more details, which you can read about here. It’s also important to understand that the level of detail will depend on the permissions of the caller, in this case, our app. If you want to get all of the details, including sharing links and invitations, you should use the Sites.ReadWrite.All scope, although that requirement might be relaxed once the API graduates to GA status.
The Permissions string generated for each shared item will be composed of the following:
- For a sharing link: type of the link, its scope, list of users, expiration date, whether it’s a password-protected link and has the “block downloads” setting toggled
- For an invitation: the role granted and the email to which the invitation was sent
- For direct permissions: list of roles granted along with the email (or display name is no email) of the user
- For inherited permissions: the “grandfathered” path
- All of the above will be concatenated together in a comma-separated string (see screenshot below)
Next, the ExternallyShared property will give you a clue whether the item might be accessible from outside of the organization. To populate its value, we get the Permissions string and parse it to detect any anonymous links, as well as compare the list of users against the list of domains configured for the company. To obtain the latter, a call to the /domains endpoint is made, which requires the application to have the Directory.Read.All permissions granted. Alternatively, you can just manually populate the list (lines 207-209). Lastly, the ItemPath property is returned, referencing the URL you can use to open the item in a browser.
Sampling the output
By default, the script will return a filtered list of just the items that have been shared and will also store the output in a global variable called $varODFBSharedItems in case you want to reuse it. The unfiltered output will be saved to a CSV file, which you can then format, sort and filter as needed.
Here’s a sample screenshot with the output of the script, showing items that are being shared. The first highlighted item corresponds to a Word document that’s being shared via an “everyone” read-only link, and the link has been used by at least one internal user already. In addition, the option to Block downloads has been toggled for the sharing link, as indicated by the [BlockDownloads] string (the square brackets are intentionally added). Similarly, the second highlighted item shows a read-only guest link, this time with expiration configured. The last item shows direct permissions assigned to a Guest/external user in the tenant.
Of course, you are free to make any changes in the list of properties gathered for each item or the way they are presented in the final output.
Summary and closing remarks
In this article, we explored the viability of using the Graph API to generate a report of all ODFB shared files in the tenant. While creating this type of report requires some preparation, such as registering an Azure AD application and granting permissions, the overall process is relatively simple. The end result closely matches the data you can obtain from the ODFB UI, and you can use the generated report to quickly list all the items a given user has shared. Some added logic helps you filter out just the entries that are shared with external audiences.
Now, for some closing remarks. The script provided with this article should be considered a “proof of concept” and not a real, thoroughly tested and production-ready solution. While it did grow way beyond my initial intentions of providing just a few code snippets, there are still many improvements that could be made. It runs fine in a test tenant with just a handful of users and few GB total data, but if you run it against any regular-sized tenant it’s very likely that you will run into some throttling issues. Especially if you use the –ExpandFolders switch.
While Microsoft doesn’t publish exact data around the number of requests a given user or tenant is allowed per some specific duration of time, they do have general guidance published here. The script makes no attempt to capture HTTP status code 429 (Too many requests) replies but adds some artificial delay when fetching multiple pages and whenever a new user is being processed. Whether this will be enough for your scenario, I cannot tell.
One other thing worth mentioning is that the Graph API will return different details depending on the caller’s permissions. The examples included in this article are generated by using the Sites.ReadWrite.All permissions, which are the least restrictive ones. When making calls using such permissions, additional information about the shared item will be returned, such as the sharing link (shareId). This, in turn, can be used to accessing the shared content and redeeming the link. The format of the links, however, doesn’t reveal the path to the actual item being shared, similarly to how the webUrl property doesn’t reveal the path to an Office file but lists the corresponding .aspx ‘wrapper’ instead. This is a potential area for improvements of the script.
If you want to find out more about the top 7 PowerShell Scripts in Office 365, check out this guide by Vasil.
Vasil Michev is an Office Servers and Services MVP, specializing in Office 365. He’s currently employed as a Cloud Technical Consultant, and in his free time he can be found helping others in the Office 365 community.