SkypeFreak: A Cross-Platform Skype forensic tool.

[rusnaki]

This is a small tool that can be used to investigate Skype user accounts stored in your PC. First of all, let’s learn how to investigate data manually. This is a very easy to understand article. I hope you have a basic understanding of SQL. All the data is stored in the main.db file related to each user in separate folders.

Windows:

C:Users<user>AppDataRoamingSkype

Linux:

/home/<user>/.Skype/

OS X:

/home/<user>/.Skype/

Overview of the Database

SELECT tbl_name FROM sqlite_master WHERE type == 'table';

This query would return all the tables in the database from the schema of the database. Out of these we are interested in the data in the Accounts, Contacts, Calls, Conversions and Messages tables.

Profile of the User

To investigate the profile of the specific user, run this query. I hope you can understand these simple queries.

SELECT fullname, skypename, city, country,datetime(profile_timestamp,'unixepoch') FROM Accounts;

We are selecting those columns from the table Accounts. The date and time is stored using the POSIX time stamp. We need to return this value into a human readable format. So we have to use the function datetime() in SQLite and pass the argument ‘unixepoch’.

Contacts

To dump the contacts use this query.

SELECT displayname, skypename, country, city, about, phone_mobile, homepage, birthday , datetime(lastonline_timestamp,'unixepoch') FROM Contacts;

Calls

To dump all the calls of the user run this query.

SELECT datetime(begin_timestamp,'unixepoch'), time(duration,'unixepoch'), is_incoming, identity FROM calls, conversations WHERE calls.conv_dbid = conversations.id;

If the ‘is_incoming’ column returns ’1? it means the call was an incoming call. If it returns ’0? it means the call was an outgoing call.

Messages

To dump all the messages, run this query. We are not interested in data consisting of <partlist alt=””>.

SELECT datetime(timestamp,'unixepoch'), dialog_partner, author, body_xml FROM Messages;

If the ‘dialog_partner’ columns are equal to ‘author’ it means the message is FROM ‘author’. If the ‘dialog_partner’ columns are not equal to ‘author’ it means the message is TO ‘dialog_partner’.

You can see one result, “hi”, which is a message.

Automation

So now I think you know how to investigate a Skype database file and what is happening behind the scenes. I thought of writing a small tool to automate this. SkypeFreak by OsandaMalith

Features

* Fully open source

* Cross-platform. Works on Windows, Linux and OS X

* Written in Python 2.7

* Can write to a file and extract data easily

Available Options:

1.Profile

1.Contact

3.Calls

4.Messages

5.Generate a Full Report

To investigate the profile, enter 1 and you will see the profile details nicely formatted.

All returned data can be written to a file.

The calls can be retrieved, including incoming or outgoing, in a detailed manner.

The messages can be viewed nicely in a clear format.

You can easily generate the whole report a text file by entering option 5 and giving a file name.

Credits goes to : Osanda Malith.