#ROOT DB SQLITE BROWSER HOW TO#
#ROOT DB SQLITE BROWSER INSTALL#
#ROOT DB SQLITE BROWSER FULL#
#ROOT DB SQLITE BROWSER ANDROID#
#ROOT DB SQLITE BROWSER PC#

ZWACHATSESSION: information about your contacts and groups ➣ ZMESSAGECOUNTER: number of messages exchanged with this contact/group ➣ ZSESSIONTYPE: 0 if it is a private message to/from a contact, 1 if it is a group, 2 for broadcast and 3 for status.

➣ data: text of the message (null for multimedia messages without a text) ➣ key_remote_jid: identifier of the remote part (the sender if the message was received or the receiver if the message was sent)

messages ➣ key_from_me: 0 for incoming messages, 1 for outgoing ➣ media_wa_type: whether the message is text (0), an image (1), a video (2), a voice message (3)… ➣ timestamp: date and time when the message was sent or received in UNIX time format.

chat_view: chat sessions ➣ raw_string_jid: identifier of the contact/group ➣ last_message_row_id: FK of the last message from that chat in messages ➣ sort_timestamp: date of the last message from that contact/group.

wa_contacts: information about your contacts and groups ➣ unseen_msg_count: number of messages without reading from that contact/group ➣ jid: identifier of the contact or group: contacts will end with “ and groups with “ ➣ display_name: name of the contact or group.

Most of them will not be relevant for us, so let’s see which tables and columns we are going to work with.

#ROOT DB SQLITE BROWSER FULL#

The WhatsApp database is full of tables, some of them with myriads of columns and rows. Just search for it in the backup folder, copy it to your working directory and rename it as you wish, for example, whatsapp.db. The command above will give you the name of the database. dump > whatsapp.sql sqlite3 whatsapp.db | grep ChatStorage.sqlite | awk ‘’

#ROOT DB SQLITE BROWSER INSTALL#

In case you have your phone rooted, you have to install adb following these steps, and then connect your mobile phone with a USB cable and run the commands inside your working directory: adb root adb shell sqlite3 wa.db “.backup ‘wa.db.bak’” adb pull /data/data/com.whatsapp/databases/msgstore.db adb pull /data/data/com.whatsapp/databases/wa.db.bak wa.db sqlite3 msgstore.db. There are some alternative ways to get it, but at the end all require root access. The WhatsApp database is stored in a location of the filesystem with restricted access, so you will need special permissions to be able to get it.

#ROOT DB SQLITE BROWSER ANDROID#

If you have an Android phone, I am sorry to tell you that you need a rooted device. Extracting the WhatsApp database From an Android phone

Docker installed on your computer to deploy Redash or any other dashboarding toolĪre you ready to join me? Let’s go! #1.

gcc and make installed on your computer to compile the backup extractor (unless you have a Mac, because the tool is already compiled for macOS).

sqlite3 installed on your computer to work with the database.

#ROOT DB SQLITE BROWSER PC#

adb if your phone is an Android, or a Windows PC with iTunes or a Mac if it is an iPhone.A USB cable to connect the phone to your computer.A rooted Android phone or an iPhone (no need to be jailbroken).If you want to try it yourself, you will need a couple of things:

#ROOT DB SQLITE BROWSER HOW TO#

How to create an interactive dashboard to visualize those KPIs –we will use Redash, but feel free to choose any other–.

The SQL queries to get the most interesting KPIs about our usage of WhatsApp.

How to get the relevant data for this experiment from the raw database.

How to copy your WhatsApp database from your iPhone or Android phone to your computer.

It has taken me a lot of time, but now I feel very proud of the results, and I am really excited about sharing them with you. It was not an easy process, since you need to somehow copy that database from your phone to your computer, then understand it, and once you have understood how it is structured, think on what useful information can be extracted and how to present it. Photo by LinkedIn Sales Solutions on Unsplash

Root db sqlite browser