18 05 2012
Google Drive Forensics Notes
sysforensics | Google Drive, Powershell Forensics
UPDATE: 6DEC2012
I wrote some new software that will parse the databases for Google Drive. You can find it here: http://code.google.com/p/barff/ I wont be using Powershell anymore for parsing.
UPDATED 28MAY12:
Google appears to have made some changes to the snapshot.db. I could no longer open up the file using SQLite Browser. I could; however, open it up using the firefox add-on SQLite Manager. It appears to be something with “Write-Ahead Logging” (WAL) which is incompatible with previous versions. After looking at the sqlite website I makes some notes about how WAL is faster, reading and writing can proceed concurrently, etc. Sounds like improvements vs. “changes”.
——-
Default Installation Path:
C:\Program Files\Google
C:\Users\\Google Drive
Database Location:
C:\Users\\AppData\Local\Google\Drive
C:\>dir C:\Users\<username<\AppData\Local\Google\Drive
05/18/2012 05:27 PM
.
05/18/2012 05:27 PM..
05/18/2012 03:05 PM 3,245 cacerts
05/18/2012 01:55 PMCrashReports
05/18/2012 03:05 PM 0 lockfile
05/18/2012 03:05 PM 4 pid
05/18/2012 05:27 PM 28,672 snapshot.db
05/18/2012 01:56 PM 3,072 sync_config.dbsnapshot.db: The snapshot.db (SQLite3) is where you will find the majority of your artifacts. I will give some picture overviews further down, but for now here is a picture of the structure:
sync_config.db:
The data -> data_value -> Contains the users email address, installation path, and also the version of the software. I don’t see anything else important in this db. This is also SQLite3.
caccerts contains a certificate. PID, when opened via a hex editor shows a number. It appears to grow. Mine started out at 716, and it’s now 2472. I’m not sure what this is as of this writing.
Let’s take a look at the snapshot.db -> cloud_entry
Resource ID: Provides the file type, and then what appears to be the parent location, but i’m not 100% sure yet.
Filename: File name. One thing to note is the file extensions don’t appear on some of the files. As a matter of fact, none of the file extensions appear on the file names if it was created within google docs via the cloud. It appears they associate the files via a “document type” number.
Modified: When the file was modified.
Created: When the file was created. File creation will not show up if it was put into the cloud locally. It will; however, show modified if the file is edited in the cloud, or locally.
ACL Role: I haven’t figured this one out yet. I tried every sharing combination, but didn’t have any luck. It stayed at 0 no matter what.
Document Type: This changes based on what document you create in the cloud. It appears that no document type is selected when you upload “stuff” locally from your machine; however, When I created a local folder it did assign it 0, which is the same as what it gets assigned if a folder is created in the cloud.
Also, when I uploaded .txt files locally they were assigned number 1. Exe files were assigned 1 as well. I tried to upload a .doc, .xls, .pdf, and .mp3 and none of them were assigned a document type. I’ll keep trying to add more and more file types.
Document Type List:
0 = Folder
1 = Appears to be a place holder type for various file extensions.
2 = Google Presentation
3 = I created everything I could in GoogleDocs and didn’t get assigned a 3
4 = Google Form
5 = Google Drawing
6 = Google Document
7 = Google Table (Currently in Beta)
Removed: When files are removed they are removed from the database, so I am not sure what this is. Still needs more research.
URL: Location of the file/folder via URL
Size: Size of the file. Folders don’t appear to have sizes even if their are files inside them.
Checksum: md5 hash of the files. When files are created in the cloud they do not appear to get an md5 checksum. They get md5 checksums if they are locally placed in the Google Drive or uploaded via the web through the upload feature Google has.
snapshot.db -> cloud_relations
This shows the child/parent resource id relationship. You can see here that the item in row 5 is located in a folder. If you reference from one of the above images you will see that it’s the Test folder, and the file is “test.txt”. You can see the Child/Parent relationships here. This would help you create a tree structure pretty easily.
snapshot.db -> local_entry
This section covers the actions locally with Google Drive. This provides the inode number for each file created locally and then uploaded to the cloud.
snapshot.db -> local_relations
Here you see the child/parent relationship via inodes. The one in red shows that “test.txt” is the child to folder, “Test”. All of the other ones are children of “root”, which is created by default.
snapshot.db -> mapping
This shows the inode number to resource id mapping.
snapshot.db -> overlay_status
Nothing had populated here yet.
I took a look at the registry to see if I could find anything useful:
InstallTime: HKLM\Software\Google\Update\ClientState\<two_listed>\InstallTime
LastCheckSuccess: HKLM\Software\Google\Update\ClientState\<two_listed>\LastCheckSuccess
InstallLocation: HKLM\Software\Google\Drive\InstallLocation
Google Drive, Google Drive Forensics, Powershell Forensics
In short, what you’re saying is that the snapshot.db file is a SQLite database…would that be correct?
Correct. SQLite format 3. Both the sync_config and the snapshot dbs are SQLite3.
Someone (Alex) posted a comment, but for some reason it didn’t post on here. Here it is:
Do you know what Journal mode is used and whether the databases are auto-vacuumed?
You can ask the databases with the following queries:
PRAGMA journal_mode;
PRAGMA auto_vacuum;
Also, a suggestion for making your script a little more concise:
file_types = ["Folder", "Null", "Google Presentation", "Unknown", "Google Form", "Google Drawing", "Google Document", "Google Table"]
for rows in cur:
print “{0}: {1}”.format(rows[0], file_types[rows[3]])
That way you can cut out that long elif chain.
Thanks for your continued great work.
I hope this comment works!
I wrote up some details of the Write Ahead Log here: http://digitalinvestigation.wordpress.com/2012/05/04/the-forensic-implications-of-sqlites-write-ahead-log/
I don’t know which SQLite viewer you’re using, but write ahead log has been around for a fair time, so it could be that your viewer is out of date. The official command line version of SQLite certainly supports it as does “SQLite Expert” if you’re on Windows (the “personal version” is free).
I was able to open the snapshot using MiTeC’s SQLite Query.
Thanks Mark!
me too on Linux: SQLite data browser
Patrick, have you decoded the entries in the cloud_relations or mapping tables? I’m wondering if they give any more useful info.
It’s on my to-do list, but I haven’t gotten around to it. Have you looked at it at all?
They are joining tables, containing foreign keys, pretty simple BTW.
Have any found out what to look for it Google Drive get’s error and print out “Unable to sync” or “Google drive need to be restarted”?
It if was possible to get this information from the db programmatically it was to restart Google drive desktop to avoid this syncing problems that many have. And just force a restart of the desktop app.
[...] I also suggest reading my post on Google Drive Forensics. [...]