18

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?

24

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

  • 2
    SkypeLogView - helps me, really nice tool. – coms Oct 10 '12 at 15:48
  • 3
    SkyHistory still works with recent Skype. Thanks! – Vadzim May 20 '14 at 13:52
  • Thanks for the links to 3rd party tools. I find that search in Skype itself is lame - some older time periods in the chat sometimes are collapsed and Skype ignores them when searching. I have to scroll through the chat first and expand all collapsed periods and only then I can find some text which was previously hidden in one of those collapsed periods. – JustAMartin Dec 3 '15 at 13:00
  • 1
    Consider replacing [USER NAME] with %USERNAME%, so that your path becomes copy/pastable. Also, "Skype name" was ambiguous to me (thought it was Skype's version or something O_o), I 'd update to "Skype username" – Balmipour Jan 5 '17 at 16:41
7

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
  • this isn't working (0 rows returned) on my current Skype version (7.33.0.105) – Facundo Colombier Mar 28 '17 at 13:13
  • @Facundo: I just tried it again with 7.34.0.102 and it still works for me. Make sure that the "%my text%" line is changed to something you're searching for (or comment out that line). – twasbrillig Mar 29 '17 at 9:54
  • @Facundo: I found my mistake; I was joining with the wrong table. Please try it again with the updated script above. – twasbrillig Mar 26 '18 at 19:11
6

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
  • I just used Skyperious again to search for a word that I knew I used in a conversation years ago, but I forgot the person I was talking to. That would have taken ages to do if I attempted to click on each individual name and search the term. Thank God. – Jon Grah Dec 11 '16 at 15:13
3

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.

1

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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.