There have been many articles written about timelines and there are various programs such as log2timeline from the SANS institute that will help an investigator create timelines. Some of these programs have plugins that allow you to work with specific SQLite databases, but not all databases have plugins written for them and sometimes the table schema changes.
To compliment these programs you can use The Forensic Browser for SQLite to create a timeline on multiple tables and output the data in a comma separated format, such that the timeline can be examined with another tool of your choice.
The following tutorial shows you how to create an arbitrary timeline on two tables from a Skype database. This could easily be extended to multiple tables on almost any database. The tutorial is also a useful source of information regarding some slightly more complex SQL constructs and some of the inbuilt “core” SQLite functions.
The main feature of a timeline is that it has one main datetime field and additional fields that describe an event that happened at that time. Depending on which format you use there can be anything from about 5 to 17 fields. For our example we will use the following:
Lets start building our timeline field by field (this is a sensible way to proceed as it lets us deal with problems in isolation before we move on) we will start with the Skype messages table.
Open a Skype database and add the message table to the query designer.
Add the timestamp field with an alias Date.
Execute the query and right click on the results to choose a conversion.
Select Unix 10 digit timestamp and “Date Only” using the format yyyy/MM/dd (note that you can set different formats for each and every date/time field within The Forensic Browser).
Add another timestamp by clicking on a down arrow in the “fields designer” in the empty row at the bottom.
Execute the query again.
For this time field set the format to “Time Only” and HH:mm:ss.
The display should now look like the screenshot below:
The next two columns are constants and have values that we will enter ourselves. We do this by entering a single quoted string in the expression column and an unquoted alias (no spaces allowed here) in the Alias column. This will now look as follows:
When we execute the query now it should look like this:
Our next task is to add the user name. We could add this field as a constant as it will not change for this query, but we want to make our query portable across Skype databases so this can be taken from the Skype Accounts table using the skypename field.
Double click on the accounts table in the table list to add it to the query designer and check the skypename field to add it to the field designer, again add the alias, in this case User. The complete display should look something like this:
Finally we need to add the description. What I want to add here is text in the form of :
For this expression we need to get clever with the skype database and some of the functions built into SQLite. There are a number of fields in the messages table that can help us with what we want but the best is the chatname field, an example of which is:
This shows a message from johndow to r3scue193.
The SQLite core functions we will use are as follows
substr(X,Y,Z) returns a string consisting, from string X, consisting of Z characters starting at Y instr(X,Y) finds the first occurrence of string Y in string (or field) X replace(X,Y,Z) replaces string Y with string Z in string (or field) X
Substr(messages.chatname, 2, x-2)
We get the value of x by use of the instr function as follows
Substr(messages.chatname, 2, Instr(messages.chatname, ‘;’) -2)
So we need to use the replace function with our compound function above as the source string - and we give this expression the Alias Description.
replace(substr(messages.chatname, 2, instr(messages.chatname, ';')-2), '/$', ' --> ') AS Description
I am sure you have noted that there is one thing missing from our query, the message itself.
To get this we just need to specify the messages.body_xml field at the end of the above expression and use the SQLite concatenate symbol || (placing this between two strings joins them together), we also want to add a couple of space characters before the message field to make it readable.
This field expression now becomes:
Replace(SubStr(Messages.chatname, 2, instr(Messages.chatname, ';') - 2), '/$', ' --> ') || ' ' || Messages.body_xml AS Description And our final SQL becomes: SELECT Messages."timestamp" AS Date, Messages."timestamp" AS Time, 'Main.db' AS DataBaseName, 'Messages' AS TableName, Accounts.skypename AS User, Replace(SubStr(Messages.chatname, 2, instr(Messages.chatname, ';') - 2), '/$', ' --> ') || ' ' || Messages.body_xml AS Description FROM Messages, Accounts
When we started this tutorial I said that we would make our timeline on multiple different tables, we do this by means of a SQLite union. A union is simply a joining of multiple tables that have the same names (there was another reason for adding an alias to each column in our query above).
For our example I will use the Transfers table, this lists files transferred between users.
So how do we create a union? Click on the plus symbol at the top right of the query designer window, this will create a union subquery. A second “Q” will appear next to the first and clicking on each will cycle between the two subqueries.
Within the new blank subquery we can start creating our new query exactly as we did before, this time using the Transfers table.
There is no timestamp in this table, but we can use the starttime column, add two instances of it above and give one an alias Date and the other Time as we did above.
Add the two literal text fields as above, make sure that the second uses the TableName ‘Transfers’ (it’s always good to fully qualify your field name when performing joins, tablename.fieldname is much clearer when debugging).
Add the account name as above.
And finally, just to keep this tutorial simple, we will just add the transfers.filename concatenated with the filesize as the final field with an alias description. The final SQL looks like this
SELECT Messages."timestamp" AS Date, Messages."timestamp" AS Time, 'Main.db' AS DataBaseName, 'Messages' AS TableName, Accounts.skypename, Replace(SubStr(Messages.chatname, 2, instr(Messages.chatname, ';') - 2), '/$', ' --> ') || ' ' || Messages.body_xml AS Description FROM Messages, Accounts UNION SELECT Transfers.starttime AS Date, Transfers.starttime AS Time, 'Main.db' AS DataBaseName, 'Transfers' AS TableName, Accounts.skypename AS User, Transfers.filename || ' ' || Transfers.filesize AS Description FROM Transfers, Accounts
• All of the fields/columns in each part of the union MUST have the same name or alias (this is one reason we used aliases)
• All of the fields/columns in each part of the union must be in the same order, i.e. date first, followed by time, followed by DatabaseName…
• You cannot execute a subquery until both of the above criteria are met
The final output of the Forensic Browser for SQLite look as below:
There is one final thing that we may wish to do, we need to output this table into a form suitable for importing into another forensic tool. Provided we do not have any graphics or hex displays in our output we can use the inbuilt Export current query to CSV option available from the “File|Report menu”.
You can of course continue and create additional unions on different tables and then save your final SQL query for re-use next time you see the same database.