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:
1
| 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.
1
| 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.
1
| 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.
1
| 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=”">.
1
| 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:
- Profile
- Contact
- Calls
- Messages
- 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.
Thank you for reading this short article about the tool. I hope now you have a good idea about Skype forensics.
No comments:
Post a Comment