Google Chrome Download History

Google Chrome keeps a wealth of data that is of interest to the forensic examiner.  There are tools that look at the browser history and cache, but tools that examine the download history are less frequent.  Part of the reason it is overlooked by examiner's, I suspect, is that the download history is a table in the 'History' SQLite database, and not a separate file itself.

The History database, found in '/home//.config/chromium/Default' for the open version of chrome, i.e., Chromium, contains the following tables:
$ sqlite3 History .tables
downloads             presentation          urls
keyword_search_terms  segment_usage         visit_source
meta                  segments              visits

The downloads table is the focus of this post.  It is structured as follows:

$ sqlite3 History .schema | grep downloads
CREATE TABLE downloads (id INTEGER PRIMARY KEY,full_path LONGVARCHAR NOT NULL,url LONGVARCHAR NOT NULL,start_time INTEGER NOT NULL,received_bytes INTEGER NOT NULL,total_bytes INTEGER NOT NULL,state INTEGER NOT NULL);

For the uninitiated, the table schema tells us that each record contains 'id', 'full_path', 'url', 'start_time', 'received_bytes', 'total_bytes', and 'state' fields.  We can extract the data in csv format, as follows:

sqlite -header -csv History "SELECT * FROM downloads"

That's nice, but the dates are unixepoch and we don't know what the 'state' integer means, though it can be deduced from the 'received_bytes' and 'total_bytes' fields and studying a known source of data.  I determined that state 1 and 2 mean "complete" and "incomplete" download respectively.

To convert unixepoch to local time, we use the SQLite datetime function.   We tell datetime to take the start_time integer, interpret it as unixepoch time, and translate to localtime.  Exerpted from the rest of the query, it looks like this:

datetime(start_time,'unixepoch','localtime')

For clarity in the output, it would be nice to convert the state integer to its text equivalent.  We can do this with the case command.  The case command is the if/then statement of SQLite.  In its simplest application it takes the form 'CASE  WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END' where x is the field data, w is a comparison value, and r is the result that is returned.  It is easiest to understand when we apply it to our current circumstance:

CASE WHEN state=1 THEN 'complete' WHEN state=2 THEN 'incomplete' ELSE 'unknown' END

Putting it all together, we can obtain nicely formatted output from the downloads table of the Chrome History database with the following command:

sqlite3 -header -csv History "SELECT id,full_path, url, datetime(start_time,'unixepoch','localtime') AS date, received_bytes, total_bytes, CASE WHEN state=1 THEN 'complete' WHEN state=2 THEN 'incomplete' ELSE 'unknown' END AS state FROM downloads"

The caps in the commands are not required, but are designed to make reading the SQLite operators easier.  You may have noted the "AS" operators after the datetime function and case expressions.  It serves to replace the expressions as the header for that column in your csv output.

Your output should look like:

id,full_path,url,date,received_bytes,total_bytes,state
1,/home/slosleuth/Downloads/keepnote_0.7.3-1_all.deb,http://keepnote.org/keepnote/download/keepnote_0.7.3-1_all.deb,"2011-06-23 12:04:46",500022,500022,complete
2,/home/slosleuth/Downloads/iso/nbcaine2.0.dd.gz,http://www.caine-live.net/Downloads/nbcaine2.0.dd.gz,"2011-06-24 13:25:42",85962197,0,incomplete
3,/home/slosleuth/Downloads/iso/nbcaine2.0gz.md5.txt,http://www.caine-live.net/Downloads/nbcaine2.0gz.md5.txt,"2011-06-24 13:27:23",51,51,complete

I hope this helps you with SQLite queries in general and specifically to parse the data in the Google Chrome downloads table of the History SQLite database.