OneDrive Microsoft.FileUsageSync.db

I recently started to look into the Microsoft.FileUsageSync.db. The database can be found in %localappdata%\Microsoft\OneDrive\ListSync\Business<1-9>\settings. It is not documented in OneDrive Evolution because it only appears in OneDrive for Business. OneDrive Evolution's data is collected from personal only. It's not known what version this database first appeared in. Just like Microsoft.ListSync.db, this database is used by Microsoft.SharePoint.exe but is not related to the Offline Mode for web feature that I am aware of. There is some interesting data in the recent_files_formatted_spo table. The FormattedValue column holds JSON data that isn't the prettiest to look at.

To make the data easier to read, I wrote the following script to convert the JSON data into CSV format.

import sqlite3
import pandas as pd
import json

db_path = "Microsoft.FileUsageSync.db"

conn = sqlite3.connect(db_path)

query = "SELECT FormattedValue FROM recent_files_formatted_spo"

df = pd.read_sql_query(query, conn)

conn.close()


def parse_json(value):
    try:
        value = value.encode().decode('unicode_escape')

        return json.loads(value)
    except Exception as e:
        print("JSON Parse Error:", e)
        return None


df_parsed = df["FormattedValue"].apply(parse_json)

df_expanded = pd.json_normalize(df_parsed.dropna())

df_expanded.to_csv('output.csv', index=False, encoding='utf-8')

So what type of data does this table hold? Unfortunately, I cannot show you the data because I don't have a development environment so I'll do my best to explain what I found.

To give you an idea, when the data is parsed out, we have the following headers:
file.Id, file.@odata.id, file.FileModifiedTime, file.LastModifiedDateTime, file.FileCreatedTime, file.FileExtension, file.FileSize, file.StorageProviderContext, file.IsEmptyCopy, file.SharePointItem.SiteId, file.SharePointItem.WebId, file.SharePointItem.ListId, file.SharePointItem.UniqueId, file.ItemProperties.Shared.LastSharedWithMailboxOwnerByDisplayName, file.ItemProperties.Shared.LastSharedWithMailboxOwnerBySmtp, file.ItemProperties.Shared.LastSharedWithMailboxOwnerDateTime, file.ItemProperties.Shared.SubjectProperty, file.ItemProperties.Shared.AttachmentItemReferenceId, file.ItemProperties.Shared.AttachmentReferenceId, file.ItemProperties.Shared.ImmutableFileItemReferenceId, file.ItemProperties.AggregatedActivities.LastUserActivityDateTime, file.ItemProperties.AggregatedActivities.LastModifiedDateTime, file.ItemProperties.AggregatedActivities.MailboxOwnerTopInsights, file.ItemProperties.AggregatedActivities.IsHidden, file.ItemProperties.SemanticProperties.Title, file.UserRelationship.LastSharedDateTime, file.Visualization.Title, file.Visualization.AccessUrl, file.Visualization.Type, file.AllExtensions.SharingHistory.Instances, file.FileName, file.SharePointOnlineFacetStatus, file.Document.Title, file.WorkingSetId, activity.message_format, activity.type, activity.users, activity.timestamp, activity.extended_info.subject, file.UserRelationship.LastSharedById, file.Document.Author, file.SharePointItem.ModifiedBy, file.PrimaryItemLocation, file.SharePointItem.ContentClass, file.SharePointItem.SitePath, file.ItemProperties.Default.SiteTemplateId, activity.extended_info.sharing_medium, file.Visualization.ContainerTitle, file.Visualization.ContainerUrl, file.Visualization.PreviewImageUrl, file.FileOwner, file.SharePointItem.ContentTypeId, file.SharePointItem.ListItemId, file.SharePointItem.DocId, file.SharePointItem.ModifiedByDisplayName, file.SharePointItem.FileUrl, file.SharePointItem.ParentId, file.ItemProperties.Default.AuthorOWSUSER, file.ItemProperties.Default.EditorOWSUSER, file.ItemProperties.Default.DocumentLink, file.ItemProperties.AggregatedActivities.MailboxOwnerHistograms, file.ItemProperties.ClientAccessByMailboxOwner.LastAccessDateTime, file.ItemProperties.SemanticProperties.Url, file.ItemProperties.SemanticProperties.ContainerName, file.ItemProperties.SemanticProperties.ContainerUrl, file.UserRelationship.FrequentlyUsedSiteWeight, file.UserRelationship.LastAccessDateTime, file.ItemProperties.Default.ProgID, file.ItemProperties.Shared.TeamsMessageThreadId, file.ItemProperties.Direct.ColorHex, file.UserRelationship.LastModifiedDateTime, file.ItemProperties.Default.RecordingStartDateTime, file.ItemProperties.Default.RecordingEndDateTime, file.ItemProperties.Default.MeetingOrganizerId, file.ItemProperties.Default.MeetingICalUid, file.ItemProperties.Default.BaseType, file.ItemProperties.Default.ListTemplateTypeId, file.ItemProperties.Default.ListIcon, file.ItemProperties.Default.ListColor, activity.extended_info.navigation_id

It appears to hold information on files that are not necessarily in your OneDrive, but files that are shared from OneDrive. This can include files that were shared to you via email, Teams, and whiteboards to name a few.

Another interesting table is recommended_files. This table appears to hold a max of 20 files. One of the things that stood out to me was a description in the JSON data. The description is the first couple lines of the file so it could give us a good indication of what the file contains.

The last table I want to talk about is top_collaborators. This one holds information on people the user interacts with the most. We could potentially glean work relationships from this data.

The plan is to add this data into OneDriveExplorer once I can get it sorted out. Until then, use the script to explore this sure to be valuable forensic resource.

OneDriveExplorer Offline Mode Edition

Changes to OneDriveExplorer (ODE)

With this release, there are a few things to be aware of that have changed with the GUI and command line version.

GUI

The ODE GUI now has a profile selection. This is to make things easier so we don't have to point to certain files/folders for parsing settings data and logs. The options are still there but this is meant more for if you have a loose collection of files.

With the profile option, all we need to do is select the profile folder %LOCALAPPDATA%\Microsoft\OneDrive and ODE will do the rest. Logs will only be parsed if the Enable ODL log parsing option is enabled in the preferences.
The GUI can now indicate if the account is Personal or Business.

Command Line

With the command line, there is a new argument (--output-dir) to designate the save folder location. There is no longer a need to add a directory to --csv, --html, or --json. These arguments are now used to indicate what type of output you want the data stored in. Also, --csvf has been dropped.

New Additions

OneDrive Offline Mode

OneDrive for Business has a feature called Offline Mode that allows you to continue to use the web version of OneDrive without an internet connection. If you want to learn more, I had written about it in another article. In order for the database (Microsoft.ListSync.db) to populate, Offline Mode needs to be set up. First off, the feature needs to be pushed to your tenant by Microsoft (I believe Microsoft has finished rolling this out). Offline Mode is enabled by default but can be disabled via group policy. When you navigate to OneDrive for web, if you see a computer icon in the upper right of the page, Offline Mode is enabled and ready.

Once this is done, the Offline Mode database will be populated. There are also some limitations that might not allow Offline Mode to be enabled. See the Current limitations of offline mode section for more information.

What does this bring to OneDriveExplorer

With new features bring new data. So what kind of data does OneDriveExplorer get from Offline Mode? In addition to knowing a file/folder is shared, we can now see who it is shared with.

Another interesting artifact of this is seeing what other people have shared and to whom. If we look at a folder that was linked to OneDrive, the shared data is present, even though we did not do the sharing.

Another data point Offline Mode brings to ODE is OCR (Optical Character Recognition). Here is an example of the data in ODE verses the actual image.

More to come

There is still a lot of data to go through with Offline Mode that can be added to ODE. Additional work will be done to have a dedicated parser for Microsoft.ListSync.db for instances where that is the only file you have available. The latest version of OneDriveExplorer can be found here.

OneDrive Offline Mode (Recallish vibes)

Back in April 2024, Microsoft announced a new feature coming to OneDrive for Business called Offline Mode. The feature allows you to continue to use the web version of OneDrive without an internet connection. It works by downloading your file metadata and running a web server (Microsoft.SharePoint.exe) located in Program Files\Microsoft Onedrive\<OneDrive_Version>\. Now, as many of you may already know, I dabble in OneDrive forensic artifacts so when I finally got the feature in December I started to poke around. I found some pretty interesting things in the database that drives Offline Mode.

The database in question is called Microsoft.LinkSync.db. It is located in %LOCALAPPDATA%\Microsoft\OneDrive\ListSync\Business1\settings. This database contains the file metadata to help run Offline Mode. According to Microsofts own documentation,

"To accomplish this, a copy of your file metadata that powers OneDrive web app is securely stored locally on your device. These data on your device are only available to you. If someone else were to sign in on your device, these local data on the device wouldn't be available to them. We adhere to privacy guidelines outlined in the Microsoft Privacy Statement.

A secure local web server on your device handles the operations that you perform on your files, such as viewing, sorting, renaming, moving, and copying where traditionally these operations would need to be handled by the OneDrive cloud service. This results in fast and smooth interactions with your files like loading your files and folders, sorting, renaming, moving, renaming, and more. And all of these operations will continue to work even when you are offline, lose your internet connection, or run into a service disruption in the app."

I find this statement to not be true. How can this data be "securely stored" when there are no protections on the database? Microsoft.LinkSync.db is sitting there in an unecrypted state. I can grab this database and copy it to where ever I want, open it and view it. Even to another device. "these local data on the device wouldn't be available to them", I was able to access the database with an account with admin privileges and the data was available to me. Now I know you're saying but you had admin rights. True but according to Microsoft, it should not be available to me. Also, I'm not a hacker but I'm sure there are other ways to get to it. Lets take a peak at the data in this "securely stored" database that can be exfiled and read by anyone.

OCR data

In my quick look over of Microsoft.LinkSync.db, I came across a couple tables named list_<listID>_<siteID>_rows. One of them had a column named MediaServiceOCR. The data appears to be related to this feature. Nice feature, until recently, when the data is being stored locally, unsecured. Here is a small sample of the data:

But why should I be concerned. Not all of these images are stored locally, some can be online only. If someone were to start downloading images from the cloud, there would be traces in the Unified Audit Log (UAL). And if it were a lot of files, it would make some noise. So why not grab the database and not even touch OneDrive. Sound familiar (Recall)? The other issue with this is the user might not even realize that when they take a screenshot with something like Snipping tool, the default in Windows 11 is to save it to OneDrive whether they decided to save it or not. And there could be concerns about HIPAA and other regulations that need to be followed.

What other fun things can we find?

There is another table called site_<siteID>_users that, you guessed it, contains user information for your organization.

Along with all the usual things like file/folder names, paths, etc. I'm probably missing some things but again, this was a quick look.

What lead me to write this?

A couple days ago, SwiftOnSecurity posted a Tweet (yes I still call them that) that I had responded to.

This caught the attention of vx-underground. Even though they are more malware related, they like me for some reason.

It's just one poll but it looks like people don't want this data laying around unsecured. The collaboration with xv-underground lead to Smelly releasing a tool to "Tool designed to exfiltrate OneDrive Business OCR Data". In turn, I decided to release the python version of this I had been working on.

vx-underground OCRMe
Beercow OCRMe

I've done all I can to raise my concerns so I'll leave it up to the community to decide if this is truly a concern or not.

OneDrive Evolution Update

 OneDrive Evolution has been updated to v24.235.1121.0001. OneDrive Evolution now holds data on 549 version of OneDrive. You can find the lates information at OneDrive Evolution.

What's New in OneDriveExplorer

It's been about a year and a half since the initial release of OneDriveExplorer. With this being a major release, I thought I'd write about some of the updates and improvements. I've been working hard in my spare time to add in data as it is discovered and to give the best user experience possible. With that said, lets look at some of the improvements and features with this release.

Updated dat parser

The way OnedDriveExplorer initially would parse information out of the dat file was with regex. With the limited dataset, at the time, this was the best viable option. The problem with this approach is that it made it difficult to reliably extract more data. After analyzing 300+ OneDrive installs, the structures in the data file became better understood. OneDriveExplore is now able to "walk" the dat file from beginning to end without the need for regex making it more reliable and able to extract more data.

SQLite parsing

Although not entirely new, it appears OneDrive is moving away from the proprietary dat file to SQLite. OneDriveExplorer is able to parse these new SQLite files and there have already been instances where the dat file has been removed from the settings folder. So rest assure, OneDriveExplorer has your back.

Updated GUI and Status Column

The GUI now has a three pane view, much like Windows File Explorer. The left pane features a folder navigation view. The center pane shows files and folders in the selected folder along with the status, date modified and file size. The right pane gives you more details about the file or folder selected. A thing to note about the status column. There may be times a number appears in the column. This is the status number form the raw data. If visible, the status meaning is not entirely understood.

Another slight change is the search function. Instead of highlighting the matches found, they are now populated in the center pane.

The latest release of OneDriveExplorer can be found here.