Thursday, April 28, 2011

Analyzing Skype Chat and Call Logs

Introduction
Skype chat and call logs are stored in the main.db file. These .db files are in SQLite database format.
To read the main.db file, you need to use a SQLite database browser.


Obtain a SQLite database browser
I like to use the Mozilla Firefox browser called SQLite Manager
  • Download Mozilla Firefox
  • Once installed, go to Tools > Add-ons
  • Search for SQLite and install the SQLite Manager
  • Reopen the Firefox browser after install


Location of Files
On Mac OS X
/Users/<current user>/Library/Application Support/Skype/<skype id>/main.db
On Windows
C:\Documents and Settings\<current user>\Application Data\Skype\<skype id>\main.db
On Windows Vista and 2008
C:\Documents and Settings\<current user>\AppData\Roaming\Skype\<skype id>\main.db


Review data in SQLite Manager


Call Logs
There are two tables in main.db that contain the call logs, whether they are skype-to-phone or skype-to-skype. It will display the phone number calls, call durations, source of call, and destination of call.


The two tables are similar but not quite. CallMembers table shows skype-to-phone calls but shows skype-to-skype calls as well however it misses any calls that were disconnected (abnormal ending).
Calls table does not show skype-to-phone calls but does show skype-to-skype calls, even those that were disconnected.


CallMembers Table
  • identity - destination of the call
  • guid - unique identifier and shows source of call
  • start_timestamp - date/time converted from unix epoch time to local time
  • call_duration - number of seconds of the call

SELECT identity as source,guid,strftime('%Y-%m-%d %H:%M:%S', start_timestamp,'unixepoch','localtime') as start_time,call_duration/60 as num_minutes FROM CallMembers order by id

Calls Table
  • host_identity - source of call
  • current_video_audience - destination of call
  • begin_timestamp - date/time converted from unix epoch time to local time
  • duration - number of seconds of the call
SELECT host_identity as source, current_video_audience as destination,strftime('%Y-%m-%d %H:%M:%S', begin_timestamp,'unixepoch','localtime') as start_time,duration/60 as num_minutes FROM Calls order by id

Chat Logs
The Messages table in the main.db contains the chat log. 


Messages Table
  • author - source of chat
  • chatname - unique identifier and shows source of chat
  • timestamp - date/time converted from unix epoch time to local time
  • body_xml - chat messages
SELECT author as source,chatname,strftime('%Y-%m-%d %H:%M:%S', timestamp,'unixepoch','localtime') as start_time,body_xml as message from messages

Export SQL Query Results to CSV
When you execute the query, adjacent to the Run Query button is Actions. You may select to save CSV to file.

Backups
Regularly backup the main.db file

6 comments:

  1. Oh thank you so much for this info. I have been looking for a way to export my chat history from a Mac for a long time. This helps me a lot! Thank you again!

    ReplyDelete
  2. Excellent post. I was looking into SQLite database for Skype and you brought some piece of answers.
    Thanks

    ReplyDelete
  3. This is the only place I could find the information for a visa application for mac computers - THANK YOU SO MUCH!

    ReplyDelete
  4. main.db is not highlighted as an option to choose. Suggestions? Thanks

    ReplyDelete
    Replies
    1. Where are you seeing that main.db is not an option. Do you mean on the filesystem? If so, at what directory level do the suggested paths no longer work?

      On Mac OS X
      /Users//Library/Application Support/Skype//main.db
      On Windows
      C:\Documents and Settings\\Application Data\Skype\\main.db
      On Windows Vista and 2008
      C:\Documents and Settings\\AppData\Roaming\Skype\\main.db

      Delete
  5. Thanks for your very helpful guide. I can run the queries within the Execute SQL tab as you describe and they run fine.

    However from there, I don't then seem to have any actions to export the specific results to a csv (I can do a general export but that gives me complete table data)

    Could you explain where I find this option? Many thanks.

    ReplyDelete