When reviewing activity from a Mac OSX system there are a number of great artifacts to consider in your investigations.
In this post I’m going to talk about the Quicklook database that stores metadata for thumbnails of files you view in the Mac Finder. When Finder session renders that thumbnail it tracks the thumbnail information in two files.
- The first which I’m covering in this post is a SQLite database called “index.sqlite”.
- The second is a cache file “thumbnails.cache” that stores the thumbnails and is also valuable. If you are interested in thumbnails.cache you can find some information by Simon Key at Guidance here.
Finding the Quicklook database
In my CEIC presentation in 2014 I briefly talked about the immense value of the Quicklook database and now I’m following up with some information on how to link the thumbnail last visit date with the filenames! I’m a little late to this party but come on, we all have day jobs.
The path to the Quicklook thumbnail database is a dynamic path but follows a general path in the table below.
- As you can see the part <dynamic> is the part that varies. You can use a simple find command to locate the database.
About the Quicklook database
The Quicklook database has several tables that are valuable to us. When looking at the table files we see columns for folder and file_name among others. We also have a tabled called thumbnails. When we try to join the obvious columns in those two tables we immediately learn that they don’t match up.
So what’s going on here? Well, what has happened is thumbnails is using the rowid increment value that was created by the table called files. The thumbnails table is using that rowid as the column file_id.
Making it make sense
I’m going to give you SQLite syntax later on so you can adjust it to your needs. However, to understand what I’m doing, and why it’s not crazy requires you to know a bit about how SQLite works.
- First, when rows are inserted in to a table in SQLite an auto-incremented value called “rowid” is created to track the row’s.
- rowid is a hidden value so when you query the table with something like “select * from bad_guys_stuff” you won’t see rowid. A little something special is needed.
Here you can see that I am querying a table specifically for the column rowid even though it’s not a column in the schema for my table!
- I run .schema tablename to show the tables columns.
- Then i query the table using “select rowid from tablename” as seen below:
As you can see above. I do not have a column called rowid…
- Let’s query a table for the column rowid anyway. Oh hey, there it is!
It’s magic time
Now that we have established that rowid can be accessed using a select statement let’s look at the Quicklook SQLite database called index.sqlite using my query and pull some data.
Since we can not join on rowid since it’s not a real column we have to use a different method. Trying to solve this problem I remembered you can create virtual tables in SQL to represent information in various ways… Below I have highlighted in blue the part of the code that represents this virtual table, aka secret sauce.
| ## A SQLite Query to link the Quicklook database ## found in MacOS. ## Version 0.1 by David Dym ## Version 0.2 by David Dym on 1/14/15 select distinct |
Results
Here you can see a sql statement that queries index.sqlite and turns the table files into a virtual table name of “k” with an extra column as f_rowid using SQL aliasing.
- Aliasing from SQL92, is shared by most SQL variants (mysql,ms sql, postgres and sqlite) and is very very useful.
Let’s dissect this query a little bit more. I’m doing a couple fun things here:
- I’m creating the virtual table to use the rowid as mentioned but I’m also ordering the results.
- It’s important to use a LEFT join because you could have entries in the files table that no longer or never had thumbnails.
- The last_hit_date is stored in the apple date-time format starting in 1/1/2001. I’ve converted the time to localtime.
Wrap-Up
So there you have it. A SQLite query to decode thumbnail activity from the QuickLook database in MacOSX+. I know I may have lost a few of you that don’t have experience with databases. I tried to break it down as much as possible so I hope it was helpful! There are other opportunities with SQLite databases on Mac’s and I’ll try to do some more posts in the future.
The Quicklook information should help supercharge your Mac investigations. I look forward to feedback and hope you find it useful in your investigations. If you have questions or comments feel free to comment on this post or send me an email me.
Enjoy!
David
You can download the query “Quicklook_Query.sql” from downloads.
SQLite Tools
- A couple of ways to review a SQLite database are to use the command line tool from sqlite.org. You can also use SQLiteman as well which ships with the command line interface and a nice free user interface.