Ernir.net home

Ernir writes

on computers and society, apparently.

Retrieving Skype chatroom history

Skype stores all text conversations in a local SQLite database. The file name is main.db, on my (Ubuntu) system the path is 

/home/[system username]/.Skype/[skype username]/main.db

The messages themselves are stored in a table appropriately named Messages, multi-user chatrooms in a table named Conversations. A SELECT statement to extract all data from one chatroom is:

SELECT Messages.from_dispname AS sender,
       datetime(TIMESTAMP, 'unixepoch') AS sent_at,
       Messages.body_xml AS message_text
FROM Messages
INNER JOIN Conversations ON Messages.convo_id = Conversations.id
WHERE Conversations.displayname='Star Wars - Episode VIII: Returning of the Torch'
ORDER BY TIMESTAMP;

Where 'Chatroom name' is your chatroom's displayed name as seen at the top of the chat.