New Presentation from Objective by the Sea 2.0 - Watching the Watchers

Just got back from a wonderful time hanging out with the who’s who of Mac security folk in swanky Monaco at the Objective by the Sea conference. I’ve uploaded my presentation Watching the Watchers in my Resources section. This presentation goes through some of the forensically useful artifacts of the following 3rd party monitoring software:

Direct link to the presentation here.

I cannot recommend enough that the OBTS conference is absolutely worth going to if you are at all involved in Apple Security. Next one is ~Q1 2020 and back in Maui!

iOS 12 APOLLO Updates

Many modules were updated to specially support iOS 12 including those below. Many were already available on iOS 12 (Powerlog, Passes, SMS, etc) without a jailbreak. As always, let me know if I missed something! Remember the ‘yolo’ option can always be used to attempt to get those entries if official support in the module has not been added.

  • Routined - Looks like Apple changed the database schema to change/remove much of the location reverse geolocation protobuf BLOBs that were in the Cache.sqlite, Local.sqlite, Cloud.sqlite databases. Queries have been updated to account for this.

Still working on a few other queries like Screen Time as well as a few more programatic updates.

Apple Pattern of Life Lazy Output’er (APOLLO) Updates & 40 New Modules (Location, Chat, Calls, Apple Pay Transactions, Wallet Passes, Safari & Health Workouts)

I started filling in the gaps to missing APOLLO modules. While doing this I realized there was some capability that was missing with the current script that had to be updated. As far as script updates go the following was done:

  • Support for multiple database name -Depending on the iOS version being used the database names may be different but the SQL query itself is the same. Instead of creating many redundant modules I now have it looking for the different database filenames.

  • Support for multiple queries on different iOS versions- You will now notice that all the modules have been updated with iOS version indicators and multiple SQL queries compatible for that version. Some going back as far as iOS 8! I put in as much legacy support as I had data for. I will likely not add much more unless it is by special request. I can’t imagine there is a whole lot of iOS 8 analysis going on out there, but you never know! I have kept it to major iOS release numbers and have tested with the data I have but it may have changed with a minor point release, if you find this to be true please feel free to let me know!

  • Module Timestamp Rearrangement and Module Cleanup– I’ve started to go through some of the modules and move the items around to make it easier to see what is going on with each record. I’ve mostly just moved the timestamps toward the end since most of them are shown in the Key column. I’ve also removed some superfluous columns and extraneous junk in the queries. I’m really only trying to extract the most relevant data. 

A few notes on script usage change. The script flags have changed, with the added arguments of -p = platform (iOS support only for now), and -v = iOS Version. You may also notice the new ‘yolo’ option – this one will likely be error prone if you are not careful. Use this when you what to run it on any database from any platform. It can also be used with your own custom modules if you don’t have versioning in them.

An example of the module changes is below. Notice the multiple databases listed. In this example, the same location data can be extracted from the cache_encryptedB.db or the cache_encrypted A.db databases depending on the iOS versions. The version information is listed in the “VERSIONS” key, while the specific queries have versions listed in the [SQL Query …] brackets, this is the version that the script is following.

The big updates were with the modules, lots of new support! I now have support for 129 different pattern-of-life items! Most of the support is for iOS, however if you run the queries themselves on similar macOS databases you will find that many of them will work. Better macOS support is coming, I promise.

Application Specific Usage:

  • Chat – SMS, iMessage, & FaceTime messages extracted from the sms.db database.

    • sms_chat

  • Call History – Extracted from CallHistory.storedata database.

    • call_history

  • Safari Browsing – Extracted from the History.db database.

    • safari_history

  • Apple Pay/Wallet - Extracted from iOS passes23.sqlite database.

    • Apple Pay Transactions - passes23_wallet_transactions

    • Wallet Passes - passes23_wallet_passes

 Location :

  • locationd - The following modules extract location data from the [lock]cache_encryptedA.db & cache_encryptedB.db databases. This will include various cellular and Wi-Fi based location tables as listed in the module filename.

    • locationd_cacheencryptedAB_appharvest

    • locationd_cacheencryptedAB_cdmacelllocation

    • locationd_cacheencryptedAB_celllocation

    • locationd_cacheencryptedAB_celllocationharvest

    • locationd_cacheencryptedAB_celllocationlocal

    • locationd_cacheencryptedAB_cmdacelllocationharvest

    • locationd_cacheencryptedAB_indoorlocationharvest

    • locationd_cacheencryptedAB_locationharvest

    • locationd_cacheencryptedAB_ltecelllocation

    • locationd_cacheencryptedAB_ltecelllocationharvest

    • locationd_cacheencryptedAB_ltecelllocationlocal

    • locationd_cacheencryptedAB_passharvest

    • locationd_cacheencryptedAB_poiharvestlocation

    • locationd_cacheencryptedAB_pressurelocationharvest

    • locationd_cacheencryptedAB_scdmacelllocation

    • locationd_cacheencryptedAB_wifilocation

    • locationd_cacheencryptedAB_wifilocationharvest

    • locationd_cacheencryptedAB_wtwlocationharvest

  • locationd – These modules extract motion data from the cache_encryptedC.db database. Not specific location data but will show device movement.

    • locationd_cacheencryptedC_motionstatehistory

    • locationd_cacheencryptedC_nataliehistory

    • locationd_cacheencryptedC_stepcounthistory

  • routined – Extracts location data from the cache_encryptedB.db database. If you have a keen eye you will notice the database name is the same as from ‘locationd’. Completely different database with different data stored in two different directories.

    • routined_cacheencryptedB_hint

    • routined_cacheencryptedB_location

Health Workouts – Using the healthdb_secure.sqlite database I’ve extracted much of the metadata from workouts. I’ve also determined some of the workout types (ie: HIIT, Rower, Run, Walk, etc), but have not enumerated all of them yet. Please let me know if you come across others – easier if you do this on your own data and can easily look it up. Same for weather conditions (Sunny, Rainy, etc.).

  • health_workout_elevation

  • health_workout_general

  • health_workout_humidity

  • health_workout_indoor

  • health_workout_location_latitude

  • health_workout_location_longitude

  • health_workout_temperature

  • health_workout_timeofday

  • health_workout_timezone

  • health_workout_weather

Network and Application Usage using netusage.sqlite & DataUsage.sqlite iOS Databases

Two iOS databases that I’ve always found interesting (and probably should test more) are netusage.sqlite and DataUsage.sqlite. These two databases contain very similar information – one is available in a backup (and file system dumps) the other only in file system dumps. These databases are excellent at tracking application and process network usage. 

These databases can provide answers to investigative questions such as:

  • What apps were being used?

  • What apps were used more than others?

  • Did the device communicate over cellular or wi-fi more often and when?

  • What apps were used that are no longer on the device?

These databases are located in the following locations depending on the type of acquisition available.

  • /private/var/networkd/netusage.sqlite

    • Available in File System dumps only.

  • Backup: /wireless/Library/Databases/

    • DataUsage.sqlite

    • DataUsage-watch.sqlite (yes, there is one just for the Apple Watch!)

  • File System: /private/var/wireless/Library/Databases/DataUsage.sqlite 

I’ve created modules for these databases in APOLLO, but you can also use the SQL queries in a standalone environment. I’m still working on how best to represent the timestamp keys and may alter the APOLLO code to accept multiple timestamp keys. This will help some other modules I’ve been working on as well so keep an eye out for that. I also need to work on acceptance of multiple database names, thanks to DataUsage-watch.sqlite.

The first set of modules are netusage_zprocess and datausage_zprocess. These two use the same SQL query as it is the same table, just different databases. These query extracts the process and/or the application bundle id. This query will show two timestamps:

  • TIMESTAMP – I believe this is the most recent timestamp for the process/application.

  • PROCESS FIRST TIMESTAMP – This appears to be the first usage of the process/application.

The first example comes from netusage.sqlite, the second from DataUsage.sqlite. It is notable to show that more information is available potentially from DataUsage.sqlite. Since this database is backed up it has the potential to have very historical data. These examples come from my iOS 11.1.2 dump. NetUsage goes back to November 4,2017 when I first setup iOS 11 on the device. The DataUsage database on the same device goes all the way back to 2013! This was from my iPhoneX which certainly did not exist in 2013. I restore backups onto new devices. I also get many more records from the DataUsage database.



The next set of queries are netusage_zliveproces and datausage_zliveprocess. These modules technically have a copy of the ZPROCESS data so they may be redundant if you are running APOLLO. Again, this is the same query for each database. DataUsage will again have many more entries. The added value from the ZPROCESS queries is the added network data information, Wi-Fi In/Out and WWAN In/Out. I will assume this value is stored in bytes until I can test further.

The major difference that I can tell between the two databases (apart from number of records), is that the DataUsage database does not record Wi-Fi network data. I know for sure I was on Wi-Fi at some point in the last six years! (It shows in NetUsage – remember it is the same device. Check out my Twitter data, its almost horrifying! 🤭)



Finally, we have an additional query only for the netusage.sqlite database, netusage_zliverouteperf. This query extracts lots of information, some of which I have no idea what it is. The first step into determining this is creating the query! In addition to some timestamps that appear to be stored on a per-hour basis we have the type of network traffic (Cellular or Wi-Fi), bytes and packings coming and going, connection information.

A second screenshot is required to show the rest of the extracted data. Some sort of cellular network identifier (any ideas?) or Wi-Fi (SSID/BSSID) are provided, with additional network-based information.

There is a lot of data going through these pipes!