First off, WOW! and THANKS! to all my readers who posted comments or information on today’s breaking stories about the consolidated.db and its information. I have seen a ton of new hits on the blog today as well as a lot of feedback in comments and e-mails. I want to take a minute to touch on a few things and give some final thoughts on this database while the story is still hot.
Yes, I did publish a post about the consolidated.db back in September 2010. Originally I had a thought on what this database was holding back in August, decided to put it off due to not time to do more research, then finally made a post in September. Having only my own devices to work with as none of my case evidence had been upgraded to iOS 4.0 yet, I said my peace. Of course as fate would have it, the following weeks I was flooded with more and more iOS devices. I should have modified my original post sooner, but I honestly never thought it would take off the way it has! The post I made in February of 2010 still holds my thoughts but I’d like to make some more points about it to follow.
The consolidated.db is NOT by any means a BAD thing. It is also, not by any means, a real surprise to me. Apple commonly changes their TOS agreements and really, how often do we really read them. There’s a plethora of information that we look over because we’re, lets face it, in a hurry. The changes about the location gathering have been there quite some time. I have also not found any information to suggest that Apple or any other group is collecting this information from its users. The only information I believe is passed to Apple is when a user clicks an iAd which sends the information of what was clicked and in what zip code the device was. True identifying information is still witheld.
This database is also not available for the average user. To access this database a user will need to have a forensic tool, jailbreak their device, or access that backups stored on their computer. I would not, however, worry about anyone grabbing this information from a live running device or backup without a user’s knowledge.
On to the data! There are multiple tables within this database. Most importantly are the CellLocation and WifiLocation points. These are pretty self explanitory with the following results. I believe the CellLocation table will contain information pertaining to local cell towers the phone is communicated with. These points are gathered over time, but multiple points may be written to the database at once. [By the way I believe the Time/Date stamps are MAC time but I don't have one in front of me.] The reason I believe this is that after looking at a freshly wiped iPhone 3G which was running iOS 4.2.1 which didn’t leave a single building, had points from all over the town.
The WiFiLocation table had similar information: Time, Longitude, Latitude, etc. but also including Mac Addresses pertaining (I believe) to the network the device sees. This does not mean the device has to connect to the network to log it, simply to see it.
Now for law enforcement and other purposes the device can come in handy. Will it give you a 100% accurate GPS point with Date/Time? No. Will it give you real-time tracking data to track someone? No. Can it help you narrow down timeframes and locations of potential suspects or victims? Absolutely, if used properly.
There’s a lot more tables in the database too that we’re not mentioning. If I had to guess the tables with the addendums of “Harvest” attached would probably tie back to a location service in an app grabbing a point of data at the user’s request. I wish the researcher who figures out every nook and cranny of this database luck. Unfortunately, case loads and other schedules prevent me from giving this as much time as I would like.
As to the tool that was released today, if you can, use it! That’s why it was made! Some forensic tools also have the ability to look at this data built in. For instance, Cellebrite’s UFED Physical Analyzer 2.0 software has the ability to parse out these location points. If all goes well, a developer friend of mine is currently working to create a Python script that will parse these points directly from a File System dump of the device which will also work inside the UFED software converting the points to a .kml file to load into Google Earth.
Once again to all those who linked back to the blog or mentioned me in their news stories, I greatly appreciate it. I hope that I have helped to shed some light into a previously unknown area of the iOS file system. Why Apple does the things they do is beyond me, but for whatever reason they have included it, this forensic analyst thanks you.
To newcomers to the blog who have come on board from whatever source I hope you keep coming back. Expect more new and interesting information to come down the pipes as fast as I can bring it to you. If you have any comments, questions, or concerns, PLEASE feel free to post them in the comments or e-mail me christopher@csvance.com!
is there any Windows application, iPhone application to present this data ?
any consolidated.db to KML for example ?
thanks
Stay tuned to the blog. A python script is currently in the works to do this.
http://www.lacunae.org/iphone-location-consolidated-db-to-google-earth-kml
found that, cant do a thing with it, can we get a windows app?
This location data would be very usefull for a friend tracker app. The current friend tracking apps usually store there own location data and sometime use more power due to using the GPS. An app that just reads the consolidated.db file for location info would be very simple to impliment.
I would buy that for $1
The time/date stamps are seconds from January 1st, 2001, which Apple uses as their reference date in their developer APIs.
https://developer.apple.com/library/ios/#documentation/Cocoa/Reference/Foundation/Classes/NSDate_Class/Reference/Reference.html (select the dateWithTimeIntervalSinceReferenceDate: class method)
I coded a Java program that can get your consolidated.db to kml. (You
have to look up the table,the latitude and longitude in the database and set the variables in the code. The program then reads the table and stores the data into geo location points and then writes them down into a kml file. Additionally I wrote a method to reduce the datapoints if they are very close to each other which was necessary for me with my 120’000 points [google could not show them all at once])
With my data I just found out that I had been to greece, the U.S., Hawaii and the bermuda triangle but never to switzerland even though I live there. Does anyone know if there is some bias on the geo location data? My data is sort of useless like this. But the fact that they logged about 120’000 location horrifies me a bit.
Could you please share your java app?