In this short tutorial, I am responding to a request from a user to allow both the raw column and its converted values to be displayed side by side. i.e. in a Skype messages table, we might want a report that contains the message timestamp, author and the message body. The following screenshot shows the raw report with just the three required columns checked:
We now right click on the timestamp column and access the “View column as..” menu option so we can customise the display of this column.
The subsequent dialog (shown below) gives us a number of choices, for a Skype date we need to select the “Unix 10 digit timestamp” option. For the purposes of this demo, we will leave the timezone alone.
The converted column now looks as follows:
Now we get to the crux of the tutorial – clearly there is only one check box in the query designer for the timestamp, so how do we add the timestamp column to the report a second time?
With the Forensic Browser for SQLite this is straight forward. In empty row in the columns display click on the down arrow (this will become visible when the row is selected) and choose the messages.timestamp column again.
The Browser will automatically add an alias for this column to avoid duplicate names.
Alternatively, we can rely on the SQL query language itself and enter a new line manually so that the code looks as below (the columns display will be automatically updated if you modify the SQL in this way).
Then Execute the query.
The example shown chooses the timestamp column again and provides an alias for it (as mentioned SQL won’t allow the same column name in the output twice – if you don’t add an alias when you manually type a query you will be prompted with a “duplicate column name” error).
As we haven’t provided a conversion for the column the displayed date is displayed in its raw form.