iPhone Call History Database

Either if you are doing forensics or just want better reports about your call patterns, the iPhone Call History database can be very handfull.

If you have a jailbroken iPhone, you can access the database file directly. If you are not, you can still access it offline simply copying the file from an unencrypted iTunes backup to some other folder on you computer to manipulate it. Here are the real files path inside the iPhone and their counterparts on an iTunes backup folder:

iTunes backup folder on Windows can be found at C:Users[USER]AppDataRoamingApple ComputerMobileSyncBackup[UDID] where [USER] and [UDID] are your user name and the unique device identifier respectively.

These files are SQLite databases and can be viewed and manipulated with SQL commands as long as you have the sqlite3 program. On the iPhone, you can install sqlite3 package from Cydia. On a Mac or Linux, the sqlite3 command line tool is already there. And on all platforms you can install the excellent SQLite Manager extension for Firefox to transform your browser into a powerfull SQL studio.

So using the sqlite3 command line tool can be like that:

$ sqlite3 /var/wireless/Library/CallHistory/call_history.db
sqlite> .tables
_SqliteDatabaseProperties  data
call                       properties
sqlite> .headers on
sqlite> select * from call limit 5;

The last SELECT query gives us a highlight on the call table layout. Results is something like this (I obfuscated some information with ‘*’):

The meaning of each column:

ROWID

An internal autoincrement integer and primary key for each call record.

address

Phone number or FaceTime ID of caller or whom you have called.

date

Date and time in UTC (Greenwich time, not localtime) when the call happened in UNIX Time. This is actually the number of seconds since midnight 1/1/1970.

duration

The call duration in seconds.

flags

Denotes incoming or outgoing, FaceTime, call status and other aspects. See details below. 

id

Contacts or Address Book ID of the person being called. This column will have values different from -1 (-1 means unknown contact) if the call was originated from a registered contact on the address book. This contact ID can be matched to the AddressBook database, ABPerson table, ROWID column. Incoming calls from known contacts/numbers will also have -1 here and the real contact can be found with a not-so-simple SQL query comparing the address field with the corresponding field on the Address Book database.

name

This columns is always empty.

country_code and network_code

This is the Mobile Country Code (MCC) and Mobile Network Code (MNC) of the operator being used by the call and can be used to roughly identify if the user was roaming. Looking at Wikipedia reference for the example values, the 724 and 10 codes are for Vivo S.A. in Brazil.

The flags value is one of the most important to provide insights about the call. There is no documentation about it so I had to reverse engineer its values and hope you value my findings. This value is actually a bitwise OR of multiple flags as follows:

Probably due to performance reasons, the call table only contains the last 100 records on iOS up to 6 and last 200 records on iOS 7 or newer, so if you don’t save your older records somewhere else, you’ll never be able to have them back.

Query examples on Call History database

select
   rowid,
   address,
   strftime('%Y-%m-%d %H:%M:%S',date-10800,'unixepoch'),
   duration,
   flags,
   id
from call;

select
   rowid,
   address,
   strftime('%Y-%m-%d %H:%M:%S',date-10800,'unixepoch'),
   duration,
   flags,
   id
from call
where strftime('%Y-%m-%d',date-10800,'unixepoch') BETWEEN
   date('2011-10-04') AND date('2011-11-04');

select * from call where flags&1=1;

select * from call where flags&1=0;

select * from call where duration>0 and flags&1=1;

select * from call where flags&16=16;

select * from call where flags&(16|1)=(16|0) and duration>0;

select * from call where flags&(1|1048576)=(1|1048576);

attach '/var/mobile/Library/AddressBook/AddressBook.sqlitedb' as ab;
select
   call.rowid,
   address,
   strftime('%Y-%m-%d %H:%M:%S',date-10800,'unixepoch'),
   duration,
   flags,
   id,
   abp.first,
   abp.last
from call,ab.ABPerson as abp
where id=abp.rowid;