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.