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

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!


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!


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

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:




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

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

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

I originally released APOLLO at the Objective by the Sea conference in early November. Since then I’ve received a surprising amount of positive feedback about various analysts using this tool or the accompanying SQL queries on their file system dumps to help a variety of investigations.  

It now time for a proper introduction. I will present this to you in what I’d like to call “The Twelve Days of APOLLO” a holiday themed (very, very loosely) blog series starting today! 


APOLLO stands for Apple Pattern of Life Lazy Output’er. I wanted to create this tool to be able to easily correlate multiple databases with hundreds of thousands of records into a timeline that would make the analyst (me, mostly) be able to tell what has happened on the device.

iOS (and MacOS) have these absolutely fantastic databases that I’ve been using for years with my own personal collection of SQL queries to do what I need to get done. This is also a way for me to share my own research and queries with the community. Many of these queries have taken hours, even days to research and compile into something useful. 

My goal with this script is to put the analysis function the SQL query itself. Each query will output a different part of the puzzle. The script itself just compiles the data into a CSV or SQLite database for viewing and filtering. While this database/spreadsheet can get very large, it is still more efficient that running queries on multiple databases and compiling the data into a timeline manually.

Because this script is all based on investigator provided queries it is highly customizable. If an investigator only wants health data, they can elect to run only those query modules. Each query can be customized by the investigator relatively easy, if you don’t need that column – remove or comment it out! I’ve uploaded many queries that I think most investigators would find useful. It is my hope that other investigators create their own and share them with the community.


The script is a simple Python script that intakes what I’m calling modules. Each module is a single SQL query that pulls out specific data from a database. The module will also have some metadata about what the SQL query

The module is a text file that contains a few required items:

  • DATABASE – The exact name of the database to perform the SQL query on.

  • ACTIVITY – What this particular record is categorized as.

  • KEY_TIMESTAMP – The timestamp to be used as the key for timelining.

  • SQL Query – The query that is performed on the database. They query should extract one specific type of record from the database, thus a database may have many modules for very specific outputs.

Depending on what the user is looking for they can run one, some, or all the modules and it will output to either a CSV or SQLite database file.

Example Usage

The script is a simple python script that only takes only a few arguments. 

python apollo.py -output {csv, sql} <modules directory> <data directory>

There are two output options, a CSV file or a SQLite database. Please let me know if other outputs are required. Following that, the path to the module’s directory and the path to the data directory. The data directory can be a single directory full of the databases needed to parse or a full file system dump – the script will find the databases by name in the module.

An example of the output is seen below. Each SQL query run will have different output for that particular query and database in the output column. In the example we see everything from health steps/distance, to location, to application usage. The Key column contains the timestamp that each record is organized on, the Activity contains the type of record. The database and module columns contain which database and module parsed that information.


One of the main challenges with these Pattern of Life databases is access. Most of the really good forensically useful POL data is not easily accessible, particularly with iOS devices. With macOS devices we may need to deal with FileVault encryption or database level encryption. This script assumes you have good data to work with. Some databases may be available some may not be.


While I primarily created this script for my use (don’t we all), I am open to feedback. If instead of using ConfigParser module files you want something else, let me know. If you need different output formats, let me know. I will consider all feedback.

I would appreciate all the help I can get. I’ve primarily tested these queries with a focus on iOS 11, however I know many will work on older versions and some have been tested with available data from iOS 12 (iOS Health). I’ve also tested running the script on macOS with Python 2.7 installed, it may not run as expected on other platforms. (FWIW: I will upgrade to python3 when macOS does.)

The script was just updated to be more efficient by Sam Alptekin of @sjc_CyberCrimes. Sam made the script much faster when running against full file system dumps. He was able to take it down from hours to run to mere minutes. Thanks Sam!

The Next Eleven Days

Each day will have a different topic that will guide you through the usefulness of the APOLLO framework. I focus primarily on iOS in these articles, but many of the queries can be ported over to macOS as well. I do intend to work on this in the future however I will discuss more on improvements later.

I will cover all sorts of topics in the next couple of weeks.

  • Device State

  • Media

  • Health

  • GUI Artifacts

  • Network and Communications

  • Connections

  • Application Usage

  • So much more!

Get APOLLO here and take it for a spin!

Day 2: 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

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

Day 4: 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

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

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

Day 7: On the Seventh Day of APOLLO, My True Love Gave to Me – A Good Conversation – Analysis of Communications and Data Usage

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

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

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

Day 11: 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

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

AirDrop Analysis of the UDP (Unsolicited Dick Pic)

I saw this article “NYC plans to make AirDropping dick pics a crime” on Friday and it got me thinking. What exactly are the cops going to find if they do an analysis of a device, either the sender or the receiver? 

I’ve already done my fair share of analysis when it comes to the Continuity technology with Apple devices with Heather Mahalik at the SANS DFIR Summit in 2017. This article gave me a good reason to do a revisit on how AirDrop artifacts between mobile devices.

Testing Scenario

I’m using the scenario of sending of dick pics in the middle of Times Square as an example. I have two test phones, an iPhone 7 and an iPhone 6. The iPhone 7 belongs to “David Lightman” and the iPhone 6 belongs to “Jen Mack”.  Each device is on iOS 11 for this test. Instead of UDPs, Jen is going to be sending David USP’s instead, or Unsolicited Squirrel Pics – I suppose I need to keep this family friendly right?

AirDrop works over ad-hoc Peer-to-Peer networking over Bluetooth and Wi-Fi. When a user attempts to AirDrop someone a file it will determine who is within range and who is currently receiving AirDrop connections. This may be set to Contacts Only or Everyone (or Receiving Off for those who don’t want UDP’s, smart choice). Poor David has the setting to Everyone, he’ll get some surprises!

To send the squirrel photo to David, Jen used the native Photos application’s sharing feature to select a device to AirDrop the USP to. The only device within range that has AirDrop turned on and in a receiving mode is David Lightman’s iPhone.

Jen selects his phone and sends the picture. On David’s device he receives a pop-up, he can choose to Decline or Accept. 

AirDrop ID

Important to this analysis is an AirDrop ID. On devices this can be found in /private/var/mobile/Library/Preferences/com.apple.sharingd.plist. The keyword ‘sharingd’ is very important in this scenario as much of the Continuity artifacts can be found by searching for that process name. This plist file should be available in an iTunes backup and from any commercial forensic utility. 

Jen’s AirDrop ID is 3DAA769F9F23.

David’s AirDrop ID is E7D713098E3B, also take note of that the DiscoverableMode is set to Everyone. (Jen does not have this key, hers was set Contacts only.)

Artifacts from the Receiver’s (David Lightman) Device

Assuming that the receiver is the individual who would likely make the complaint to the cops, we will look at this iPhone first to determine what artifacts will persist and if they are show attribution to a certain device and/or owner.

Very few artifacts on the devices will actually show this connection happening. Let’s take a look at some of the main artifacts that I would explore if I had the case.

Unified Logs  

Logs can show lots of data and in great verbose detail. Unfortunately, these Unified Logs do not get backed up on iOS devices. The only way to extract them from the device is to get a physical file dump of the device (Jailbreak/Cellebrite CAS/GrayKey). (UPDATE: 12/04/18 - It has come to my attention from a commentor that these unified log files can be collected using the sysdiagnose process on iOS devices. I’ve tested this and they are in fact there. Funny enough, per Apple Documentation, it wants you to copy this archive off the device using AirDrop. 🤷🏻‍♀️ Not entirely the most forensically sound method but hey, data is data - get it with whatever means your legal team will allow. Thanks ‘G’!)

In this example on David’s iPhone, you can see the sharingd process start to scan, attempting to find peers (Continuity connections). During this process you will also see lots of wirelessproxd and bluetoothd activity as well since AirDrop uses Bluetooth and Wi-Fi services. You will also see references to AWDL or Apple Wireless Direct Link.

Follow the logs down a bit and you’ll start to run into some potentially identifying information. We are now starting to see records containing Jen’s device name “Jen Mack’s iPhone”. Pay close attention to the first record highlighted and you’ll see what looks like a MAC address, however it is neither the Bluetooth nor the Wi-Fi addresses. This address is generated different every time a connection is made, therefore not an ideal data point for attribution. 

Going back to the device’s name. This may lead you in the right direction, however anyone can name their device anything they want. I can call my iPhone X “Samsung S9” for instance, no identifying information and frankly a device that doesn’t even do AirDrop.

The next couple of highlighted sections (in red), show the start of the AirDrop connection. We can see an incoming request and start seeing records that include the AirDrop ID of Jen’s iPhone, 3DAA769F9F23. This is where I think attribution may be possible. This ID seems consistent across connections and different devices in my experience. It may be possible to tie this to an Apple ID or specific device. I have yet to find this connection however – it’s not part of the Serial, UDID, or various MAC addresses that I can tell. More research is needed here.

Next, in purple, is more metadata about the file transfer to include transfer status, media type, originating device name, source application, and associated transfer GUID.

In between these metadata records, it shows that it is transferring a file to /var/mobile/Downloads/com.apple.AirDrop/BA40D8CF-54E6-4B09-8F2F-717FB638174E/Files. Whether the user chooses Accept or Decline, the photo still gets transferred to the device.

Finally, the user receives an alert about the AirDrop’ed photo. Following this record is more details on how the user is alerted to the connection audibly and physically with alert tones and vibrations.

This particular AirDrop connect was “Declined” by David. This can be seen in the records below where the ‘selectedAction’ now shows Decline and a clean-up process has started. Highlighted in teal is the AirDrop connection closing.

If the user Accepted the AirDrop’ed photo, the logs would look like the following. The file would have been ‘accepted’ in the metadata records. Finally, since it is a photo – the default application Photos wants to import it into its files and databases. The AirDrop connection is also closed at this time.

Photos Database

Since the photo gets transferred into the user’s Photos database we can look there for hints. This is one file that does get backed up by iTunes and commercial forensic utilities. The Photos database is located /private/var/mobile/Media/PhotoData/Photos.sqlite on the physical device. 

The filename on Jen’s device was IMG_0007.JPG before it got renamed to IMG_0110.JPG on David’s phone. The original filename can be found in the ZADDITIONALASSETATTRIBUTES table in the ZORIGINALFILENAME column.

It is worth noting that the imported photo will carry the same EXIF data as the original on Jen’s device, in fact it is exactly the same photo (hashes should match). The file size and some timestamps get carried over into the Photos database. Other metadata items can be used to determine a photo from another device are the Height/Width, assuming it is from a different family of devices the pixels may be different.

In the ZGENERICASSET table, we have the same Height/Width, however some timestamps are updated to match the time of import via AirDrop. The ZDATECREATED timestamp matches the original creation of the photo. The ZCLOUDASSETGUID matches the GUID seen in the last few entries in the logs above. There does not appear to be any attribution data in this database.

Artifacts from the Sender’s (Jen Mack) Device

In the rare event that the Sender’s device was acquired, the related artifacts are below.

Unified Logs

Much of the logs look similar to the receiver. The sample below shows the sharingd process connecting to David’s AirDrop ID, E7D713098E3B and shows his iPhone device name. Again, the MAC address does not appear to be consistent and will change on every connection.

A few more lines down we can see some file conversion for the photo, IMG_0007.JPG (which apparently wasn’t needed). This is followed by an AirDrop transaction with the AirDrop ID of David’s iPhone.


The lack of attribution artifacts at this time (additional research pending) is going to make it very difficult to attribute AirDrop misuse. At best, if the cops are provided each device, they can pair the connections up – however this will require access to file system dumping services like Cellebrite CAS, GrayKey from GrayShift or performing a jailbreak to get the most accurate analysis. If the devices are named appropriately (ie: If Jen Mack’s iPhone was actually named ‘Jen Mack’s iPhone’) this may make an analysis easy, however I can see an instance where this can be abused to imitate someone else.

Curious about the USP, here you go. This was an advertisement in the DC Metro system, that for some reason whenever I saw it I giggled. 🤷🏻‍♀️