Search via all Skype history

Question

How to search given string at all chat histories?
СTRL+F - search inside one chat, but I have a lot them, so I want find something in all saved chats?

Answer 1

In the Contact list or Recent list, simply click the contact or group you want to view the conversation history for and your conversation history is displayed.

The main.db database file where all chat history is stored. main.db is a standard SQLite database file and can be opened using any SQLite browser application. However, there is a free application, which is especially designed to read Skype’s chat and call data – SkypeLogView.

The main.db file is saved in one of the following (depending on OS).

On Windows 7 + : C:\Users\%USERNAME%\AppData\Roaming\Skype\[Skype User Name]

On Windows XP: C:\Documents and Settings\%USERNAME%\Application Data\Skype\[Skype User Name]

Following should work on all Windows versions, you can copy it into the Run prompt:

%APPDATA%\Skype \[Skype User Name]

On Mac OS X: Library/Application Support/Skype/[Skype User Name]

For future, you may want to consider SkyHistory

Answer 2

Writing your own SQL queries using e.g. SqliteBrowser to search the Skype database, allows for more flexibility and functionality than using a 3rd party Skype-specific tool.

For example, this query will search for a text string in all your chats, and display the (local) time the message was posted, the message itself, the name of the chat (if it has a name), and who is in the chat.

select DISTINCT datetime(m.timestamp, 'unixepoch', 'localtime') as postedon, c.displayname as chatname, m.from_dispname as fromuser, m.body_xml as msgtext
from Messages m
INNER JOIN Conversations c ON m.convo_id = c.id
where m.body_xml LIKE '%my text%' --case insensitive
order by m.timestamp DESC

Answer 3

Skyperious also might be worth checking out. It has a few capabilities over SkypeLogView, such as

• Import contacts from a CSV file to your Skype contacts
• View any database table and and export their data
• Change, add or delete data in any table
• Execute direct SQL queries
• Synchronize messages in two Skype databases: keep chat history up-to-date on different computers, or restore missing messages from older files into the current one
• Chat statistics

Answer 4

I recently found a nice online tool for browsing Skype history: http://www.skypebrowser.com

Seems to be the best solution if you're not concerned about privacy issues.

Answer 5

Following queries on main.db works for me:

Finds group chats

SELECT DISTINCT datetime(m.timestamp, 'unixepoch', 'localtime'), c.id, m.author, m.body_xml FROM
messages m
JOIN conversations c ON c.id = m.convo_id
WHERE c.type = 2 AND
m.body_xml NOT NULL
ORDER BY m.timestamp ASC

Finds private chat with your buddy

SELECT DISTINCT datetime(m.timestamp, 'unixepoch', 'localtime'), m.author, m.body_xml FROM
messages m
JOIN conversations c ON c.id = m.convo_id
WHERE m.body_xml NOT NULL AND
c.identity LIKE '%YOUR.BUDDY.NAME.HERE%' --case insensitive
ORDER BY m.timestamp ASC

In private chat with your buddy finds given word

SELECT DISTINCT datetime(m.timestamp, 'unixepoch', 'localtime'), m.author, m.body_xml FROM
messages m
JOIN conversations c ON c.id = m.convo_id
WHERE c.identity = 'YOUR.BUDDY.NAME.HERE' AND
m.body_xml NOT NULL AND
m.body_xml LIKE '%YOUR.SEARCHED.WORD.HERE%' --case insensitive
ORDER BY m.timestamp ASC

PS: sometimes your buddy name might be different than it is displayed in skype, so check this one first:

SELECT identity, displayname FROM conversations

and pick up one from identity column

Answer 6

SkyHistory did not work for me - seems like it was not designed for 68MB of skype logs : )

One of the most powerful approaches is a also very simple - just use SQLite client. Here I wrote a simple manual: http://jehy.ru/articles/2014/05/26/searching-through-skype-history/

If you have more then 300 contacts and they have overlapping conversations – you understand that it’s impossible to find anything with simple Ctrl+F approach.

Fortunately, Skype uses SQLite database and we can make a direct connect to it and search there directly. So, you need to:

1) Download SQLite client (i used http://sqlitebrowser.org/ but you can install any client you like) 2) Find your history file as it is decribed on skype web site:

Hold down the Windows key The Windows key on your keyboard, then press R to bring up the Run window. If you are using a touch screen device on Windows 8, you can bring up the Run window from the Search charm. Type %appdata%\Skype into the Run window and press Enter. Open the folder named after your Skype Name. Find the main.db file in the folder, this file is your chat history.

3) Use your SQLite client to open this file. 4) Open database table “messages” 5) Use any SQL queries you want to search for message you need. Here’s an example of me searching for “git” word in conversations – but there are many field that you can use for searching and ordering.