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:
|Filename inside iTunes backup folder||Original iPhone file name||Description|
||Call log SQLite DB|
|31bb7ba8914766d4ba40d6dfb6113c8b614be442||/var/mobile/Library/AddressBook/AddressBook.sqlitedb||Contacts (address book) SQLite DB|
|3d0d7e5fb2ce288813306e4d4636395e047a3d28||/var/mobile/Library/SMS/sms.db||SMS sent and received, including deleted messages|
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 ‘*’):
The meaning of each column:
- An internal autoincrement integer and primary key for each call record.
- Phone number or FaceTime ID of caller or whom you have called.
- 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.
- The call duration in seconds.
- Denotes incoming or outgoing, FaceTime, call status and other aspects. See details below.
- 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.
- 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:
|0||0||0||Incoming call flag|
|1||1||1||Outgoing call flag|
|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
|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;