Procedure to extract iPhone SMS logs from a MAC

[astrlsrfr 1]

If someone could move this to the right forum, please...

For use as "evidence of Relationship', you may want to get a dump of your iPhone's SMS logs. We used IM mainly in video mode, so we actually had more text in SMS that in IM.

There are tools you can buy to do this. This is how to do it for free with a little elbow grease. I found scattered instructions on how to access the logs, but not the whole process. So.. here is what worked for me. No guarantees, but hope it helps someone.

(** = commentary, & not a command)

**Backup your iPhone in iTunes: right click your device & select 'Back Up'

**Open Terminal (from Applications) & issue the following commands:

** in the following command, replace [username] with your username

cd /Users/[username]/Library/Application Support/MobileSync/Backup/3402aa514e7302dbf18a2649fca1243427ac450e

sqlite3 3d0d7e5fb2ce288813306e4d4636395e047a3d28 .dump > sms.db.txt

sqlite3

** you are now in the sqlite3 db tool

.read ./sms.db.txt

.output smsLog.txt

.separator '%'

** see below for details about this query

SELECT CASE WHEN flags=2 THEN 'FROM' ELSE 'TO ' END, address, datetime(date,'unixepoch','localtime'), text FROM message WHERE address like '84%' OR address like '+84%';

**open smsLog.txt in Excel or openOffice Calc & edit / ready for printing (it will be in the same path you cd's to access the logs). In the spreadsheet tool, you need to define the field separator as '%' when inhaling the file - it should automatically prompt you for this. Any newlines (Carriage returns) in your SMS texts will be read as new lines, so you will need to manually edit those lines.

For the SELECT statement.. This is basic SQL syntax. If you don't know anything about SQL & don't care to research it.. you're still ok. Note that my example matches 2 possible wildcards on the country code 84 (Vietnam). In some cases, my phone used the +, in others it didn't, so I needed to do it this way. You can also search for exact #s like so:

SELECT CASE WHEN flags=2 THEN 'FROM' ELSE 'TO ' END, address, datetime(date,'unixepoch','localtime'), text FROM message WHERE address ='8885551212';

Edited May 30, 2011 by astrlsrfr