Creating a timeline with the Forensic Browser for SQLite

by | Oct 13, 2014 | Forensic Browser for SQLite

Timelines are a hot topic in the forensic field and rightly so; viewing data from any source alone is only part of the picture, data when viewed in context alongside other columns often paints a very different picture and the evidence can sometimes just jump out at you.

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 complement 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:

  • Date
  • Time
  • DatabaseName
  • Table
  • User
  • Description

Let’s 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 :

Message from – -> :

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:

#johndoe/$r3scue193;5cf150f3ff4e274

This shows a message from johndow to r3scue193.

The SQLite core functions we will use are as follows

Code:

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
The first thing to do is extract all characters from the chatname field starting at character 2 (the first character in the string “#” is position 1) up until the “;” character. So our code will look like

Code:

Substr(messages.chatname, 2, x-2)
where x is the position of the “;” character

We get the value of x by use of the instr function as follows

Code:

Instr(messages.chatname, ‘;’)

Now substituting the expression above for x, this gives us the following compound expression

Code:

Substr(messages.chatname, 2, Instr(messages.chatname, ‘;’) -2)

Finally, we want to replace the two characters that separate the names ”/$” with “ – -> “
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.

Code:

replace(substr(messages.chatname, 2, instr(messages.chatname, ';')-2), '/$', ' --> ') AS Description

While developing your equation it makes sense to build it step by step, you can execute each function above in turn and see whether you get any errors, for instance in the first case you could use an arbitrary value, 21 and see what happens when you just use the Substr part of the expression, e.g. Substr(messages.chatname, 2,21) 

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:

Code:

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 an 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

Code:

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

A few things to keep in mind:

  • 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.

https://www.sqlite.org/lang.html

https://www.sqlite.org/lang_corefunc.html