Friday, March 15, 2013

sqlite3 + skype chat history + group chat history

Raw post, but very useful info for extracting messages from Skype chat SQLite databases. sqlite3.exe "$(cygpath 'C:\Users\kmilnes\Downloads\main-ml.db')"

Open a SQLite DB:

sqlite3 /path/to/your/main.db Cygwin: sqlite3.exe "$(cygpath 'c:\Downloads\main.db')"

SHOW TABLES:

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

DESCRIBE a table:

PRAGMA table_info(Conversations);

Schema (CREATE SYNTAX) for a table:

.schema TABLENAME

A list of unique usernames you've chatted with:

SELECT DISTINCT(dialog_partner) FROM Messages;

Show a list of messages for a given chat partner, ordered by timestamp:

SELECT author, from_dispname, datetime(timestamp, 'unixepoch') as date, body_xml FROM Messages where dialog_partner = 'your_dialog_partner' ORDER BY timestamp;

Show a unique list of participants from group chats you've had:

SELECT DISTINCT(participants) FROM Chats;

Get the chat id for a given group conversation:

SELECT id FROM Chats WHERE participants = 'participant1 participant2';

Get the conversation id for a given group conversation:

SELECT id FROM Conversations WHERE chat_dbid = 17627;

Get the messages for a given conversation id, ordered by timestamp:

SELECT author, from_dispname, datetime(timestamp, 'unixepoch') as date, body_xml FROM Messages where convo_id = 17631 ORDER BY timestamp;

20 comments:

Anonymous said...

this was very good.

Anonymous said...

this was very good...

Anonymous said...

but where does the messages goes? where can i open it n read it?

George said...

Great post, you have pointed out some good details , I too conceive this s a very wonderful website. free chat

Anjudevan said...

Really informative Blog...Thanks for sharing...Waiting for next update...
Wordpress Training in Chennai
Wordpress Training
wordpress course fees
wordpress training in vadapalani
wordpress training in Guindy
Struts Training in Chennai
clinical sas training in chennai
Spring Training in Chennai
Photoshop Classes in Chennai
LoadRunner Training in Chennai

Riya Raj said...
This comment has been removed by the author.
sanjandevan said...

awesome article,the content has very informative ideas, waiting for the next update...
javascript training in chennai
javascript training in velachery
javascript training courses
javascript training classes
javascript course fees
javascript training in chennai
core java training in chennai
Photoshop Classes in Chennai
Manual Testing Training in Chennai
Drupal Training in Chennai

Riya Raj said...

The blog which you have shared is more creative... Waiting for your upcoming data...
Python Training in Chennai
Python course in Chennai
Python Classes in Chennai
Python Training course
Python training in OMR
Python Training in Annanagar
Big data training in chennai
JAVA Training in Chennai
Selenium Training in Chennai
JAVA Training in Chennai

Vivek Vichu said...

Nice Blog...Thanks for sharing the article waiting for next update...
Artificial Intelligence Course in Chennai
artificial intelligence training in chennai
Mobile Testing Training in Chennai
C C++ Training in Chennai
javascript training in chennai
Html5 Training in Chennai
QTP Training in Chennai
Spring Training in Chennai
DOT NET Training in Chennai

Sivanandhana Girish said...

Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
AWS Training in Chennai
AWS Training
AWS Training institute in Chennai
DevOps Training in Chennai
Azure Training in Chennai
VMware Training in Chennai
AWS Training in Velachery
AWS Training in Tambaram
AWS Training in Tnagar
AWS Training in Anna nagar

Sivanandhana Girish said...
This comment has been removed by the author.
balamurugan said...

Good Blog!!! Thanks for sharing this great information with us
Selenium Training in Chennai
Selenium Course in Chennai
selenium certification in chennai
Best selenium Training Institute in Chennai
Selenium Training in Velachery
Selenium training in Adyar
Python Training in Chennai
Software testing training in chennai
JAVA Training in Chennai

vinudevan said...

Valuable one...thanks for sharing...
DOT NET Training in Chennai
Best DOT NET Training institute in Chennai
Best DOT NET Training in Chennai
dot net coaching centers in chennai
dot net training in Guindy
Html5 Training in Chennai
Spring Training in Chennai
Struts Training in Chennai
Wordpress Training in Chennai
SAS Training in Chennai

The India said...

Content Writing Company in Delhi
Content Writing Services in Delhi
Mobile App Development Company Delhi
PPC Company in Delhi
PPC Company in India

Vivek Vichu said...

Nice Blog...Thanks for sharing the article waiting for next update...
Artificial Intelligence Course in Chennai
artificial intelligence training in chennai
Mobile Testing Training in Chennai
C C++ Training in Chennai
javascript training in chennai
Html5 Training in Chennai
QTP Training in Chennai
Spring Training in Chennai
DOT NET Training in Chennai

vinudevan said...

Thanks for sharing informative article with us..
DOT NET Training in Chennai
best .net training institute in chennai
dot net training center in chennai
best .net training in chennai
Dot Net Training in OMR
Html5 Training in Chennai
Spring Training in Chennai
Struts Training in Chennai
Wordpress Training in Chennai
SAS Training in Chennai

rinjuesther said...

awesome article,the content has very informative ideas, waiting for the next update...
clinical sas training in chennai
clinical sas training fees
clinical sas training in vadapalani
clinical sas training in Guindy
clinical sas training in Thiruvanmiyur
SAS Training in Chennai
Spring Training in Chennai
LoadRunner Training in Chennai
QTP Training in Chennai
javascript training in chennai

rinjuesther said...

awesome article,the content has very informative ideas, waiting for the next update...
clinical sas training in chennai
clinical sas training fees
clinical sas training in vadapalani
clinical sas training in Guindy
clinical sas training in Thiruvanmiyur
SAS Training in Chennai
Spring Training in Chennai
LoadRunner Training in Chennai
QTP Training in Chennai
javascript training in chennai

lokesh said...

I am inspired to read this blog. keep sharing more informaive blog.
PHP Training in Chennai
PHP Training in bangalore
PHP Training in Coimbatore
PHP Course in Madurai
Spoken English Classes in Bangalore
Data Science Course in Chennai
Best PHP Training in Chennai
Best PHP Training Institute in Bangalore
Best PHP Training in Coimbatore
AWS Training in Chennai

sasi said...


The blog you shared is very good. I expect more information from you like this blog. Thankyou.
web designing training in chennai
web designing training in bangalore
web design training coimbatore
Web Designing Course in bangalore
web designing course in madurai
Web development training in bangalore
Web development training in chennai
Big Data Course in Coimbatore
Web Designing Course in bangalore
web designing course in madurai