Finding Discord chats in OS X

After much searching the Discord cache folder is located here:

/Users/myusername/library/Application Support/discord

The cache folder follows the same file structure as the one found in Windows.

Discord cache folder in OS X

The following links will explain how to extract the json chat objects and how to convert them to html or xls files using a Python script. Although written originally for objects found in Windows the extraction and conversions steps apply all the same.

Extraction of json objects:

 https://abrignoni.blogspot.com/2018/03/finding-discord-app-chats-in-windows.html

Discord json chats conversion to html or xls.

https://github.com/abrignoni/Discord-JSON-chat-conversions

As background on how I located the correct folder I took the following steps:

1. Created a virtual OS X using Virtual Box. Virtual storage was VDMK format.
2. Installed the Discord program.
3. Logged in to my test account. The chats were synchronized from the ones in Discord servers.
4. Turned off the virtual machine. Created a snapshot.
5. Tried to process the snapshot with Autopsy. Wouldn't take it.
6. Made a clone of the machine in order to consolidate the snapshot and the image into one. Autopsy still wouldn't take it.
7. Installed Qemu. Converted the VMDK to RAW using the following command:
   
   quemu-img.exe convert -f vmdk 'J:\my-clone-mac-disk.vmdk' -O raw my-mac-disk.raw
8. Processed the raw file with Autopsy. In the keyword search section I added some of my test chats content and some other obvious terms like 'discord'.
9. Looked at the keyword search results. These lead me to the proper folder location described above.

At the end of the day I not only found what I was looking for but also learned about OS X folder structures and how to manipulate virtual machines. Everything you do is an opportunity to learn and share with others.

How to convert UNIX Epoch timestamps in SQLite DB fields to local time.

Short answer:
 select mychatfield, datetime(mytimefield/1000, 'unixepoch', 'localtime') from mytable

Detailed answer:
Most SQLite databases used in Android applications store their time stamps in UNIX time, also know as UNIX Epoch. Unix time defines a point in time as "the number of seconds that have elapsed since 00:00:00 Coordinated Universasl Time (UTC), Thursday, 1 January 1970, minus the number of leap seconds that have taken place since then." It goes without saying that these date are not stored in a way that makes them understandable to our consumers.

If your forensic tool does not parse a particular SQLite DB content automatically here is a quick way of converting those UNIX time stamps to local time.

For this example I will use an Tumblr Android SQLite DB that was extracted using Magnet Forensics Acquire and FTK Imager. To view the contents I used DB Browser for SQLite. The path location of the extracted database in my exemplar phone was:

userdata (ExtX)/Root/data/com.tumblr/databases/Tumblr.sqlite

Here is how the table looks with some sample data using DB Browser for SQLite.

Notice the timestamp field. A long string of numbers. Also notice the text field. The user content we are looking for if we are interested in chat content, for example. In order to present the chats with the corresponding time stamps in a human readable way we can use a SQL query to make the conversion.

Here is the query and the results:

select text as messages, datetime(timestamp/1000, 'unixepoch', 'localtime') as dates from messaging_message 

The datetime function takes three arguments in this example. The time string, UNIX epoch and local time modifiers. The reason the timestamp field values are divided by 1000 is due to the UNIX time being stored in milliseconds when the datetime function expects the UNIX time to be in seconds. By dividing we change the milliseconds to seconds.

In order to make the final column headers more descriptive change them in the query by using 'myfielname as newname' as seen above.

As always when doing these types of conversions on a case run validation tests with known data in a replica of the environment you are analyzing.

Discord JSON chats to XLS

Just added a script to convert the JSON chats to XLS spreadsheets.

Hopefully will be able to add batch functionality for all files in a folder as well as output selection all in one script by the end of the week.

• https://github.com/abrignoni/Discord-JSON-chat-conversions

@alexisbrignoni

Update 0:

Batch functionality and output selection done.

Update 1:

Added error handling via screen and error.txt file for chats that are unable to be converted to XLS.

Finding Discord app chats in Windows.

Discord on the desktop
In previous posts I discussed some ways of recovering and presenting Discord app chats from Android devices. This post will discuss how to find Discord chats in Windows machines and provide a simple way to visualize them.

As way of background Discord is a chat application whose target audience is people who play video games. Wikipedia states that the Discord app has 87 million unique users. With such a large user base it is interesting to find that current commercial tools do not parse Discord chat artifacts directly yet.

Location and Extraction
Like many Windows applications, the user generated files and configurations reside in the apps folder. For example:

C:\Users\SampleUser\AppData\Roaming\discord

The Discord AppData folder has the following structure:

Cache folder highlighted

The user activity files are located in the Cache folder. At first glance the chat files we are looking for are not immediately apparent.

Where are the chats?

A look at the Cache folder contents might seem familiar. It is the same file format of the Google Chrome cache located at

C:\Users\SampleUser\AppData\Local\Google\Chrome\User Data\Default\Cache

My default browser is Chrome and the Discord app uses the same storage structure. For comparison here is my Chrome cache folder view.

Same file structure

Since the file structures are the same it seem clear that the content we are looking for had to reside within the Chrome cache like folder structures in the Discord cache folder. Thankfully there are many tools that allow us to parse those structures and extract files from them. For this analysis I used the folowing tool:

ChromeCacheView v1.77 - Cache viewer for Google Chrome Web browser Copyright (c) 2008 - 2018 Nir Sofer

The tool will by default parse the Chrome cache at the default folder location in your computer. Just hit stop on the upper left corner and point the tool to the Discord cache folder. After processing the contents of the folder the tool will show something similar to the following:

Discord cache folder contents

The tool will allow you to print out all the metadata on screen to an HTML file. Even more useful is the ability to extract the actual files from the cache by selecting an entry in the list and pressing F4 (or via the menu.)

To find the chat files look in the URL column for addresses that end with "messages?limit=50".

messages?limit=50

These are the files we will export from the cache and will contain the chat messages. It is of note that they not always end in 50. In some cases they can end in 100. Also note that the name of these files is usually 50 as stated in the URL variable but that is no always the case. Some chat names start with the words After or Before followed by some sort of numeric ID. Hence the best way to identify them is to go by the ending of the URL column.

By clicking on file entry in the list one can see the pertinent metadata. It states that the files we are exporting are JSON files.

Content Type: application/json

Since they are JSON files they can be viewed in any regular file viewer. After exporting here is how one looks using Wordpad.

Content key is key

The formatting is hard to the eyes but understandable. Notice the content key, the value is the user generated chat. Each user generated block starts with the attachment key and continues with keys for multiple time stamps, user IDs, the chat content and the like.

In order to make it a little easier to read the values, the following script takes the json file contents and presents them as a collection of html  tables.

A little better

The best way to look at these files is to use Chrome since other browsers do not know how to decode certain characters, like emoji.

Look!!! Emojis!!!

The script is really simple and can be found here:

• https://github.com/abrignoni/Discord-JSON-chat-conversions

Script uses the json2html module that does the actual heavy lifting. It can be found here:

• https://github.com/softvar/json2html-flask

Pending will be the capability for the script to parse a group of chats in a folder instead of one by one.

I can be reached via twitter @alexisbrignoni

PD:

One can log into a target Discord account without the username and password by installing the Discord application on our forensic computer and then copying over the Discord app data folders from the examined computer. The program will require internet access for this to work. It goes without saying that consent to search or a search warrant is needed before attempting this type of access. 

-Brigs

Organization of American States - 37th Cybercrime Regional Workshop

Beautiful Guatemala City. 

What a great experience training and exchanging views with prosecutors from Costa Rica, the Dominican Republic, El Salvador, Guatemala, Honduras, Mexico and Panama in REMJA’s Cybercrime Regional Workshop.

Hope to be back soon...

Antigua Guatemala.
Worth visiting. Great local art and plenty of interesting history.

OEA flag and seals.

Welcoming ceremony. Event organized by the always
amazing @fiorella_mh

Lecture and case studies.

Cybercrime discussion with @TheJusticeDept prosecutors and
@OEA_Justicia subject matter experts.

@fiorella_mh

Honored to be able to speak on
mobile technology, social media, and dark markets

Dinner with faculty.