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:

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
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 ‘*’):

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 0x4 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 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

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;

29 thoughts on “iPhone Call History Database”

  1. how do you view the call history database using the SQLite extension for Firefox?

    thanks,

    1. Have the SQLite Manager extension installed on Firefox, open it from Firefox menu Tools->SQLite Manager and from there open the database file you transfered from your iPhone. Simple as that.

  2. Great information, thanks!

    Is there a way to retrieve more than just 100 recent calls?

    1. No: this limitation is apparently hardcoded in the original Phone app. It will delete older records while new calls happen.

      Yes: since you are jailbroken, you can write a simple SQL script that will copy newer records to another database of your choice with infinite call log. This is actually what I do with my log so I can, from time to time, retrieve some personal longer term business intelligence from my records.

  3. I would like to access call_history.db in iOS 5. Anybody knows how to do that? Other ways to access this?

  4. What about viewing SMS history database? What would the query be like? And would I be able to recover deleted SMS?

    1. All SMSs are stored in a similar database. Different from the call history, they never get deleted automatically.

      But I don’t know about manually deleted messages. You will have to hack this database to find them or not.

  5. Hi im not much good at this tech stuff and am wondering why you would need all the above method to get to the history when its simply available on the phone? I see you say its not possible to access more than 100 recent calls (which seem available to me). Is it possible to change a setting or add code or something that will change this and resurrect deleted numbers that went over the 100 calls mark is the important for me? Or any way to retrieve by date etc im desperate for a number that dropped out of the 100 by 2 days

    1. Unless your iPhone is jailbroken and you saved records from your call history as I explained in the post, you won’t be able to get it from your phone. It is lost forever.
      It is also impossible to change some configuration to make it not delete the 101 record and above. It will be deleted from the main history and you will have to save it into another history before it gets deleted.

      However, you can ask your operator to send you a detailed billing showing all calls placed and received, which numbers and at what time. My operator sends me this in a regular basis, check with yours if they do it too.

      Good luck !

  6. dear,

    if i open the file with hex editor i can see all incoming calls include the deleted history. How could see the deleted call history with sqlite3

    Regards

    1. I don’t know. These are probably table records marked as DELETEd and just waiting for a SQLite “vacuum” command to be vanished for ever.
      To keep database sematics integrity, I really doubt there is a formal way in SQLite to undelete these records.
      So only an hex dump will give you those lost records.

  7. Curti muito o site, boa dica de consultas sql, estou usando o sqlite no firefox perfeito. Só queria perguntar como eu faco uma consulta por determinado numero, pra saber quantas ligaçoes tenho deste.

    Obrigado!

  8. Very nice, detailed post. Are these files read-only or possible to edit through SQLite?
    Many thanks

    1. These files are SQLite databases that can be edited with any tool that can edit SQLite databases.

  9. I need too…. how can I find deleted call history in iPhone backup file?

    If i edit with hex the file Call History, I can see a number called… but with DB browser for SQLite i don’t see this number, because are deleted… I need see the time and date… how?

    best regards

    1. Miq

      Did you manage to finally get the time and date of numbers called?
      I have exactly the same issue as yours so would be extrememely helpful if you suceeded somehow.
      Thanks.

      ML

  10. Hi, I found another database which it titled recents. I am unsure if it is emails, calls or texts. When opened in SQlite the table is named recents. It Lists the first two and the last two of each call, text or email with the dates. There is a column which has an address_hash. Are you aware of this file? The file is 75b12106910f0b106f64d72eb75397427884fd5a. Can you tell me if this is calls, texts or emails and whether they are outgoing or incoming?

  11. Hi,

    I am trying to access the call log in my iWatch app. Can I use your trick to copy the call_history.db file into our custom folder and then accessing it in an iPhone app. ?
    In short I want the call log via iPhone app without jailbreak.

    Thanks in advance.

  12. Some flags discovered (iPhone 4):

    flag 0, duration > 0 => Incoming call
    flag 0, duration = 0 => Missed call

    flag 1, duration > 0 => Outgoing call
    flag 1, duration = 0 => Not answered outgoing call

    flag 65537, duration = 0 => Unable to place call (network problem)

    flag 1507328, duration > 0 => Dropped incoming call (network problem)
    flag 1507329, duration > 0 => Dropped outgoing call (network problem)

  13. does the call history db on for example a Macbook Pro show which iPhone was used to make the call (i can see a Device ID column in the db but the values are all Null).

Leave a Reply

Your email address will not be published. Required fields are marked *