How do I extract my iPhone SMS logs as plain text, or XML, or anything else greppable/archivable on my desktop computer?
Update: Ok, I wrote this: sms-backup-iphone.pl.
Where's my iPhone SMS logs at?
Dear Lazyweb,
Tags: computers, firstperson, lazyweb, mac, phones
Current Music: Mystic Knights of the Cobra -- Text Messaging Slut ♬
32 Responses:
I'm sure there's an app for that.
And I have a ban-hammer for people like you.
You have to jailbreak the phone, as far as I know.
I just switched to a Nexus One and went through the pain of jailbreaking my old phone. Then fire up iPhone Explorer (or ssh in), it's located at /var/mobile/Library/SMS/sms.db.
It is a SQLlite DB, easily exportable.
http://lifehacker.com/399631/export-iphone-text-messages-to-a-spreadsheet is most of what I used.
Well, I had it jailbroken for a while, and it made it very unstable. So that's not gonna work for me.
Looks like there's a bunch of shit under "Library/Application Support/MobileSync/Backup/" but it looks like a bunch of randomly-named SQL databases containing opaque binary data.
No, it's just sqlite. Just find the file named 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata and dump it:
$ sqlite3 -csv -separator , 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata "select * from message;"
....or you could do that.
Jesus I wish I'd known that earlier. I wasted so much fucking time jailbreaking my damn phone :(
Sweet, I can work with that.
What's that 3d0d* number the hash of? It's 8 bytes too long to be an MD5...
It's probably a SHA1 of the database name or something.
0 ~% printf 'HomeDomain-Library/SMS/sms.db' | sha1
3d0d7e5fb2ce288813306e4d4636395e047a3d28
Possibly also worth knowing, it appears for a given sha1 hash there are two files, FOO.mdinfo and FOO.mddata. FOO.mdinfo appears to be a binary encoded property list which includes, amongst other things, the path name the file came from, so using strings and grep is a brute force way to identify which sha1 hashes you care about. FOO.mddata appears to be in whatever format is native to the iPhone (eg, JPEG files for camera pictures, SQLite databases, some more binary encoded property lists, etc).
mdhelper (OS X binary) (source on github) is one of the programs to decode this combination into actual filenames, presumably by extracting the filenames from the FOO.mdinfo files. (Found via stackoverflow discussion thread; there's also iPhone Backup Extractor that someone mentioned earlier, but from what I can see there's no source and it's GUI only; the older iPhone Backup Decoder python script seems to expect a different backup format from what iTunes 9 creates since it has been unmaintained since iTunes8 -- the author wrote the GUI/no source one instead.) AFAICT you'd still need to decode the data yourself, but SQLite files should be easy as someone else already pointed out.
Ewen
I bet this was intended to make the DMCA apply somehow.
Thank you, much appreciated!
There's also the iPhone Backup Extractor which will take those Backups and turn them back into the files as they exist on the iPhone. Use that, and then poke at the
Library/SMS/sms.db
sqllite database.
Part of the problem is that the jail isolates you out of /var, I believe. Which sucks - it should be totally trivial to write an app for this (and it'd be a totally legit purpose), but I doubt Apple would ever approve such an app. Theoretically you might be able to cobble something together and install it to your phone using a self-signed app cert, but I've not done that (and I've abandoned learning Objective-C because it makes my eyes hurt).
I tried finding documentation on the local backups on the computer side, to see if it's extractable on that side, but it appears to be jammed together with everything else.
As much as it sucks, depending on how often you need to export the SMS DB, if you -really- needed a PC copy you could sync the phone (doing a full backup), then jailbreak it, do the restore, copy the db, and then clean-restore it with the virgin image. Oh god I can't believe I just suggested that, that's like two hours straight in the toilet.
There's always the hammer, chisel, and electron microscope :/
I was going to suggest Disk for iPhone to access files, but that might not allow access to /var without jailbreaking either.
If you're referring to the SMS messages themselves, have you seen Phoneview (http://www.ecamm.com/mac/phoneview/)?
Seconded, I use PhoneView for this.
Thirded, I recently and painlessly extracted a good number of SMSs with this app. All of the other features are great too.
Missing Sync for iPhone claims to do this: "Archive text messages and recent call history to your Mac where they can be viewed, searched and sorted. Export a CSV file to use in other documents, like Excel, too."
Archiving voicemail is also pretty handy, even if you can't grep it yet.
Given JWZ's (and others, from what I've heard) well known and extreme dissatisfaction with Missing Sync, I'd guessing this is unlikely to be satisfactory.
Missing Sync is the reason that I was not a WebOS early adopter, in fact. Good thing, too, the Droid seems to be the least annoying smartphone out there (at least, if you are annoyed by on-screen keyboards).
I also enjoy my Droid, but I find I actually use the onscreen keyboard more than I ever use the hardware keyboard. Even moreso after getting Better Keyboard.
Once again your "current music for this post" wins three internets
It probably reflects on the type of people we've been working with that my first thought was "Cellebrite?"
Looks like you found a much easier answer.
I just wish there was a setting to show the timestamp of received SMSs. I find it highly annoying when I text someone, and ages later realize I have a reply, and have no way of knowing when the reply actually showed up. Not being glued to my phone, I often don't realize when a response comes in.
I haven't had a chance to debug the script to try and figure out WHY, but this script doesn't seem to want to extract SMSes received before 2010. If I run this script verbosely with an empty "SMS Backup" directory I get lots of these:
sms-backup-iphone.pl: didn't write old file 2008-08.617XXXXXXX.txt (25 of 25 lines)
Anything from 2010 works just fine. I'll try and figure out what's going on a bit later today.
Oh, yeah, as a sanity-check it won't write any files older than a couple months (this is left over from this script's original incarnation as a Palm SMS backup, because the Palm SMS db would often get corrupted and just make up random years-old dates.) Set $old_p=0 at line 222 to turn off that check.
Cool, thanks!
For your visualization enjoyment: http://tinyurl.com/netsharc/sms-browser/ .. if you want to copy it all you need is the index.html and your own version of messages.js and addressbook.js , the rest is taken care of by Simile Timeline.
...apparently the answer is, "wait until jwz is annoyed enough that he writes a script that does it". Thanks--very useful!
Thanks for that script; it'll come in very handy.
Thanks, this is VERY helpful! Any insight into how I might change this to also retrieve call logs?
Cheers,
jw
That DB is "HomeDomain-Library/CallHistory/call_history.db", so:
sqlite3 ~/Library/Application\ Support/MobileSync/Backup/*/\
ff1324e6b949111b2fb449ecddb50c89c3699a78.mddata \
'select address, date, duration, flags, id from call'