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

Let’s investigate the main.db in a hex editor.

The header of the file indicates that this is a SQLite based file. We can open this file using SQLite and start exploring.

Open the main.db file using sqlite3. To view the tables run this query:

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. http://osandamalith.github.io/SkypeFreak/

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

Want to learn more?? The InfoSec Institute Advanced Computer Forensics Training trains you on critical forensic skills that are difficult to master outside of a lab enviornment. Already know how to acquire forensically sound images? Perform file carving? Take your existing forensic knowledge further and sharpen your skills with this Advanced Computer Forensics Boot Camp from InfoSec Institute. Upon the completion of our Advanced Computer Forensics Boot Camp, students will know how to:
  • Perform Volume Shadow Copy (VSC) analysis
  • Advanced level file and data structure analysis for XP, Windows 7 and Server 2008/2012 systems
  • Timeline Analysis & Windows Application Analysis
  • iPhone Forensics

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

Thank you for reading this short article about the tool. I hope now you have a good idea about Skype forensics.