After years of ignoring the issue, Microsoft has finally started rolling out the External sharing report feature for OneDrive for Business. The rollout is still not 100% complete, so the feature might not be available in your tenant just yet, but it should be coming soon.
Table of Contents
Generating a Sharing Report
To generate the External sharing report, open your OneDrive for Business site, go to Settings on top (cog wheel), OneDrive settings, More settings and finally under the Manage access section, click Run sharing report. You will then be asked to select where to store the report (Figure 1).
Figure 1: Generating a OneDrive for Business Sharing Report
After you select a folder and hit Save, the report is generated in a manner of a minute or two. You will be alerted by an email notification once the report is available, or you can look into the folder you selected for the output.
The report is a CSV file based on your Display name, followed by the date and time of its generation. The file is viewable in the browser or can be downloaded and opened with Excel. The latter option might be better for non-English users, as the columns and values of the generated CSV file will reflect the locale selected (in my case, Bulgarian), which resulted in an illegible mess because of the encoding, as shown in Figure 2.
Figure 2: Some encoding problems in a OneDrive for Business Sharing Report
Examining OneDrive for Business Sharing Data
Downloading the file and importing the data to an Excel worksheet, while simultaneously adjusting the encoding to UTF-8, produced a much more pleasant version (Figure 3). From left to right, you will see the Path to the item, its type, the permissions given, the user(s) which the item is shared with (one entry per line), user’s email where applicable, the User or Group type, Sharing Link ID, Sharing Link Type and AccessViaLinkID. Some of those fields might be empty, depending on the type of sharing, and the screenshot below only reflects External sharing (read below). Do note that the labels and values used are my own translation from the Bulgarian strings used in original, so there might be slight disconnect with what you see.
Figure 3: Contents of a OneDrive for Business Sharing Report
Despite what the feature name suggests, the report includes both internally and externally shared items, but more on that below. The items themselves are alphabetically sorted based on the full item’s path. As already mentioned above, each line represents a single permission entry, meaning you will see multiple entries for items that have more than one sharing link or direct permission, or any combination of those. Nested folders and items stored within them are covered, with some important omissions discussed below.
Comparing a Graph-Based Report
I took the liberty of comparing this report to the one generated with the Graph API based script I published over at Practical 365 a while back. Overall, you can expect to see very similar data, however there are some interesting differences. For example, the built-in report includes the default Web permissions, as well as permissions from other Lists/Libraries in your ODFB, while the script report focuses only on the default /Documents library. It’s also interesting to note that the Microsoft-generated report does not include information about permissions given to any secondary site collection owners, although they are readily available from the Graph endpoints.
Figure 4: A OneDrive for Business Sharing Report generated with the Microsoft Graph
The biggest difference between the two files is the sheer number of entries missing from the downloadable report. As an example, I sync the Camera roll from my mobile device to OneDrive for Business and have shared some of the images from OneDrive. This results in few hundred entries in the report just for the Photos folder, whilst the built-in report only lists a single entry for the folder. Trimming the entries makes sense, as all the items have the same set of permissions. However, the fact that trimming happens is not mentioned in the official documentation, so make sure to keep this aspect in mind when determining the actual number of shared items.
Similarly, there seems to be a bit of a gray area in the definition of internal vs external sharing. While the built-in report often seems to exclude entries that have additional permission entries that are considered internal only, it still lists other items even when they do not have any additional sharing links configured.
Administrative Challenges
Probably the major drawback for admins is the fact that there isn’t any easy way to run the report on behalf of a given user. Technically, you can add yourself as a secondary site collection admin for users’ ODFB drives, and you can then use those permissions to access the settings page of their sites and generate the report. However, this method is hardly manageable for anything but a handful of users.
Among other things worth mentioning is that the built-in report does not include information about link expiration, or additional link settings such as the Block download controls. Lastly, if you want to list all externally shared items, make sure to include the SharePointGroup value in addition to the External one when selecting a filer for the User or Group Type column. With all those adjustments in mind, the results from both files match perfectly, so whichever method you choose to use is entirely up to you.
Office 365 for IT Pros has lots of useful insight like this covering different aspects of the ecosystem. Our subscribers have the chance to download an updated book monthly. Shouldn’t you be one of them?
Maybe in a third-party reporting product. The Microsoft features seem to be very site-focused (for SharePoint Online) and user-focused (for OneDrive for Business).
Yes, use the script from the P365 article 🙂 Honestly, I’m not aware is Microsoft has any plans to make the built-in report easier to automate, I tried looking at any Graph endpoints corresponding to it and found nothing.
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
So, is it possible to get a report about all shared files in an org in one take?
Maybe in a third-party reporting product. The Microsoft features seem to be very site-focused (for SharePoint Online) and user-focused (for OneDrive for Business).
Yes, use the script from the P365 article 🙂 Honestly, I’m not aware is Microsoft has any plans to make the built-in report easier to automate, I tried looking at any Graph endpoints corresponding to it and found nothing.