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 |
|---|---|---|
| 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 ‘*’):
| 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:
| 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;
|
how do you view the call history database using the SQLite extension for Firefox?
thanks,
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.
Hi Avi, any success in accessing call_history.db under iOS 5? Thanks!
This informations is great
Thomsen the Dane
Great information, thanks!
Is there a way to retrieve more than just 100 recent calls?
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.
I would like to access call_history.db in iOS 5. Anybody knows how to do that? Other ways to access this?
Nice thx 😉
What about viewing SMS history database? What would the query be like? And would I be able to recover deleted SMS?
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.
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
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 !
This page indicates that flag 8 is a blocked call:
http://code.google.com/p/iphonelogd/wiki/CallHistoryDatabase
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
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.
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!
Very nice, detailed post. Are these files read-only or possible to edit through SQLite?
Many thanks
These files are SQLite databases that can be edited with any tool that can edit SQLite databases.
doesnt seem to work on ios 7
how can I find deleted call history in iPhone backup file?
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
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
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?
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.
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)
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).