Calllog.db and SMS data on Android 7.0 Nougat

A few weeks ago, Jamie McQuaid at Magnet Forensics wrote an interesting article titled Android Messaging Forensics – SMS/MMS and Beyond. The article is a great overview of the different databases that Android uses to store SMS/MMS data.

Based on my recent findings, I think that another database should be mentioned: calllog.db. During the analysis of two Samsung smartphones (SM-G935F and SM-G930F) running Android 7.0, I've found out that calllog.db also contains SMS data.

I was able to find and analyze this db because I had created a physical dump of the two non-rooted smartphones by using UFED 4PC. I haven't verified if the file can be obtained with other methods like a ADB backup.

This sqlite database can be found here:

\data\com.android.providers.contacts\databases\calllog.db

The table calls within the database has a column named m_content. If the length of this field is greater than 0, it means that the record contains a text message (if zero, it's a phone call log).

At a quick glance, the most relevant fields in the table are:

• number: sender's or recipient's phone number;
• date: message date;
• type: its value indicates if it's a sent or received message; 
• name: contact name associated with the phone number;
• last_modified: message date (it usually matches the date of the field date);
• m_content: first 50 characters of SMS message body.

This is a quick query to view the contents of these fields:

SELECT number,datetime(date/1000,"unixepoch","utc") AS date,type,name,datetime(last_modified/1000,"unixepoch","utc") AS last_modified,m_content FROM calls WHERE m_content <>""

If I run the query SELECT DISTINCT type FROM calls WHERE m_content <>"", I can see that the field type has value "1" or "2". After some comparison with other messages, I deduced that "1" means "received" and "2" is "sent".

As of writing, by default the tools UFED Physical Analyzer (7.9.0.223) and Magnet AXIOM (2.5.1.11408 - with custom artifact plugin) only extract call logs from calllog.db.

This is how I adapted the tools to my needs.

UFED Physical Analyzer

By using the internal tool SQLite Wizard within the Physical Analyzer, I created a query to do the parsing:

Select calls.number,
  calls.date,
  calls.type,
  calls.name,
  calls.last_modified,
  calls.m_content
From calls
Where Length(calls.m_content) > 0

I then mapped the fields by drag&drop and customized the conditions of the field type.

I ran the query and successfully retrieved 500 additional SMS records that I added to my report.

The table shows the big difference in the number of SMS found before and after parsing the file calllog.db.

(before)

(after)

Some messages were duplicates, but many others were not. This is an example sorted by message body:

While creating the query, I tried with and without the "include deleted rows" option. In the end I decided to keep it unticked since I was getting too many false results.

Anyway you can download both query versions from here.

How to import and run the query: Physical Analyzer | Tools | SQLite Wizard | Open SQLite query manager | Import | select the query file to use | Run.


Magnet Axiom

When creating a new case with Magnet AXIOM Process, I recommend to use the Dynamic App Finder ("Find more artifacts" turned ON). It's a useful feature that allows to discover additional databases that may contain relevant data. Once the search is complete, a window pops up asking to select any of the found databases and to map the needed fields.

When done, click on "Save selected artifacts". Magnet AXIOM Examine will show these custom artifacts under the category "Custom".

My custom artifact can be downloaded from here. Just import it from the menu in Magnet AXIOM Process (Tools | Manage custom artifacts | Add new custom articact) or simply copy the file to the path "C:\Program Files\Magnet Forensics\Magnet AXIOM\AXIOM Process\plugins".