scripts

On the Twelfth Day of APOLLO, My True Love Gave to Me – A To Do List – Twelve Planned Improvements to APOLLO

in , ,

My Christmas gift to you - improvements!

  1. More Queries – There is plenty more to come. There are more databases and many half-written queries that I have yet to add.

  2. Additional Testing – I want these to be as accurate as possible.

  3. BLOB/Protobuf Parsing – More location information is useful.

  4. Plist Extracting – So much metadata that puts more context to the data.

  5. Database Coalescing – Those WAL files are important.

  6.  Data Visualization – Pretty pictures always help.

  7. Unarchiving of Powerlog Archive Files – Can’t forget about those archives!

  8. More macOS Coverage – I’ve been focusing on iOS, but there is some good macOS databases too.

  9.  Version Detection for Different SQL Queries

  10. Potentially Different Output Formats – Any special requests?

  11.  Better Module Documentation – Describe what each query is extracting in the module notes.

  12. Better Activity Categorization – More specific categories for better filtering.

Grab APOLLO Here!

Start with Day 1: On the First Day of APOLLO, My True Love Gave to Me - A Python Script – An Introduction to the Apple Pattern of Life Lazy Output’er (APOLLO) Blog Series

Discover & share this Merry Christmas GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

On the Eleventh Day of APOLLO, My True Love Gave to Me – An Intriguing Story – Putting it All Together: A Day in the Life of My iPhone using APOLLO

in , ,

I did a blog article, especially about the knowledgeC.db about a day in the life of my iPhone and it went over really well. I’ve decided to do a similar story using all the data that I’ve parsed from my iPhone using APOLLO, quite a bit more data to handle. For my device, I had 1.6 million rows!

Grab a holiday cocktail or a mug of eggnog and sit back and read the (quite boring) tale of my iPhone on September 16, 2018. This is the query I used for this day to filter it down from ~8800 rows.

On this particular day I was out celebrating my good friend Brian Moran’s Birthday at his house in Maryland. Around Midnight I decided it was time to leave and connected my phone to my car using CarPlay. The Device Status here shows the plugged in and CarPlay connections.

Next, I put in directions to “Home” in Apple Maps. I haven’t left yet, I’m still sitting in his driveway. You can see the SPEED is 0.0 in the Location output. Once I start to leave, you’d see that get populated.

During the drive I’m listening to Apple Music, in the Application Activity entries you can open these in the cell browser (depends on your SQL browser) to get more information than is seen in the screenshot row – some entries are very lengthy. Its late at night, good time for some dance music! 

Discover & share this Dancing GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

I barely leave his house for five minutes before I receive a text from him in the Messages application (💕you Brian! 😂). Again, clicking the row for more information can be helpful. You can see how often I chat with a contact, over what application, and various related timestamps.

I message him back using Siri through CarPlay. The App Usage shows com.apple.assistant_service and com.apple.MobileSMS which is Siri dictation for the Messages application. Just after that you can partially see a Send Message Intent in Application Activity. The next Application Activity/Device Status is CarPlay switching back to my music.

Apple Watch data is sometimes activated during a drive – here you can see me getting my steps in while I’m clearly driving. 🤷🏻‍♀️

While my iPhone is connected to CarPlay, it is also charging. Note the BATTERY LEVEL increasing in the next two screenshots.

I get close to home, so I turn off my Maps navigation. The Application Activity can be used to determine my navigation “to” and “from” locations. Redacted below.

A few minutes later I’m home and parked, I disconnected my phone from my car.

I unlock my phone and start checking my Messages. You can also see some population of Significant Locations here as well.

I have an Orangetheory [https://www.orangetheoryfitness.com] class in the morning (later this morning, really), I better set an alarm. Also check to make sure I know what time the class is.

I plug the phone in before going to sleep.

Early in the morning, I want to know what time it is so I tap the screen to check. Still plenty of time to sleep!

I’m awake and (somewhat) active - I unplug the phone.

Of course, I need to check Twitter.

…and some other apps…

…and more apps, while drinking my coffee. 

Time to walk to the gym and start a workout. Once I select workout on my Apple Watch a couple of Health Workout Locations get populated with the coordinates of my gym. (Feel free to join me!)

In the middle of my workout I feel like I’m dying. 😵

A bit later I check WhatsApp, good to see my heart rate go down a bit too!

The afternoon is filled with research on my laptop at home. You’ll see plenty of location data of me going absolutely nowhere (other steps recorded around my condo) – however if you check my knowledgeC.db on my laptop things would be a bit more interesting!

I’m playing with the LiberiOS Jailbreak.

Looks like I logged in somewhere else that asked me for my two-factor code. (I don’t even remember.)

Check in my Fantasy Football team, not doing so awesome this year. 😬

Getting to Sunday evening, I start determining what I have to do in the next couple days. What exercise classes did I sign up for, what do I have to do on the 18th?

Finally, I set an alarm to make sure I get up on time for my workout.

Discover & share this Cute GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

On the Tenth Day of APOLLO, My True Love Gave to Me – An Oddly Detailed Map of My Recent Travels – iOS Location Analysis

in , ,

I saved one of my favorite topics for (nearly) last. There is no question that location can play a major role in many investigations. 

iOS location data as changed drastically with iOS 11 from previous iOS versions. I published research on these locations in the past and parsing scripts.

It is my goal to update these scripts with this new research soon(ish).

Powerlog Metadata

The CurrentPowerlog.PLSQL contains some useful metadata associated with the primary locations data I’ll discuss a bit later in this article.

The powerlog_location_tech_status module contains a log of how location was determined. Was the location determined by Wi-Fi or GPS technologies? This information can contribute to how accurate the location data may be.

The powerlog_location_client_status module appears to keep track of which applications and services are requesting location data. Some app examples below include Waze, Weather Underground, The Weather Channel, and the AUDI app. The services can be seen in the second screenshot (the data contained in this table was too long for only one!). 

The type of location is also recorded along with accuracy figures. I’ve seen the following types.

  • Location

  •  Significant (Likely has something to do Significant Locations)

  • Fence (Geofencing?)

  •  Visit

Finally, we have a small log of providing time zone context to the data. The powerlog_timezone module will extract this information.

Significant Locations & Routined Databases

As I’ve mentioned above, the storage of the routined process locations and Significant Locations has changed dramatically in iOS11 from previous versions.

These databases are stored in the following path and are only accessible in full file system dumps.

  •  /private/var/mobile/Library/Caches/com.apple.routined/

    • Cache.sqlite

    • Cloud.sqlite

    • Local.sqlite

Cache.sqlite - routined Locations 

The first database, Cache.sqlite, contains an extremely detailed history of coordinates where the device was. In my own data I had over 40,000 (!) data points. This data is stored for just over a week. This can provide a very accurate map of where I was during that last week.

The routined_cache_zrtcllocationmo module can extract these coordinates along with a timestamp, altitude, course, speed (meters/second), and vertical/horizontal accuracy figures.

Also kept for about a week is data extracted with the routined_cache_zrthintmo module. It has fewer data points, but the timestamp and coordinates appear to be accurate.

Cloud.sqlite - Significant Locations - Visits

The next few modules all use basically the same query but are keyed off of different timestamps. The example shown is of the first module.

Again, this screenshot was split into two because of the amount of data provided by this table. Each significant location visit contains various timestamps (visit entry/exit, visit creation/expiration, learned place creation/expiration). Each visit has coordinates along with a Place ID (an identifier for a specific location), data points collected for that place, uncertainty and confidence figures, and device logging information. 

In the second screenshot there are two odd looking columns, Place Name BLOB and Place Geo BLOB. Each of these columns is storing BLOB data in hex format. I chose this format as it is easy (relatively) to copy/paste into a hex editor for viewing. Examples below.

The first column “Place Name BLOB” contains a smaller amount of binary data (than the Place Geo BLOB), and as you can see you can fairly easily determine where I was at this time – Dulles Airport (I’m a frequent visitor there as you can imagine.) You get address, city, state, business name information in this blob.

The second column “Place Geo BLOB” contains much more binary data, but you can still pick out some similarities in the strings.

So here is the kicker – for YEARS I just accepted these BLOBS, tried to parse them and was unsuccessful but didn’t care too much as I could always see the contents of them. While putting this article together I finally discovered their format – it’s a Christmas miracle!

I do lots of mobile work, that includes Android devices for a good portion of the time (Android, eww – I know, but it provides a paycheck. 😉) When I see random BLOBs in anything Android related my first inclination is to say it’s a Protobuf – a very Googly format. 90% of the time I’m right, I can usually spot them pretty easily. For some reason while looking at them now, my usual protobuf triggers just slapped me in the face so I gave the protoc a try (see the usage below.) 

protoc --decode_raw < binary_BLOB

It worked! I finally have a parsed data structure, granted some of the protobuf pieces I still need to determine but its certainly further than I’ve gotten before. Below is the protobuf output for the “Place Name BLOB”. Many of the strings are obvious, however the highlighted section is the coordinates for Dulles Airport. These are 8-byte floats stored in big-endian as shown in the hex editor below.

The “Place Geo BLOB” is quite a bit lengthier, too long in fact to create a decent screenshot of but still contains the string data and coordinates as expected.

Fun Fact – These protobuf data BLOBs are scattered throughout macOS and iOS systems, you can also see them in Maps data if you are looking for examples to play with. Those also appear to contain timestamps! I promise I’ll do a nice protobuf location blog in the near future. I love me some protobufs! (I’m weird, I know.)

Local.sqlite - Significant Locations - Locations of Interest

Ok back to Significant Location databases. There is one left, the Local.sqlite database. This contains similar data to what we’ve seen before. Timestamps, coordinates, confidence, uncertainty, data point count, and (protobuf!) BLOBS. As with the other modules, they query is basically the same but is using different timestamps to key off from. The example is from the first module. These screenshots are also from the same query, too long to post as a single screenshot.

The last piece of location data I’m extracting from the Local.sqlite database is parking history. I connect my iPhone to my vehicle via CarPlay so I’m not sure if this is required to populate this data. The first module routined_local_vehicle_parked shows the last location of my parked car. 

The routined_local_vehicle_parked_history module shows a parking history.

These modules should give you a pretty good idea where a certain user’s device was a given moment. It is worth mentioning the data does expire after a certain period of time, the sooner you have the data the better the data will be for you. Historically it appears to be pretty accurate but significant locations, but it doesn’t have every location. Dump that phone ASAP! 

Santa can easily find where you live. Creepy Santa.

Grab APOLLO Here!

Start with Day 1: On the First Day of APOLLO, My True Love Gave to Me - A Python Script – An Introduction to the Apple Pattern of Life Lazy Output’er (APOLLO) Blog Series

Discover & share this Laff GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

On the Ninth Day of APOLLO, My True Love Gave to Me – A Beautiful Portrait – Analysis of the iOS Interface

in , ,

The interface of the device can produce some useful artifacts. Starting with screen orientation. Perhaps you want to know if the user was watching a video for a period of time. In conjunction with other artifacts that I’ve already details like app usage the knowledge_device_orientation module will show if the screen was in landscape or portrait mode.

The knowledge_device_is_backlit module will let you know if the display was backlit or not, this is different than if the device was locked or not – perhaps the user was just checking their messages without unlocking the device.

Moving to the Powerlog, we can use the powerlog_device_screen module to see what “screen” the device was on. I’ve researched this one a bit and on my iPhone7 on iOS 11. These are the “screens” I was able to determine.

  • Homescreen(s) = 2 

  • Widgets = 19

  • Control Center = 5

  • Lock Screen = 9

  • Pin Unlock Screen = 15

  • Blank Screen = 0

  • App Switcher = 4 

  • Spotlight Search = 18 

  • Lock Screen Camera = 11   

  • Lock Screen Widgets = 17

Perhaps the how light or dark the environment is can help you in an investigation. The Powerlog stores the screen brightness. The lower the brightness, theoretically the darker the environment is where the device is, especially if the auto adjust feature is on. The powerlog_display_brightness module can output this data.

The next two modules powerlog_springboard_aggregate_bulletins and powerlog_springboard_aggregate_notifications] are some modules that I’d like to research more. I believe these are the notifications that are presented to the user for each application. However, I don’t know yet what the differences between a bulletin and a notification yet.

Discover & share this Christmas GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

On the Eighth Day of APOLLO, My True Love Gave to Me – A Glorious Lightshow – Analysis of Device Connections

in , ,

Discover & share this Christmas GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.

Today we’ll be analyzing the knowledgeC.db and CurrentPowerlog.PLSQL database for various connections. The first thing you may want to know in an investigation is – was the device plugged in or not? This can be gained from a few places.

The knowledgeC.db database tracks this information and can be parsed out by using the knowledge_device_pluggedin module. This will keep track of the plugged in and unplugged states of the device and for how long each of those events were as calculated out in the ‘Usage in Seconds’ column.

Similar data is captured in the CurrentPowerlog.PLSQL database. The powerlog_lightnining_connector_status module extracts the same events, however I have seen in my own data some slight oddities, like the plug in/unplug events every minute or so – almost like the cable was loose (it wasn’t). Take that observation for what it’s worth, at least we can corroborate with other data!

Another option is the powerlog_accessory_connection module. This one also appears to be more accurate but I’m not entirely sure what all “accessories” would be covered. My own data shows connections to power cables, this would include my CarPlay connection.

Speaking of CarPlay, we can extract that connection information using the knowledge_device_carplay_connected module. This output only has the initial connection, not the disconnect event.

Instead of physical connections, we may also be aware of wireless connections like Bluetooth. We can use the knowledge_audio_bluetooth_connected module to extract this information from the KnowledgeC.db database. This output contains the Bluetooth MAC address and name of the device. I’m obviously rocking out using my AirPods like a good Mac Fan Girl. 🤘🎶

Next on the Bluetooth list is the Apple Watch. The knowledge.db database also keeps track if the device is near the iPhone to which it is paired. The knowledge_device_watch_nearby module will show if I walked away from my iPhone for a certain period of time.

In the Powerlog, the paired Apple Watch information is stored. It not really in a log format but could be useful information. I have yet to determine the significance of the timestamp, it is not the initial pairing of the device. This can be extracted using the powerlog_paired_device_config module.

Finally let’s look at Wi-Fi connections. The Powerlog is keeping track of each SSID that my phone has connected to, but the name is a bit odd, I’m still researching why it is using this naming scheme, ideas are welcome. The “SSID” is alphanumeric and always same length. The powerlog_wifi_properties module will extract this data.

powerlog_wifi_properties.png

Discover & share this Christmas GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.