Recently one of our users contacted me and asked for help creating a custom report for a Skype database after other forensic software had failed to include some very relevant data in their reports’.
In a nutshell, the messages report he had produced using the other software only included the author of a given message and did not list the other person(s) who were part of that conversation. This information is maintained in different places in a Skype main.db SQLite database. This article discusses how to include this information in a user-friendly format.
In order to include the required information, the two tables that we need are the messages table and the participant’s table. Normally in a Skype installation, this table includes most of the information we want in the dialog_partner column, however in this database (from an HTC One phone) this column was blank.
In order to understand the problem more clearly have a look at this extract of selected columns from the participant’s table:
The convo_id column is a unique number that refers to each conversation; the identity column is the identity of the participants. Note that conversation 426 has 4 participants. Also note that r3scue193 (that’s me) appears in every conversation, as you would expect.
If we now look at the messages table we can see that there is a corresponding convo_id column and we can use this column to perform a join between the two tables so that we can include a list of all the participants.
If we simply do a LEFT join between the two tables on convo_id then for each row in the messages table we will get a join for each matching row in the participants table, i.e. for a conversation with two participants we would get a duplicated row for each participant (the identity column below), for three participants we would get three rows etc. – this would be very confusing:
The answer is the SQLite aggregate function group_concat, from the SQLite web site (https://www.sqlite.org/lang_aggfunc.html)
| group_concat(X) group_concat(X,Y) |
The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (“,”) is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary. |
The query we use is below and utilizes the group_concat function along with the GROUP BY expression:
Code:
SELECT Participants.convo_id,
Group_Concat(Participants.identity, ', ') AS ConversationParticipants
FROM Participants
GROUP BY Participants.convo_idThis might be clearer if we review the original table:
Remember you can use the Case Manager to save the query you have just created for re-use on another case.
We now want to use the query created above with the messages table so that we can list the participants alongside each message. But before we do that we will make a VIEW based on the above query. A VIEW can be thought of as a sort of temporary table and the Forensic Browser for SQLite allows you to create a VIEW by selecting the appropriate option from the Queries menu:
You then need to enter a name for the view
This view can now be used in the same way as any table by dragging it to the query designer. The screenshot below shows that:
Code:
SELECT Participants.convo_id,
Group_Concat(Participants.identity, ', ') AS ConversationParticipants
FROM Participants
GROUP BY Participants.convo_idCan now be replaced with:
Code:
SELECT *
FROM Convo_participants
All that now remains is to create a JOIN on the messages table using our new view and select the columns we want to display:
