Analysis of Skype - Windows 10 App Version 12.7 and higher
Why this post?In my last post a gave an overview of the different version of Skype.
I had recently a case where I found relevant pictures in a folder that leads me to Skype.
After processing the data with Magnet Axiom (Version 3.3.1) I took a look into the decoded Skype chats if I can see if the pictures were sent/received and if I can see the corresponding Skype accounts.
Unfortunately there were no pictures shown in the Skype messages. Just messages like "account_a shared file_x with account_b".
And file_x was not a file name that could be found in the file system.
I was pretty sure there must be a way to map the pictures to the messages. There were over 400 pictures and most of them were relevant.
For this case it was important that I am able to tell if the pictures were sent out or "just" received/stored.
What I had at the beginning
- The system was a Windows 10 Home System.
- The installed Skype version was "14.40 Skype for Windows 10". But probably Skype was used for a long period of time. I could see usage from 2016 until mid of 2018. So the usage started with an older version.
- I found the relevant pictures in the following folder: %USERPROFILE%\AppData\Local\Packages\Microsoft.SkypeApp_[Version]\LocalState\[skype_user_name]\media_messaging\media_cache_v3
- I have a skype.db in the folder: %USERPROFILE%\AppData\Local\Packages\Microsoft.SkypeApp_[Version]\LocalState\[skype_user_name]
- And I have a cache_db.db in the folder: %USERPROFILE%\AppData\Local\Packages\Microsoft.SkypeApp_[Version]\LocalState\[skype_user_name]\media_messaging\media_cache_v3\asyncdb
The analysisA few months ago I read an article from Paul Sanderson about the media cache analysis of Skype. He did the research with his Forensic Browser for Sqlite.
Please find his report here: https://sandersonforensics.com/forum/content.php?223-Investigating-Skype-cloud-based-media_cache-image-sharing-with-the-Forensic-Browser-for-SQLite
Paul stated out, that the path to the files is stored in the field "serialized_data" in the table "assets" of the database file cache_db.db.
I checked this for my case and this is still the same. That is good, no changes in the cache_db.db
The problem is that the mapping to the skype.db, where the messages are stored, is not possible in the same way like Paul showed it for the old Skype version with the main.db.
So I needed to find the new possible correlation between those two database.
First I will give a short overview of the relevant content in the two databases
The cache_db.db file
In this database there is the table "assets" with the fields key and serialized_data (also other fields but not interesting for this analysis).
When I read "key", I hope I can work with it to match it to the other database in any way.
The content of the fields are like:
The skype.db file
In this database is a table named "messages" that contains the messages sent and received by the Skype User.
In the field "content" I see the content of the message. If a picture was received or sent this field contains a XML-like--structure with a value for the key "url".
This value looks very similar to the value in the field "key" of the table "assets" in the database cache_db.db.
Example of content field when a picture is shared:
Now I have an idea how to map the messages to the pictures.
I checked it it for one picture and one message manual. The creation timestamp of the message and the created timestamp of the picture were almost identical (just about 2 seconds separated). I tested it for another picture, and the same.
The two seconds separation could be there because the message is sent and then the picture needs time to be downloaded onto the machine.
So I decided to write a script that gives me a list with all filenames in the media_cache_v3 folder and their status about sent or received, which skype accounts and creation timestamp of the message.
I am sure I could have done all of this in SQL or with a complete tool but my SQL knowledge is not the best and I don'T have a tool. So I decided to use Python Version 3.
This script can be found in my GitHub repository https://github.com/kalink0/useful_scripts/blob/master/skype/analyzeSkypeApp.py. At the moment of writing this post one needs to insert the path to the Skype profile folder into the code for the variable "base_dir".
I will change the script asap in the way that one can call it with a parameter or the script will ask for the path.
Result/ConclusionWith this research and the script I am able to tell if a picture in the folder media_messaging/media_cache_v3 was sent or received and also by whom.
If you have a system you analyze with relevant pictures in the media_messaging/media_cache_v3 folder of Skype, you now know how you can perhaps map this pictures to the Skype chats.