Auditing Your OneDrive for Business Sharing Using Power Platform
Sharing content in Microsoft 365 is easy due to many advancements in this platform. However, for a company to ensure that its content is safely shared, auditing the sharing of content periodically is important. In this article, I’m discussing how to develop a Power Platform solution for auditing an individual’s OneDrive for Business sites.
Sharing content in Microsoft 365
In today’s corporate environment, sharing content with peers and external parties has become the norm. While we still see old habits of sending files as attachments linger around, sharing files via links is becoming easier than ever in Microsoft 365 – whether sending the file via Outlook or selecting to share it directly from a synced location, SharePoint, Teams or OneDrive for Business.
Yet with the ease of sharing comes the risk of oversharing. Without proper governance in place, content may be shared after the initial need has ended. This could expose the company to potential situations where employees are accessing information that they should not be privy to. Worse, companies may be subject to data loss if such content is shared externally in an uncontrolled fashion. One way to reduce these risks is to periodically audit how content is being shared by employees.
Auditing OneDrive for Business
Performing a OneDrive for Business audit requires scanning all files and folders stored within a user’s personal OneDrive for Business site and reporting back the findings to a central location, such as a SharePoint list or mailbox.
A company can use the information gathered to track how corporate content is shared over time and identify patterns of sharing to answer questions such as:
- Is the content being shared accessible to individuals outside the organizations?
- How many individuals can access specific content?
Power Platform OneDrive for Business audit solution
Such an audit solution lends itself well to be developed using the Microsoft Power Platform. Depending on the needs and licensing options by an organization, it can be developed as a stand-alone Power App application or reside inside of a Team. Regardless of the option an organization chooses, there are three key components that can be used in the Power Platform to develop such a solution:
- Power Automate Flow to get an inventory of the files.
- Power Automate Flow to load audit results into Power Apps.
- Power App for user to interact with their shared content.
Get inventory flow
For the audit to take place, the first step is to collect the list of files and folders that are shared with other users. OneDrive for Business is built on top of SharePoint Online. Therefore, it is no surprise that some solutions will leverage a mix of OneDrive for Business and SharePoint Online actions to work. The overall flow is broken down into the following steps:
- Initiate the workflow from a Power App, get user context and initiate file list array.
- Get list of files.
- For each file, check if it’s shared or not.
- If yes, get the email of each user the file/folder is shared with.
- Save results to a file.
- Notify user that audit results are ready.
Flow initiation
The flow begins with a call from the Power App to collect the file sharing information using a button or when the app is launched. The first step when the flow runs is to determine the user’s OneDrive for Business URL using the Office 365 connector’s Get my profile (V2) action.
Retrieving list inventory
To easily get a list of all files and folders within the user’s OneDrive for Business site, the SharePoint Get files action can be used as shown in Figure 3. Unlike the OneDrive for Business actions that return a list of files in a folder, the Get files action from SharePoint returns all files within the selected library. For the Get files action to query the correct site, the Office 365 connector was used with the dynamic property My Site to get the OneDrive for Business URL for the user who is calling the flow.
While the description in the Top Count property suggests that no value will return all items, it limits the number of items returned to the first 100. It is possible to increase Top Count to 5,000, which is the upper limit that the Get files action will permit. However, that may still not be high enough as users may have more files. To further increase the limit, leave the Top Count blank and add pagination with a threshold limit of 100,000. This is done by selecting the ellipses (…) in the top-right corner of the Get files action and selecting Settings as shown in Figure 4.
Check if a file or folder has been shared
Once the file list has been retrieved, the flow needs to access each item in the hierarchy and check if it has been explicitly shared with any users. A for loop is used with the output of the Get files action to iterate through the list of files as shown in Figure 5. Again, the SharePoint connector can be used with the Send HTTP Request to check for this. In this sample, the action has been renamed to Check file for unique permissions to make it more meaningful for the reader.
The critical part is the text in the Uri property, which queries the item for unique access. The ID value is referring to the file identifier coming from the Get files action. If the file is shared, the body(Check_file_for_unique_permissions)?[‘d’]?[‘HasUniqueRoleAssignments’] property is true and can be used in a conditional check a shown in Figure 7.
From that point on, if any file is found that has unique permissions, the list of users is captured in a Select statement and a new record is added to the array variable declared at the beginning of the flow that includes the name, path and list of users with whom it is shared are saved into an array as shown in Figure 8.
Saving the audit results
As the collection is a long running process that can take many minutes, the results are stored into a file in the user’s OneDrive ready to be consumed when the user opens the app. The flow will first try to retrieve the file and if it exists, delete it. Then, it will replace it with a new file. In this sample, the file name is set to DO_NOT_DELETE_OD4B_SHARING_AUIDIT.json and the format of the file is in JSON so that the Power App can easily load it.
Notification to the user
Once the audit has finished, an email is sent to the user to let them know that they can return to the app to view their shared files.
Load audit results into Power Apps
To load the audit results from the JSON file into Power Apps, another Power Automate flow is used. The flow retrieves the file content using the Get file content using path action. If the file exists, it is loaded into a string variable. If not, then it is empty. The results are then sent to the calling Power App and converted into a proper JSON object which a Power App collection can understand.
Power App loads the file
For this process, the user leverages a Power App to review and act on the share content. The app requires two galleries:
- Files gallery – to list all the files that are shared.
- Shares gallery – to list the users for each shared file.
App
When the app loads, the file shares are loaded into it using the Power Automate flow.
| Property | Value |
| OnStart | ClearCollect(myOD4BShares,GetOneDriveforBusinessAuditResults.Run()) |
Files gallery
The Files gallery lists all the files that have been shared. A text field is used to display the filename. A secondary text field can be added to display the file’s URL. When selecting a file, the list of users needs to be loaded into the itemShares collection for the Shares gallery.
| Property | Value |
| Items | myOD4BShares |
| OnSelect | ClearCollect(itemShares,AddColumns(ThisItem.SharedWith,"Action","")) |
Shares gallery
The Shares gallery lists all the users with whom a file has been shared. A text field is used to display each user’s email address with whom the file has been shared.
| Property | Value |
| Items | itemShares |
Then, the files gallery Items property is set to myOD4BShares. A text field in the Files gallery displays the Name field for each file. In the Shares gallery, a text field is used to display the email of the person it is shared with using the FilesGallery.Selecteditem.
Haniel Croitoru
Haniel Croitoru is an enterprise architect and Microsoft MVP with over 20 years of experience in Microsoft 365. Since 2003, Haniel has been focusing on delivering solutions to meet short-term and long-term business goals using Microsoft 365 workloads such as SharePoint, Teams, Power Platform and more.
In addition to Haniel’s professional tenure, he has always been a big proponent of sharing knowledge and giving back to the community through presenting at numerous conferences and networking meetings on the topics of utilizing Microsoft 365 for business process optimization, effective collaboration and communication, and managing projects using the platform.
Prior to entering the Microsoft 365 arena, Haniel spent several years in the medical imaging industry where he helped launch an orthopedic software division and published four patents and numerous articles.
Haniel holds a Master of Science in Computer Science with a specialty in Computer-Assisted Orthopedic Surgery from Queen’s University and a Master’s Certificate in Project Management from the York Schulich School of Business. He is a PMI-certified Project Management Professional (PMP) and Agile Certified Practitioner (PMI-ACP).