databases

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.

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

in , ,

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! 

Why

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.

How

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.

Challenges 

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.

Feedback

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

Presentation - #DFIRFIT or BUST: A Forensic Exploration of iOS Health Data (SANS DFIR Summit)

in

At the SANS DFIR Summit in Austin this year I had the pleasure of presenting with Heather Mahalik on iOS Health Data. We get into data acquisition, database contents, patten of life analysis, workout metadata, locational data, forensic data recreations, and finally tool support (or lack thereof).

Video of it will be out eventually, my Resources page will be updated when that happens, however slides are available below:

Find the presentation here!

Finally we had limited edition custom shirts made with our awesome #DFIRFIT logo by our famous DFIR Photoshopper in Residence Brian Moran. We hope that more of these shirts are made available, keep an eye on Twitter!

As always, the SANS DFIR Summit is a great time and a absolutely fantastic conference. I hope to see you all next year!

 

 

 

Pincodes, Passcodes, & TouchID on iOS - An Introduction to the Aggregate Dictionary Database (ADDataStore.sqlite)

in , ,

Have you ever wondered how Apple can put out statistics such as “The average iPhone is unlocked 80 times a day”? How the heck do they know?

Now I do not know for sure that they use this database, however I'd consider putting some cash down on this bet. The Aggregate Dictionary database aggregates (hence the name) certain features of the iOS operation system.

This database is located on the physical device in the path below, and it is not backed up in iTunes/iCloud backups.

  • /private/var/mobile/Library/AggregateDictionary/ADDataStore.sqlitedb

It is worth mentioning here, the ‘dbbuffer’ plaintext file) in this same directory – is exactly what the filename suggests, a buffer for the database. Worth looking at for entries not yet written into the main database.

The ADDataStore.sqlitedb aggregates data on a 7-day per-day basis (first day in, first day out). You will generally see the last weeks worth of data. The day is stored in the column ‘daysSince1970’ which can be converted to human-readable time by using the SQL date function as shown below. It is worth noting this data is being stored in UTC.

I’ve known about this database for a while but have not had the chance to really dig into it. Recently, a reader emailed me about a case they were working on and wanted to know if the passcode was turned on or off on a specific day. Luckily this investigator had a physical acquisition to analyze and the specific day was in the last week of when the acquisition was created. The physical acquisition has many more of these logging databases are available for analysis too! - Want to see more about these awesome databases, check out my iOS of Sauron presentation.

The ADDataStore.sqlite database stores all sorts of seemingly odd settings in the ‘key’ column of the ‘Scalers’ table. The key that I was interested for this readers inquiry was the ‘com.apple.passcode.PasscodeType’ key which stores the passcode type. The ‘value’ column stores a numerical representation of the passcode type last used during each day (UTC time). If the user changes their passcode type throughout the day, it will store the last one configured.

I have enumerated the following passcode types. The example device show above used the 6-Digit passcode, then recently switched to a Custom Numeric passcode.

  • -1 = 6-Digit
  • 0 = No Passcode
  • 1 = 4-Digit
  • 2 = Custom Alphanumeric
  • 3 = Custom Numeric

We can take a look at how many times the device was unlocked successfully or unsuccessfully by using the keys ‘com.apple.passcode.NumPasscodeEntered’ and ‘com.apple.passcode.NumPasscodeFailed’, respectively. The user was mostly successful inputting their passcode, but put the wrong passcode in once (highlighted in pink).

Hrm. Those values look small – the user is probably using TouchID instead of manually entering their passcode. TouchID allows users to enroll up to five fingers to unlock their devices. Note: They still need a passcode to do this, but it allows the user to have a more complex passcode because they can use their enrolled fingerprints to unlock the device for convenience and general usability

Looking at the ‘com.apple.fingerprintMain.templateCount’ key we can determine how many finger “templates” have been enrolled. The example tells us that four fingerprints were enrolled.

By default the enrolled fingers are named ‘Finger 1’, ‘Finger 2’, and so on as shown below. (Nerd Note: When you are in this screen as a user of the device you can check if a finger is enrolled because it will be highlighted in gray as shown - well, I thought it was neat.).

*** WARNING - RESEARCH BELOW IS ONGOING - READ AT YOUR OWN RISK ***

...just kidding...its actually kinda interesting.

I did want to show the complexity of this database therefore I decided to post this as some readers might find it interesting, however don't take my word on this - I can be wrong. In encourage everyone to do their own testing!

This is where it becomes less clear and more research will need to be done. In the Scaler table at any given moment there are many entries that contain the keyword “fingerprint” for all sorts data. I've listed some of the more interesting keys below.

  • com.apple.fingerprintMain.enabled – Binary value – 1 = Enabled, 0 = Not enabled
  • com.apple.fingerprint.countimagesForProcessing – This appears to be the number of times that TouchID was used (whether or not it was used to unlock the device).
  • com.apple.fingerprint.match.autonomousAttempts & com.apple.fingerprint.match.attempts - Match attempts (Not entirely sure the difference as of yet – I think one may be for unlocks (autonomous) and others for other TouchID functions.)
  • com.apple.fingerprint.unlock.touchesTouchIDNotAllowed – How many times a TouchID was attempted but not allowed for some reason, perhaps fingers were the wrong ones or greasy from a hamburger! (c’mon, we’ve all been there)
  • com.apple.fingerprint.unlock.bioLockouts – I found this key to be incremented when I attempted to unlock the device with the wrong finger too many times.
  • com.apple.fingerprint.unlock.passcodeUnlocksNonMandatory – The user put in the passcode, however they were not required to do so.

Enrolling Fingerprints

  • com.apple.fingerprint.enroll.attempts – Fingerprints “enrolled”
  • com.apple.fingerprint.enroll.popup.tooLittleMoves – Apparently I didn’t move my finger enough when enrolling. 

TouchID Passes – Various keys that I’ve seen that show the specifics of how a TouchID match was passed. Many of these are hard to test to explain.

  • com.apple.fingerprint.match.autonomousPassesAfterHomeButton – Incremented when a match was made after the home button was pressed.
  • com.apple.fingerprint.match.autonomousPassesAfterPowerButton – Incremented after boot when TouchID was used (after passcode of course).
  • com.apple.fingerprint.match.autonomousPassesButtonDown – Incremented after the home button was pressed or “pressed” if you have an solid state button.
  • com.apple.fingerprint.match.autonomousPassesButtonLifting
  • com.apple.fingerprint.match.autonomousPassesButtonUp
  • com.apple.fingerprint.match.autonomousPassesButtonUpWithPressureMitigation
  • com.apple.fingerprint.match.autonomousPassesHumid – I assume this one has to do if you are a live body or not.
  • com.apple.fingerprint.match.passesButtonDown
  • com.apple.fingerprint.match.passesButtonUp

TouchID Fails – On the flip side, if a fingerprint fails – we have many keys with many reasons, many with the same characteristics as above but failed.

  • com.apple.fingerprint.match.autonomousFailsBadImageBadBlocks
  • com.apple.fingerprint.match.autonomousFailsCancels
  • com.apple.fingerprint.match.autonomousFailsFingerOffAfterHomeButton
  • com.apple.fingerprint.match.autonomousFailsNoMatchAfterHomeButton
  • com.apple.fingerprint.match.autonomousFailsNoMatchAfterPowerButton
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonDown
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonLifting
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonUp
  • com.apple.fingerprint.match.autonomousFailsNoMatchButtonUpWithPressureMitigation
  • com.apple.fingerprint.match.autonomousFailsNoMatchHumid – I guess I might have been a bit under the weather? ;)
  • com.apple.fingerprint.match.failsNoMatchButtonUp
  • com.apple.fingerprint.match.failsNoMatchHumid

Getting specific, TouchID unlocks appear to be recorded in the ‘com.apple.fingerprintMain.unlock.unlocksByFinger*’ keys.

As far as as I can tell the keys containing “unlocksByFinger” (colored below in yellow) contain the actual number of unlocks, however in the example below the total unlocks were 11, not 22. I am not sure why there are two entries – I’m sure they record different items, however I cannot find documentation to sort out each one. I would rely on the ‘com.apple.fingerprintMain.unlock.unlocksByFinger#Fail’ entries. (As an aside, it’s worth noting that if you get ‘Finger2’ entries, they have an ‘s’ appended ie: com.apple.fingerprintMain.unlock.unlocksByFinger2Fails, maybe a strange type in the code?)

The ‘fail’ in the key would seem to suggest it records “failed” attempts however my testing shows these are in the green highlighted ‘unlocksCanceled’ entries instead.

What’s the ‘QT’ stand for? I have no idea. I tried looking through Apple documentation to find out, but I’ve got nothin’. Suggestions are welcome!

You might think, “Hey, I see Finger 0, Finger 1 in there – that must be the unlocks for each enrolled fingerprint right?”. That’s what I thought, however that was not the case in my testing. In my tests no matter which finger I unlocked the phone with it would be added to the ‘Finger0’ count.

Another key that looks interesting and related are the ‘com.apple.fingerprint.sortedTemplateMatchCount#’ keys. I would have thought these would be equal to the number of times the fingerprint template was used, however it appears that is not the case. I can try two different fingers (each enrolled) and sometimes they show up under the expected number (ie: finger 1 = com.apple.fingerprint.sortedTemplateMatchCount1) and sometimes it won’t – however one of them will increment. Not sure what is going on here.

In conclusion - I’ve only barely touched one of the tables in this database. There is so much data in this database! There is also the ‘DistributionKeys’ and ‘DistributionValues’ tables which store more numerical-based stats versus the incremental/binary stats of the 'Scaler' table. Try the following SQL query on your own and see what you find!

select 
DistributionKeys.key,
date(DistributionKeys.daysSince1970*86400,'unixepoch','localtime') as daysSince1970,
DistributionValues.secondsInDayOffset,
DistributionValues.value
from DistributionKeys
left join DistributionValues on DistributionKeys.rowid = DistributionValues.distributionID
whereDistributionKeys.key like '%unlock%'

I will hopefully be putting out a few more blog entries on other data found in this database (and other databases!). I will of course post updates to this research if and when I get the chance to do it! Stay tuned!