I have recently started looking at the wealth of data that can be obtained from file system iPhone extractions; a lot of which has already been explored by Sarah Edwards in her iOS of Sauron presentation, and also recently in her post on the KnowledgeC database.

Based on that I decided to take a look at the powerlog PLSQL SQLite databases on a jailbroken iPhone running iOS 10.2. I would have to double check, but I don’t think this file will get exported from a standard backup, and as a result you’ll have to jail break the device to get at this file.

There is a lot of potential user interaction data that can be identified in the many tables of this database. Unfortunately, the database rotates through whilst the phone is powered and doesn’t appear to come out with a standard backup. This is definitely something to consider as the longer the device is on post time of interest, the more chance that the data may disappear.

The powerlog is stored as an SQLite database, and also gzipped into archives as well. I don’t think any tools currently pull out the data in this database automatically, but I’ve found Cellebrite’s Physical Analyser does a decent job of presenting the data (reporting on it is a different story). There also appears to be a WAL associated, but I need to do more reading up on understanding that (see Paul’s SQLite book for more info).

Many of the tables have a UTC timestamp, and PA will suggest a way to decode the data, which is great.

Unfortunately, PA doesn’t currently a) un-gzip the archives and b) combine the plsql files into one big timeline. Hopefully, they’ll be adding something like this in the future.

Because of this, I wrote a python script that will identify all of the tables in the database with the timestamp field and then print out all the data. From here, you can load it into Excel and sort/filter/highlight to your heart’s content.

If anyone has some test data and can play around with this to let me know how it goes that would be great. I’m sure Apple has messed with the data stored in here between iOS versions, so it’s entirely possible that the timestamps won’t decode with my current SQL queries.

As per what data might you find here:

• Camera usage
• Torch usage
• Bluetooth connections
• Wifi usage
• Some App usage
• Volume settings
• Audio output
• Lock screen

I’m sure there’s plenty more, but this is some of the data that has stood out to me as potentially useful.

One of the tables, screenstate, showed a pretty good rough timeline of what I did in a 30 minute period. Granted I set up the device, performed the action, and then acquired it very quickly, but I can imagine if you’re able to get the device very quickly after an incident it may help direct your investigation.

Is anyone else using the powerlog? If you are, I’d like to see what other people are finding!

You can get the script here.

PS: Ideally, I’d like a FOSS framework (maybe even in Autopsy!?) that can let people add in their own artefacts and build out even more comprehensive timelines of user activity but that’s a different issue.

---

Edit – 11 Feb 2020

PPS: Go look at APOLLO and iLEAPP!