How to report on documents ‘shared with guest’ or ‘shared with anonymous’

Office 365 with SharePoint, Teams and Onedrive is great for sharing information and documents. But this must be managed properly to maintain security.

And since Microsoft has made it easy for users to share documents, we are getting a lot of requests from administrators and security groups to pull a report on documents that are shared with anonymous links or with external/ guest users.

The good news is that any Office 365 administrator who has access to the Office 365 audit logs can pull a report for this. Here’s how:

Begin by either

• • • Opening the security and compliance center by using this link: https://protection.office.com
    • Or by opening https://portal.office.com and navigating to the Security and Compliance center.
      Once you are in the Security and Compliance admin center:

• Click Search
• Click Audit log search

In the audit log search page select:

• Sharing and access request activities

• Select a Start date and time and an End date and time for the required activities.

Note: The audit log is kept for 90 days with Office 365 E3, and for 1 year with Office 365 E5

• Click Search to run your search.

Once your search is completed:

• Click Export results
• Click Download all results to download the results in a CSV file

To get the report, you will need to manipulate the results from the CSV file.

Open Excel 2016 and

• Click the Data tab
• Click New Query/ From file
• Click From CSV and select the CSV file that you just downloaded
• Click Import
• Click Load data

You will find 4 columns:
CreationDate, UserIDs, Operations and AuditData.

• Click Edit at the bottom of the page.

Note that AuditData is a multi-property field. In the next step we will create a new column for each of these properties.

Select the AuditData column and

• Click Split Column (in the Home tab)
• Select By Delimiter
• Select Comma as delimiter
• Select At each occurrence of the delimiter

Click OK.

On the File tab

• Click Close & Load

This closes the Query Editor and opens the file in an Excel workbook

Once your search is completed:

• Click Export results
• Click Download all results to download the results in a CSV file

Depending on the number of ‘sharing’ events you have, you’re going to have multiple AuditData columns.

So the next step is to filter the file to only display the ‘sharing’ events that you’re looking for. sapio365 makes this a lot easier by displaying only the sharing status that is relevant in this case – even if content was shared beyond the limits of 90 days for Office 365 E3 and 1 year for Office 365 E5. All you need to do is:

Open sapio365 and connect with an Ultra Admin account.

• click ‘Show the complete list of users in your Tenant‘.

In the Job Center (left panel)

• click on Users with shared OneDrive Files…

Optional: You can specify the types of sharing that you are looking for (shared anonymously, with guests, etc…).

Tadah! You will see the results in a grid, which means you can also do things like filter, group by, create a chart, etc. You can also export if you want to use Excel.

Note: From sapio365 you can also edit and remove permissions to files.

The option to list files that are ‘shared with anonymous’ or ‘shared with guest’ is available for all OneDrive for Business and for all SharePoint Sites. Here is a link for more information about sapio365

Find all articles and tips for IT Professionnals working with Office 365 on Techlab.