analysis

On the Sixth Day of APOLLO, My True Love Gave to Me – Blinky Things with Buttons – Device Status Analysis

in , ,

On this sixth day we’re going to go back to looking at the knowledgeC.db and CurrentPowerlog.PLSQL databases. If you are unfamiliar with these databases, please go back a few blogs. Today is all about what state the device is in. 

Let’s start with the battery level. This is surprisingly covered in a couple of databases and multiple tables in these databases, plenty of places to get this information. The first one is using the knowledgeC.db database with the knowledge_device_batterylevel module. This database provides the usage time between battery levels which may be used to determine amount of usage during a particular time period by the user or device.

Similar is a couple of modules using the CurrentPowerlog.PLSQL database. These two have slight differences. The powerlog_battery_level_ui module just has the battery level as shown in the GUI, while the powerlog_battery_level shows additional information such as raw level and if the device is charging or is fully charged.

The next state is if the device is locked or not. Again, we’ll start with the knowledgeC.db knowledge_device_locked module. 

Going back to the CurrentPowerlog.PLSQL database we have a couple of modules for the same stat. The difference between these is that the latter keeps track of devices locks if the device is using the timed auto-lock function, that “1” just shows that it is in a locked state. The first example shows lock and locked states.

The next few modules are all random device states that may come in handy. The first is the button state. The powerlog_button_state module appears to keep track of which button are being pressed. I’m still testing this one, but this data came from an iPhoneX that has just the power button (apart from the volume buttons). I believe button 48 may be the power button as the timestamps seem to align with locking the device.

The powerlog_camera_state module will keep track of which app and which camera is being used. This will truly tell how many duckface selfies you’ve taken.

Care to know if THE DEVICE IS AT FULL VOLUME PLAYING ANNOYING YOUTUBE VIDEOS? Well then, give powerlog_device_volume a try. 100% is a full volume, and 0 is no volume or “muted”. For some reason I cannot get the “MUTED” column to populate when the device is clearly muted. 🤷🏻‍♀️

The powerlog_mobilebackup module keeps track of backup activity, in this case iCloud backups.

Finally, we have the powerlog_torch_state, which keeps track of when the device flashlight is turned on and off. I still think the torch sounds odd, but I guess the rest of the non-US based worlds calls it that. 🤷🏻‍♀️

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

On the Fifth Day of APOLLO, My True Love Gave to Me – A Stocking Full of Random Junk, Some of Which Might be Useful!

in , ,

Today we go over one of the stranger databases on iOS, the Aggregate Dictionary database, or ADDataStore.sqlitedb. This database is only available with a physical file system dump in the /private/var/mobile/Library/AggregateDictionary/ directory. The database also has a different way of storing its data. Instead of a “this time this event happened” storage system like many of the other database I’ve gone over. This one aggregates data for the last seven days. It only records data on a per-day basis so the APOLLO modules will only store a day timestamp. 

This database is good to find random bits of useful stuff, not every case will need it but you might be surprise what it is tracking. The Distributed Keys and the Scalars are each keeping track of seemingly obscure items. In my opinion, the Scalars are more interesting. On my example database I have 5398 unique keys for Scalars and 795 keys for the Distributed table. I can’t even begin to show you everything this database tracks, its best just to take a look at one yourself to see what might help in your own investigations. I will focus here on a few of the more interesting Scalars entries.

I’ve previously written about this database with respect to Pincodes, Passcodes, and Touch ID.

APOLLO only has two modules for the Aggregate Dictionary. Since these are using only a per-day timestamp, I’ve made them easy to filter out or as a user choose to not use these modules. They can look a tad messy in the final output.

The first item I pulled out of the stocking is CarPlay data. The example below shows how many cars I’ve connected using CarPlay in com.apple.CarPlay.VehicleCount. I’ve connected this device to three different cars. I’m still testing the difference between*.CarPlayCar.* and *.tCarPlayPhone.* keys, however I could guess that Activations has something to do with how many times the app was selected while the ActiveTime is how long the app was in use.

The next item has to do with the Messages application. This app is collecting metrics on the different types of items sent and received over SMS or iMessage protocols.

The Settings applications (com.apple.Preferences) keeps track of which setting got viewed. The example below shows I viewed the Bluetooth menu twice on the 10th, and the privacy settings once on the 14th. I encourage everyone to search for various bundle IDs of interest in this output – you never know what the apps are going to store!

The Clock app (com.apple.MobileTimer) keeps track of how many alarms the device has set, if any are active, number repeating, and named alarms. Some apps are better at storing data like this than others.

Safari records the number of tabs open on a particular day

The Photos app (com.apple.mobileslideshow) keeps track of how many photos there are in the albums.

Curious how many times a device was plugged in during a day, try the com.apple.power.state.pluggedin.count key. We will revisit this action in an upcoming device state article.

When I say obscure, I mean obscure – it also keeps track of button presses…because why not!

Some settings are also stored in this database. You can perform a filter for a couple of keywords – enabled or disabled. I’ve provided a screenshot for both. For the “Enabled” keys, I chose to filter on the main interface, Springboard (com.apple.Springboard). The value is a binary value that means on (1) or off (0). This is different for the “Disabled” keys.

If the keys have the term “disabled” in them, you have to think opposite. For example, all these Accessibility features are actually turned on or enabled. If the disabled setting is a 0, it means it is turned on (you can see many of the settings in the iOS screenshot, Shake to Undo and Vibration for example.)

Finally, in the toe of the stocking we can get some data usage information. I’ve filtered here by the Twitter application (com.atebits.Tweetie2). The screenshot shows how much data was transferred in kilobytes over Wi-Fi and/or WWAN – incoming or outgoing.

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

On the Fourth Day of APOLLO, My True Love Gave to Me – Media Analysis to Prove You Listened to “All I Want for Christmas is You” Over and Over Since Before Thanksgiving

in , ,

The fourth day brings us media artifacts using the knowledgeC.db and CurrentPowerlog.PLSQL databases. Each database stores similar yet somewhat different records when it comes to audio, and video usage.

Let’s get in the mood!

KnowledgeC.db 

Starting with the knowledgeC.db database we get an idea of audio inputs and outputs. Using the knowledge_audio_input_route and knowledge_audio_output_route modules we can extract this data.

The example below is audio inputs. Two different devices can be seen in the screenshot below, a CarPlay device and my AirPods.

In the output example we see the same two devices. Common to both is the fact that you may see it bouncing back and forth as devices are being used. For example, if you are using CarPlay rocking out to WHAM’s Last Christmas, it will bounce between playing your music and using the microphone/speaker to listen/dictate messages as they come in.

The knowledge_audio_media_nowplaying is where the real truth of your guilty musical pleasures will make an appearance. It provides a detailed listing of what the user was listening to at a particular time. The example below shows me listening to music, podcasts, and audio books. There is no denying that you started listening to holiday music entirely too early!

CurrentPowerlog.PLSQL

The powerlog_app_nowplaying module sounds like it should extract the same information as above, however it is different in that it will just show the application’s bundle ID being used to play the media.

More context can be found by using the powerlog_app_audio module. This module will extract the app/service name and/or bundle ID for the app using the audio function. In the screenshot below, I received a phone call and a bit later connected the iPhone to my car to listen to music. During that time, I was also using Siri (assistantd) to dictate messages. Fortunately for me, this database doesn’t show what I was listening to! 🤭

Similar to the module above, is the powerlog_audio_routing module. This shows where the audio was routed to, either the Speaker, Receiver, or Car in this example. 

Finally, the Powerlog populates a table just for videos. The powerlog_video module will show the application bundle ID that the video was played with, however not what video was playing.

On the Third Day of APOLLO, My True Love Gave to Me – Application Usage to Determine Who Has Been Naughty or Nice

in , ,

On this third day, we will focus on application usage. We will cover three databases:

  • KnowledgeC.db

    • Be sure to check out more detailed information on this database in my two previous articles.

    • Access to this database is limited to a file system dump, it will be located in /private/var/mobile/Library/CoreDuet/Knowledge/KnowledgeC.db

  •  InteractionC.db

    • Access to this database is limited to a file system dump, it will be located in /private/var/mobile/Library/CoreDuet/People/InteractionC.db

  • CurrentPowerlog.PLSQL

    • Access to this database is usually limited to a file system dump, but I recently learned that if you can perform a sysdiagnose on the iOS device you can get this log as well! This method is certainly not forensically sound, but it will get you access to the database. You will have to rename this database to “CurrentPowerlog.PLSQL” from something like “powerlog_YYYY-MM-DD_##-##_########.PLSQL” to parse it with APOLLO. I have plans to update the script to support this naming scheme. 

    • In a file system dump, you can find this database in /private/var/containers/Shared/SystemGroup/<GUID>/Library/BatteryLife/CurrentPowerlog.PLSQL

      • It is worth a mention here that additional historical Gzipped PowerLog databases may be found in the /Archives directory in the path above. (My APOLLO script does not handle auto unarchiving of these yet, but it is planned for the future.)

Application Information, Installation, & Deletion

Let’s start by getting some basic app information. The Powerlog has a nice listing of iOS applications with their App name, executable name, bundle ID, and version and app type. These entries are not exactly an ongoing log file, but it does get updated frequently. The app type column can provide information on what type of application it is.

  •  1 – Background iOS Service

  • 3 – iOS Native App

  • 4 – 3rdParty App

These entries will also have information on if the app has been deleted. This listing can be extracted by using the powerlog_app_info module.

I have a very similar module called powerlog_app_deletion that uses the application deletion time as its key to get just the recently deleted applications.

To get a listing of recent application installs, we need to go to the KnowledgeC.db database. You will see in this blog series that you cannot rely on one database for all the answers. The module knowledge_app_install will extract the time of install and Bundle ID for the newly installed app. 

Application Play by Play 

Knowing what application was being used at any given moment, down to the second, can be a huge forensic win. Fortunately, we have a couple of options, each a little different.

The first is the KnowledgeC.db database. The knowledge_app_inFocus module shows the app Bundle ID, day of the week, GMT offset, start/end of the usage time from which I calculate usage time in seconds.

Another option is the powerlog_app_usage module which uses the CurrentPowerlog.PLSQL database. The output contains the app Bundle ID and a few other metadata items.

You will notice a few additional columns in the output:

  • ORIGINAL_SCREEN_STATE_TIMESTAMP

  • OFFSET_TIMESTAMP

  • TIME_OFFSET

These additional columns are needed because the timestamps need to be normalized. The original timestamp is stored with an offset, sometimes in the future, sometimes in the past. This offset is stored in PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET table in the CurrentPowerlog.PLSQL database. This offset gets used in many of the Powerlog tables. I want to provide a warning to other investigators to not always believe the timestamp provided in a database. You MUST test this, I’ve seen offsets from as little as a few seconds to as much as 15 minutes. In my queries I’ve done my best to test what I can, but I’m sure I’ve missed some and they may change per iOS version (we will see an instance of this in the next module). Noticed I missed one? Please let me know!

The modules powerlog_app_usage_by_hour and powerlog_app_usage_by_hour_iOS12 extract data usage on a per hour basis. During the research for this blog article, I was using my own iOS 12 Powerlog output from sysdiagnose to test differences with iOS 12. I came across one issue with this particular module, I determined in iOS 12 that the timestamp in this table now implements the same time offset that we saw above, however on iOS 11 (and older) it did not. Soon I will implement a function in the script to determine which iOS to perform which queries, however now it will run both so please be aware of this.

Below is an example from iOS 11.

Below is an example from iOS 12 with the time offset applied (offset columns are hidden from view). Not shown in the screenshot is a bug where the applied time offset may show the ADJUSTED_TIMESTAMP off by a single second for earlier records. If anyone has recommendations on a better SQLite query to perform this offset adjustment, drop me a line!

These modules extract an applications usage on a per hour basis. The query will extract the app bundle ID, screen and background time, and additional background usage with audio and location.

Context Providers 

The next step is to provide more context to each application being used. We have quite a few modules for this.

The InteractionC.db database contains lots of information to correlate contact information with application activity. The screenshot below only shows a portion of the contact data for each record, but it should be a good indication of what email was being read or what contact was being texted. This module is named interaction_contact_interactions.

Curious what the user was browsing when they were using Safari, try the knowledge_safari_browsing module. There are a few catches to this one, it will not record private browsing and entries will be removed when they are history is cleared (either by the system or user).

Using application activity streams in the KnowledgeC.db database we can extract more information using knowledge_app_activity module on to determine items like Apple Map directions, Mailbox or Message views, specific 3rd party application usage, and other native Apple iOS app activities.

I created a similar module, knowledge_app_calendar_activity, specifically for Calendar activity since it has a bit more calendar related metadata items associated with it.

Again using the KnowledgeC.db for context, I can use the knowledge_app_intents module to determine more details of application usage, including sent messages, map searches and navigation, Safari browsing, and phone calls. A keen eye will notice the data blob in the SERIALIZED INTERATION (HEX) column starts with the hex of a binary plist. This binary plist contains more specific contact information, such as who a message was sent to or what contact was called. I saved it in hex to easily copy/paste it into a hex editor or to save off as a plist file.

Finally, we can use the knowledge_application_portrait to get even more contact information for messages or account information for an associated email account. The items in GROUP ID can vary but usually if it’s a GUID it will correspond to a specific email account that can be found in the user’s Accounts database.

On the Second Day of APOLLO, My True Love Gave to Me - Holiday Treats and a Trip to the Gym - A Look at iOS Health Data

in ,

The iOS Health database may be the easiest database to acquire. While other databases need physical file system dumps of the devices, this database can be accessed with an encrypted iOS backup, or possibly an iCloud acquisition. If you happen to have a file system dump these databases can be found in /private/var/mobile/Library/Health. The database we are going to be looking at for this article is the healthdb_secure.sqlite database. 

Retention of Pattern of Life data is also an issue as well. Some data is kept around for a day, some is kept forever. The data in the health database is by far has the most longevity. Users generally want to keep these records indefinitely. Apple makes it easy to backup and restore Health data to new devices. My own personal health data goes back years! It is worth mentioning that this longevity will also make a very large SQLite database, mine is currently at 820mb, that’s a giant SQLite database! This particular one is from iOS 12.1.1.

NOTE: In the following screenshots I’m not showing the output from the APOLLO script only due to ease of readability, however all data represented in this output is in the script output.

The health_distance module extracts the recorded distance in meters for a specific period of time. The data type of ‘8’ is how the Health database keeps track of this distance. More of these data types will be explored.

Along the same line is the health_steps (data type = 7) module. 

We have distance in meters and steps, how about height? The flights climbed (data type = 12) can be extracted using the health_flights_climbed module. It appears I don’t take the stairs as much as I should. #newyearsresolution

If the user has a paired Apple Watch, it will record very regular heart rate readings (data type = 12). We can view this with the health_heart_rate module. This is the same datatype that is recording the new ECG heart monitor on watchOS 5.1.2/iOS 12.1.1.

Time to take a stand, the Watch records this action too! The health_stood_up module will show this (data type = 75). 

Finally, we end with some location information. Each time I record a workout on my watch, its starting location is also recorded. Using the health_workout_locations_start and health_workout_locations_end modules we can see these coordinates. The timestamps record the start and end timestamps of the workout, but only records the start in this particular “metadata key”. Full workout locations are stored elsewhere (still working on this one).

The iOS Health database is one of the largest databases on the system. I hope to have many more modules/queries created for many of the metadata entries captured in the health data, there are hundreds of data types! Got a special request, let me know!

If you want more information on iOS Health data, Heather Mahalik and I discussed this database recently at the SANS DFIR Summit in Austin.

Now go eat cookies. I won’t judge you.

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 Rocket Mortgage by Quicken Loans GIF with everyone you know. GIPHY is how you search, share, discover, and create GIFs.