Skype, more and more, is becoming a memory dump for me. My Co-workers post quick how-to do this or
that into chat rooms, resulting in Skype becoming a repository of
information.
It's all there for the
asking, but Skype's search is really lacking!!!
When I am looking for a bit of information, it goes a bit like this… I remember joe told me that in room X
sometime within the last month. So as a
result of that I would love to search based on
- User (or users)
- Room
- Date Range
My first go at this is to just dump all the messages into a
text file, so I can at least grep it.
This document is going to cover creating a simple script to dump the
data to a text file.
Find the Skype Database!
Skype stores all the messages in an SQList database. This database is called main.db
Where is it?
On Windows 7
C:\Users\<windows username>\AppData\Roaming\Skype\<Skype Username>\main.db
If your Windows username is joe and Skype username is
pitfall the location of main.db would be
C:\Users\joe\AppData\Roaming\Skype\pitfall\main.db
In OS X
~/Library/Application Support/Skype/<Skype Username>/main.db
If your username where pitfall the location of main.db would
be
~/Library/Application Support/Skype/pitfall/main.db
Install a Ruby Gem
The ruby script I am writing requires the sqlite3-ruby gem https://rubygems.org/gems/sqlite3-ruby
[1] Their github page https://github.com/sparklemotion/sqlite3-ruby
[2]
To install run the following command.
> gem install sqlite3-ruby
|
I also decided to use the OS gem to determine which OS the
script was running on. The gem page for
the OS Gem is at https://rubygems.org/gems/os
[3] their github page is at https://github.com/rdp/os
[4]
> gem install os
|
Simple test script
This simple script is just for testing purposes, to make
sure you can connect to the DB and dump a few messages to the screen
Create the script
> touch skype_dump.rb
> chmod u+x skype_dump.rb
> vu skype_dump.rb
|
Here is the simple script.
Replace the username and skype_username with your own.
#!/usr/bin/ruby
require 'sqlite3'
require 'os'
username = 'USERNAME'
skype_name = 'SKYPE_USERNAME'
if OS.windows?
db_loc = "C:/Users/#{username}/AppData/Roaming/Skype/#{skype_name}/main.db"
elsif
OS::Underlying.windows?
db_loc = "/cygdrive/c/Users/#{username}/AppData/Roaming/Skype/#{skype_name}/main.db"
elsif
db_loc = "/Users/#{username}/Library/Application
Support/Skype/#{skype_name}/main.db"
else
abort "Exiting, Can't determine if OSX
or Windows"
end
begin
db = SQLite3::Database.open db_loc
stmt = db.prepare "SELECT body_xml
from messages order by id DESC limit 10"
rs = stmt.execute
rs.each do |row|
puts row.join "\s"
end
rescue
SQLite3::Exception => e
puts "Exception occured"
puts e
ensure
stmt.close if stmt
db.close if db
end
|
Now run it
> ./skype_dump.rb
|
It successfully displayed the last 10 messages that were
skyped to me!
Adding in timestamps, room names, and user names
Here is the SQL query I came up with to get this data.
select msg.id, msg.timestamp,
msg.from_dispname, con.meta_topic, msg.body_xml
from messages as msg,
conversations as con
where msg.convo_id = con.id
order by msg.id;
|
And here is the ruby script (replace the user names)
#!/usr/bin/ruby
require 'sqlite3'
require 'os'
username = 'USERNAME'
skype_name = 'SKYPE_USERNAME'
if OS.windows?
db_loc =
"C:/Users/#{username}/AppData/Roaming/Skype/#{skype_name}/main.db"
elsif OS::Underlying.windows?
db_loc =
"/cygdrive/c/Users/#{username}/AppData/Roaming/Skype/#{skype_name}/main.db"
elsif
db_loc = "/Users/#{username}/Library/Application Support/Skype/#{skype_name}/main.db"
else
abort "Exiting, Can't determine if OSX or Windows"
end
begin
db = SQLite3::Database.open db_loc
query = 'select msg.id, msg.timestamp, '\
'msg.from_dispname, con.meta_topic,
'\
'msg.body_xml '\
'from messages as msg,
conversations as con '\
'where msg.convo_id = con.id '\
'order by msg.id;'
stmt = db.prepare query
rs = stmt.execute
rs.each do |row|
puts row.join(":::").gsub(/\r?\n/, "\\n")
end
rescue SQLite3::Exception => e
puts "Exception occured"
puts e
ensure
stmt.close if stmt
db.close if db
end
|
The Message can contain carriage returns. Since all the row data to remain on one row,
I replace each carriage return with the literal character \n
This leaves \n in multiline messages so that you can back them out later
if you want to. (not perfect but a
start)
puts row.join(":::").gsub(/\r?\n/, "\\n")
|
I tested it and it works.
I dumped it to a file
> ./skype_dump.rb > dump.txt
|
Then grepped on a name
> grep -nI ":::Vince"
dump.txt | grep -i "ios"
|
This returns the message from a user named Vince where he
mentioned ios.
It’s not a perfect solution but at least gives me a file I can grep through. Hope this helps someone.
I put the code up on github gist at https://gist.github.com/patmandenver/0e753184c789dc0f40eb
References
[1] SQlite3 ruby gem
Accessed 1/2015
[2] SQlite3 github page
Accessed 1/2015
[3] OS Gem page
Accessed 1/2015
[4] OS Gem github page
Accessed 1/2015
No comments:
Post a Comment