Thursday, April 28, 2011

Analyzing Skype Chat and Call Logs

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.

Regularly backup the main.db file