Reporting on OneDrive for Business shared files

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

Reporting on OneDrive for Business Shared Files

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.

About the Author

Vasil Michev

Vasil Michev is an Office Servers and Services MVP, specializing in Office 365. He's currently employed as a Technical Product Manager, and in his free time he can be found helping others in the Office 365 community.


  1. Ryan

    Im stuck with (403) forbidden, Ive checked all the permission requirements for Graph. Not sure what I am missing

    VERBOSE: POST with -1-byte
    VERBOSE: received 1577-byte response of content type application/json; charset=utf-8
    VERBOSE: Successfully acquired Access Token
    VERBOSE: GET with 0-byte payload
    Invoke-WebRequest : The remote server returned an error: (403) Forbidden.
    At C:\Temp\Graph_ODFB_shared_files.ps1:223 char:21
    + … { $result = Invoke-WebRequest -Headers $AuthHeader -Uri $uri -Verbose …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebExc
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

  2. Shaun

    Hi Vasil

    First of all thankyou so much for offering your script. This has greatly helped me in updating my own internal systems. I had written a script for SharePoint using pnponline to gather all sharing files. However this now takes 8 or 9 hours to run as our company has got bigger. This doesn’t even include look at users OneDrive accounts which i am now investigating using your script.

    I have quite heavily modified it for my own purposes and it works great for users OneDrive accounts. However when running “$($driveID)/drive/items/$($” to process all the files in a folder of a SharePoint site the $file.shared property is missing on some files which do have sharing properties. If i manually run “$($driveID)/drive/items/$($itemID)/permissions” on the files that are missing the share property from the initial folder search it does pull back the required shared properties. However i can’t be running this manually against all files due to the time / throttling isses.

    Have you come accross this at all as its kind of a deal breaker if i can’t trust graph to pull back the Shared property. I would say it happens on 5% of files, and there is nothing obvious as to why it won’t say its shared.

    Kind Regards


  3. Adam


    Thanks so much for developing this, it’s exactly what I need. However, I’m having issues getting it to connect. I’m getting the same “Insufficient privileges” error some other comments have mentioned and I made whatever changes were recommended with no luck. This script is pretty over my head in terms of my own PS experience and troubleshooting it is proving challenging on my own. Any additional help would be greatly appreciated.


  4. Raphael N.

    Hi Vasil,
    thank you for this guide and script. Is it possible to authenticate with a certificate (as service principal) to the tenant instead of the App client Secret? Most other connections are initalized by using a certificate. So we could use these existing Service Principal to get the One Drive Report, too


    1. Vasil.Michev

      The auth method doesnt really matter, as long as you can obtain a token successfully. Replace the relevant section of the script with your own auth code. I used app secret as it’s easier for demos, in production you should be using that.

  5. Guca

    I obtain an empty CSV.
    After having a look at all the steps, it appears that I have no column “@odata.nextLink” in the processChildren answers.

    #fetch children, make sure to handle multiple pages
    do {
    $result = Invoke-GraphApiRequest -Uri “$URI” -Verbose:$VerbosePreference
    $URI = $result.’@odata.nextLink’
    #If we are getting multiple pages, add some delay to avoid throttling
    Start-Sleep -Milliseconds 500
    $children += $result

    PS C:\Scripts> $result = Invoke-GraphApiRequest -Uri “$URI” -Verbose:$VerbosePreference
    PS C:\Scripts> $result
    @odata.context value
    ————– —–$metadata#users('XXXXXX-XXXX-XXXXX-XXX-XXXXXXXXX‘)/drive/root/children {}

    Do you have any idea of what’s wrong in my case please ?

    Thank you, best regards,


    1. Vasil.Michev

      @odata.nextLink will be present only if you have more than 100 entries. Check your permissions.

      1. Guca

        Thanks for your answer.
        My permissions are :

        Azure Active Directory Graph (2)
        Directory.AccessAsUser.All / Delegated
        Directory.ReadWrite.All / Delegated / Yes / Not granted for xxxx

        Microsoft Graph (3)
        Directory.ReadWrite.All / Application / Yes / Granted for xxxx
        Reports.Read.All / Application / Yes / Granted for xxxx
        User.ReadWrite.All / Application / Yes / Granted for xxxx

        SharePoint (1)
        Sites.FullControl.All / Application / Yes / Granted for xxxx

        Does the 2nd one matter ? “Not granted for xxxx”
        Or it is replaced by the Microsoft Graph one ?

        Thank you

        1. Vasil.Michev

          The script uses the Graph API, not the SharePoint one, so you need to add the Sites.ReadWrite.All scope. Under Graph, not SPO.

          1. Guca

            Ok it worked great with the Graph API permission, sorry for my mistake.
            Do you know if there is a similar script to scan the SPO shares ?
            Thank you

  6. Chris D

    What is causing an error like this (running Transcript when PowerShell starts shows this error)

    TerminatingError(Invoke-WebRequest): “{ “error”: { “code”: “ResourceNotFound”, “message”: “User’s mysite not found.”, “innerError”: { “date”: “2020-07-02T13:42:09”, “request-id”: “95c12220-54c3-43a1-9900-f644008823b0″ } } }”

    $uri = “$`select=displayName,mail,userPrincipalName,id,userType&`$top=999&`$filter=userType eq ‘Member’ and startswith(mail, ‘a’)”

    1. Chris D

      Never mind found my answer in a thread below.

  7. sebus

    Input from csv would be nice indeed

    1. sebus

      For now I am doing “ugly”:
      [CmdletBinding()] #Make sure we can use -Verbose
      foreach ($user in $GraphUsers)
      if ($user.mail -eq $UPN)
      else {
      write ‘This user was not processed’

  8. sebus

    Not getting anywhere with it

    VERBOSE: Successfully acquired Access Token, valid until 06/16/2020 20:08:15
    VERBOSE: GET$select=displayName,mail,userPrincipalName,id,userType&$top=999&$filter=userType
    eq ‘Member’ with 0-byte payload
    Invoke-WebRequest : { “error”: { “code”: “Authorization_RequestDenied”, “message”: “Insufficient privileges to
    complete the operation.”, “innerError”: { “date”: “2020-06-16T19:36:31”, “request-id”:
    “9e881cf0-ccc5-4754-bb97-68ce1b18f93d” } } }
    At C:\PSScripts\Graph_ODFB_shared_files.ps1:205 char:21

    1. Vasil.Michev

      “Insufficient privileges to complete the operation” looks pretty clear to me – make sure the app has all the needed permissions, as detailed in the article above.

      1. sebus

        Indeed, privileges must be to the App (and not Delegated) – kind of obvious, but not specified. Exactly where I went wrong. Once added to the App, all works nicely

  9. sebus

    Any idea why it fails:

    VERBOSE: Successfully acquired Access Token, valid until 06/16/2020 20:08:15
    VERBOSE: GET$select=displayName,mail,userPrincipalName,id,userType&$top=999&$filter=userType
    eq ‘Member’ with 0-byte payload
    Invoke-WebRequest : { “error”: { “code”: “Authorization_RequestDenied”, “message”: “Insufficient privileges to
    complete the operation.”, “innerError”: { “date”: “2020-06-16T19:08:16”, “request-id”:
    “e55dbfce-82dc-458f-a79a-d40b1a9a00db” } } }
    At C:\PSScripts\Graph_ODFB_shared_files.ps1:205 char:21
    + … { $result = Invoke-WebRequest -Headers $AuthHeader -Uri $uri -Verbose …

    PS C:\PSScripts> Write-Host $authenticationResult

  10. Mat B.

    I managed to run this script and verbose output is showing processing but final variable is empty hence the CSV file is empty. Here is sample of the output:

    VERBOSE: Successfully acquired Access Token, valid until 05/18/2020 18:12:24
    VERBOSE: Processing user
    VERBOSE: GET with 0-byte payload
    VERBOSE: received 646-byte response of content type application/json;odata.metadata=minimal;odata.streaming=true;IEEE754Compatible=false;charset=utf-8
    VERBOSE: GET with 0-byte payload
    VERBOSE: received 140-byte response of content type application/json;odata.metadata=minimal;odata.streaming=true;IEEE754Compatible=false;charset=utf-8
    VERBOSE: Processing user
    VERBOSE: GET with 0-byte payload
    VERBOSE: received 649-byte response of content type application/json;odata.metadata=minimal;odata.streaming=true;IEEE754Compatible=false;charset=utf-8

    This goes one for several hundred users. Some have OneDrive accounts and some do not but I have setup shares on few myself so I’m pretty sure this should output some data.

    Any help would be appriciated.

      1. Jay B.

        Thanks for the great script Vasil. I’m having the problem above. I upped my depth to 3, but I still get a blank report. Any ideas would be super helpful. Thanks.

        1. Vasil.Michev

          Hard to guess without knowing how you run the script. For starters, check the app permissions, make sure the token has all the relevant scopes, etc.

  11. Jai Chanani

    Hi Vasil,

    Great article and script!

    Very close to what I was looking for. Is there a way to limit the files to specific type of files and three days or newer?


    1. Vasil.Michev

      Just filter it in Excel? There is no date field exposed though, so that part is not possible.

  12. Justin Halbmann

    I’m curious if this thread is still looked at. I attempted to run this today but seems the permissions are not quite right. I keep getting the following error:

    VERBOSE: Successfully acquired Access Token, valid until 01/28/2020 21:50:34
    VERBOSE: GET with 0-byte payload
    Invoke-WebRequest : {
    “error”: {
    “code”: “Authorization_RequestDenied”,
    “message”: “Insufficient privileges to complete the operation.”,
    “innerError”: {
    “request-id”: “9a5f78d9-a254-46b9-8b79-1599f12d5892”,
    “date”: “2020-01-28T21:28:13”
    At C:\Users\USERNAME\Downloads\Graph_ODFB_shared_files.ps1:205 char:21
    + … { $result = Invoke-WebRequest -Headers $AuthHeader -Uri $uri -Verbose …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebExc
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

    1. Vasil.Michev

      Check your token, you can copy/paste it out of the $authenticationResult or $authheader variable and use a tool such as to parse it. The /domains call in particular requires Directory.Read.All permissions, you can get around it by simply hardcoding the list of domains you want to check against (line 271).

    1. Vasil.Michev

      There’s no error here, it simply skipped the corresponding user. In any case, Microsoft released built-in reports now, try them out.

      1. Sal

        Hi, wondering about where to find the built-in reports you mentioned.

          1. Tsullyman

            These are only useful for a single user – How can you report on all users via builtin tools??

  13. Ken

    Where can I download the script. I don’t see any links.

    1. Vasil.Michev

      Now now Ken, did you read the entire article? Be honest 🙂

  14. Raphael N.

    Hey Vasil,

    Thanks for the guide! I have some Issues with the ‘Invoke-GraphApiRequest’ function in line 206 of the script (try { $result = Invoke-WebRequest -Headers $AuthHeader -Uri $uri -Verbose:$VerbosePreference -ErrorAction Stop }):

    I created an Azure Application and set the access rights as required. I also filled the variables with the App ID etc.
    When I run the script, I got an error: ‘Invoke-WebRequest : the remotehost returned an error: (403) forbidden’

    Can you help me? Thanks
    Kind regards

    1. Vasil.Michev

      You probably havent granted all the required permissions, or consented to them, or refreshed the token after granting them. Try running the script with the -Verbose switch, it will show you which exact query was attempted so you can adjust the permissions accordingly. You can also get the access token out of the $authenticationResult global variable and run it against a parser ( to confirm the permissions are correctly represented.

      1. Raphael N.

        VERBOSE: Successfully acquired Access Token, valid until 10/01/2019 07:23:42
        VERBOSE: GET with 0-byte payload
        ‘Invoke-WebRequest : the remotehost returned an error: (403) forbidden’

        Result : Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationResult
        Id : 23308
        Exception :
        Status : RanToCompletion
        IsCanceled : False
        IsCompleted : True
        CreationOptions : None
        AsyncState :
        IsFaulted : False
        AsyncWaitHandle : System.Threading.ManualResetEvent
        CompletedSynchronously : False


        In Azure, I set the application access right for “Sites.ReadWrite.All” (status is granted), and created a secret client key, which is filled to the $client_secret variable.

        Here an extract from jwt.msc:

        “typ”: “JWT”,
        “roles”: [

      2. Raphael N.

        Oh, I saw the problem after clicking at “Post Comment” 😀
        There are a few more access rights which you have to grant. I added Sites.ReadWrite.All and Directory.Read.All. Now it works fine. Thanks a lot!
        At the Variable $appID Comment, only Sites.ReadWrite.All were listed.

  15. VValentin

    Hela Vasil,
    Sound a great script but I have and issue with it.
    I have arround 15 000 Onedrive to check. It start without issue but after around 1H and 2000 accounts analysed

    I have error line 28 saying “Access token has expired”. How to solve it?

    By the way it seems that all guest accounts are analyzed as I see des #EXT#. Should be removed as there in no linked Onedrive

    1. Vasil.Michev

      Sounds about right, as the Access token validity is one hour and the script makes no attempt to refresh it 🙂 As I mentioned in the article, the script is more of a “proof of concept” and not something you should expect to work flawlessly in every environment. I certainly didnt test it about that big amount of users, I terminated all my tests at around 10000 files total. So in a word – either split the execution of the script to smaller groups of users, or add code to handle the token renewal.

      As for external users, that’s because of the method used to get a list of drives. The Graph doesnt have any method to list just the users with OneDrive enabled, so instead we get a list of all users (Guest included), then for every user it checks for the presence of a OneDrive for Business drive. But you are right, I should’ve filtered out just “regular” users, which is done easily by changing the query to:$select=displayName,mail,userPrincipalName,id,userType&$top=999&$filter=userType eq ‘Member’

  16. David A.

    It works pretty well.
    For production tenants with large amount of data. Is there a way we can specify only 1 user? It would be great to check quickly the files for a particular user

    1. David A.

      An easy workaround is adding a if statment in the foreach loop, like
      if ($user.mail -eq “********”)

      1. J.Christophe Q.

        You can also add a parameter (ie -UserPrincipalName) and build the request if it is used.
        If present request can be the following
        “$select=displayName,mail,userPrincipalName,id”. If not, request all users.

    2. Vasil.Michev

      I see you already found a way 🙂

      Another approach would be to make the script accept input from CSV file, and process only the corresponding users. But there’s no way for me to address all different scenarios/requirements. The code is available and can be reused freely, so just make any changes you need.

      1. David A.

        Thanks for both ideas! They are really useful!

  17. Oleg

    This is a good workaround solution. But i hope Microsoft introduces some reporting option at some point.

Leave a Reply