I discovered recently that the latest version of Skype (v4) now holds all users session data, such as your chats, in a SQL database. What’s more it is easy to write queries using standard SQL to query the database and extract data. Here I’ll show you how.
%appdata%/Skype/<username>/main.db
It is important to note that Skype MUST be closed before you can open the database.
Skype presents sixteen tables including Contacts, Messages, Voicemails, Calls and Chats. I am going to concentrate on Messages in this post but the principle is exactly the same for all the other tables.
The Messages table holds a record of all IM messages between you and your contacts and there is one row per message sent or received. Using this table it is possible to query the records for something in a number of interesting ways – by date range, username or even content of message. So, for example, the following query will return all messages that have occurred in the last day that have the word “SMTP” in the body:
SELECT author, datetime(timestamp, ‘unixepoch’), body_xml
FROM messages
WHERE timestamp BETWEEN strftime(‘%s’,’now’, ‘-1 days’) AND strftime(‘%s’,’now’)
AND body_xml LIKE ‘%SMTP%’
ORDER BY timestamp
A few things to note.
Firstly date/times are held as a time stamp, so the current time (2nd October 2009 11:23 BST) is held as 1254478990 so it is necessary to convert to and from this format. To convert a Skype timestamp to something that is human readable use the datetime function as one line one of the SQL statement above. the strftime function converts a date to the timestamp format used so the BETWEEN statement is looking for records between ‘now’ and one day ago.
Secondly, the body of the message is held as XML and so may contain more than just the message itself to take account of formatting etc.
With some clever uses of SQL it is possible to do some very complex searches but the restriction of having to have Skype closed is a bit limiting and I intend to see if there is a way around this.
You can get more information on the SQL statements that SQLite uses here and I would be interested to know how you have used this data or intend to.
(Lead picture: http://www.flickr.com/photos/malthe/ / CC BY-NC-SA 2.0)