«  
  »
Linux & Open Source ● Mobility

iPhone Call History Database

Published: Thu, 22 Dec 2011 19:32:59 +0000 Updated: Thu, 22 Dec 2011 19:32:59 +0000 | 18 comments

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:

Files path inside the iPhone and their counterparts on an iTunes backup folder
Filename inside iTunes backup folder Original iPhone file name Description
2b2b0084a1bc3a5ac8c27afdf14afb42c61a19ca /var/wireless/Library/CallHistory/call_history.db
 Call log SQLite DB
31bb7ba8914766d4ba40d6dfb6113c8b614be442 /var/mobile/Library/AddressBook/AddressBook.sqlitedb Contacts (address book) SQLite DB

iTunes backup folder on Windows can be found at C:\Users\[USER]\AppData\Roaming\Apple Computer\MobileSync\Backup\[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 ‘*’):

5 records example from the call table
ROWID address date duration flags id name country_code network_code
6795 +5511960***** 1324488873 0 5 405 724 10
6796 +5511754***** 1324491693 53 5 441 724 10
6797 some.name@email.com 1324491793 18 20 -1 724 10
6798 011960***** 1324495100 159 4 -1 724 10
6799 011960***** 1324503332 27 4 -1 724 10

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:

Call flags
Decimal Hexadecimal Binary representation Meaning
0 0 0 Incoming call flag
1 1 1 Outgoing call flag
4 4 100 Regular call
8 8 1000 Very rare and probably similar to 0×4 above
16 10 1 0000 FaceTime call
65536 10000 1 0000 0000 0000 0000 No network flag
131072 20000 10 0000 0000 0000 0000 Some kind of error ???
262144 40000 100 0000 0000 0000 0000 Some kind of error ???
524288 80000 1000 0000 0000 0000 0000 Some kind of error ???
1048576 100000 1 0000 0000 0000 0000 0000 Dropped Due to Network Problems flag

Probably due to performance reasons, the call table only contains the last 100 records 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

Desired Result Query
Almost raw call history but with date field converted to my timezone (-3 hours or -10800 seconds) and presented in a more human friendly way 
select
   rowid,
   address,
   strftime('%Y-%m-%d %H:%M:%S',date-10800,'unixepoch'),
   duration,
   flags,
   id
from call;
Same thing but show only calls between 2011-10-04 and 2011-11-04 
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');
Show only outgoing calls 
select * from call where flags&1=1;
Show only incoming calls 
select * from call where flags&1=0;
Show only outgoing calls that were actually answered 
select * from call where duration>0 and flags&1=1;
Show only FaceTime calls 
select * from call where flags&16=16;
Show only incoming FaceTime calls that where actually answered 
select * from call where flags&(16|1)=(16|0) and duration>0;
Show only outgoing calls that were dropped due to some network problem 
select * from call where flags&(1|1048576)=(1|1048576);
Show contact name along with the call record using the Address Book database 
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;
By Avi on Thu, 22 Dec 2011 19:32:59 +0000 | Categories: Linux & Open Source Mobility

18 comments to iPhone Call History Database

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>