Enriching Investigations with Apple Watch Data Through the healthdb_secure.sqlite Database
·
Synopsis
Forensics Question: What pertinent data can be obtained from an Apple Watch through the paired device and how can an Examiner/Investigator obtain and display this data in a readable format?
Apple Watch Series 3, 42mm Aluminum Case with watchOS 8.1 (watchOS 8.1 was the primary watchOS, data from watchOS 7.3.2 and 8.3 were also used within testing)
Tools:
Cellebrite Physical Analyzer Version 7.49.0.28
Magnet AXIOM Examine Version 5.7.0.27176
DB Browser for SQLite Version 2.12.0 on Windows 10 Enterprise, 21H1, referred to as “DB Browser” within this work
HxD Hex Editor Version 2.5.0.0 on Windows 10 Enterprise, 21H1
By: James R. McGee, Digital Forensic Examiner, United States Army, [email protected]
Background:
While an Apple Watch itself offers limited extraction options, the paired device can provide significant data for today’s criminal investigations. Whether the Nilsson case1, where watch activity and heart rate data indicated a time of physical attack and subsequent death, or the Dabate case2, where Fitbit data (correlated with other data) from the wife and victim pointed to the charging of her husband for her death, these smart devices provide a side of the story otherwise not available. As I will cover, beneficial location data and activity/heart rate data is maintained within the paired device. Knowing where to locate, how to review, and how to display this data in a readable format can provide the picture required to aid investigations. This process additionally allows the validation of data parsed through other software, if available. This article will focus entirely on the data gained from an Apple Watch through a Full File System Extraction/Advanced Logical File System Extraction of the paired Apple iPhone.
Location of the Health Application:
Once a file system extraction of the Apple iPhone has been obtained, the Health application database is located within /private/var/mobile/Library/Health. This folder contains the healthdb_secure.sqlite, healthdb_secure.sqlite-wal, and healthdb_secure.sqlite-shm files required for a review of Health application data. This location additionally holds the healthdb.sqlite file as well as the healthdb_secure.hfd and healthdb_secure.hfd-wal files, the latter contains additional location data that will be covered within this article.
Location Data and Obtaining Metadata for Workouts:
The Workout App on the Apple Watch tracks a user’s activity once a workout has been initiated and the Apple Watch records location data during ongoing workouts. While the location data can be parsed correctly with software such as Cellebrite Physical Analyzer, key data was not obtained through basic parsing – such as timestamps and the related workout type. Once a File System Extraction was obtained of the paired test Apple iPhone, the healthdb_secure.sqlite, healthdb_secure.sqlite-wal, and healthdb_secure.sqlite-shm files were extracted and opened through DB Browser. The below sqlite query obtains as much relevant data as is available for completed workouts within one query. This data can be exported from DB Browser as a .CSV file or saved in view as an image. As the query is lengthy, we will review it in sections. When executed, the query yields the following from completed workouts using the Apple Watch:
Figure 1 – Depicting the First Half of the Workout Metadata Query Result, through DB Browser.
Latitude/Longitude values were shortened for security reasons as the location data is quite precise.
Figure 2 – Depicting the Second Half of the Workout Metadata Query Result, through DB Browser.
Query Breakdown:
Shown in Figure 3, lines two through 79 of the query are a case function to identify the specific workout chosen by the user of the Apple Watch. In determining the workout activity type ID, each of the 80 available workout activity types were initiated on the test watch for a minimum of a minute each and documented with start and stop times per the World Clock3. A simple sqlite query was generated to display the workout activity type IDs alongside start and stop timestamps and the corresponding ID was recorded for each Workout.
Figure 3 – Depicting the First Portion of the Workout Metadata Query, through DB Browser.
Shown in Figure 4, lines 80 and 81 obtain the Latitude and Longitude data for the workout. This query uses the metadata_keys table key column data instead of the rowid column which is associated with the keys. It was found that the Row ID data changed during a review of different watchOS versions, i.e. for watchOS 7.3.2 rowid 24 was associated with “_HKPrivateWorkoutWeatherLocationCoordinatesLatitude”, the key for Latitude, while rowid 9 was associated with the Latitude key for watchOS 8.3. The Latitude and Longitude data itself resides in the metadata_values table of the database, as displayed in the query. Through testing it was confirmed the location data was captured where the workout was initiated and only one location point was generated within the database per workout.
Figure 4 – Depicting Location Data within the Workout Metadata Query, through DB Browser.
Shown in Figure 5, lines 82 and 83 obtain timestamps for the date and time the workout was initiated and concluded. In this example, the timestamps remain in the database default UTC-0 time zone, which is then reflected in the query return. This section can converted to local time or your relevant time, such as “datetime(samples.start_date + 978307200, 'UNIXEPOCH', '-05:00')” for a conversion to UTC-5.
Figure 5 – Depicting Start and Stop Timestamps within the Workout Metadata Query, through DB Browser.
Shown in Figure 6, lines 84 through 88 obtain the time duration between the start and stop times of the workout. This query generates start/stop time durations for workouts in hour/minute/second format, i.e., “0h 40m 8s” is equivalent to a start/stop time duration of 40 minutes and eight seconds.
Figure 6 – Depicting the Start/Stop Time Durations within the Workout Metadata Query, through DB Browser.
Shown in Figure 7, lines 89 through 93 obtain the Workout Duration Time.
Figure 7 – Depicting the Workout Duration within the Workout Metadata Query, through DB Browser.
While the inclusion of start/stop time difference and workout duration may seem repetitive these time values can differ. There are two main reasons for this difference, first that the Apple Watch can alert the user of a possibly stopped workout session. The workout is paused while alerting the user and this pause will create a shorter workout time duration than the overall time duration. A second reason for the time difference is that the user themselves can pause and start the workout. The “workouts.duration” value expressed as a timestamp yields something similar to “2001-01-01 00:24:01”, where the duration time is 0 hours, 24 minutes, and 1 second.
The difference between the time duration of the start and stop timestamps of a workout and the workout duration time becomes more pertinent for that of alibi verification. For example, a subject states between 10:30 and 11:30 on Nov 9th they were engaged in an Outdoor Run Workout using their Apple Watch, and not committing cyber-crimes on their personal computer. A File System Extraction of their iPhone is conducted, the healthdb_secure.db is copied, and this query is run which contains the following:
Figure 8 – Depicting Start/Stop vs Time Duration Differences in the Workout Metadata Query Result, through DB Browser.
Reviewing Figure 8, we can see a workout was initiated at 10:31 and concluded at 11:28 on Nov 9th with an overall time duration of 57 minutes and 8 seconds. However, the actual workout duration was only 25 minutes and 33 seconds so either the watch paused the workout or the user paused the workout.
Shown in Figure 9, lines 94 and 95 obtain the distanced traveled, in miles, and the number of calories burned.
Figure 9 – Depicting Distance and Calories Burned within the Workout Metadata Query Result, through DB Browser.
Shown in Figure 10, lines 96 through 101 obtain the workout goal types. Workouts contain goal types which include “Open” (the default workout type), “Distance”, “Time”, and “Calories Burned”. Through testing, workouts were completed with set goal types to obtain the Goal Type ID.
Figure 10 – Depicting the Goal Type within the Workout Metadata Query, through DB Browser.
Shown in Figure 11, lines 102 through 104 obtain the Workout Duration Time in Minutes, Distance in Miles, and Calories Burned for workouts that do not use the default “Open” workout type. The Workout Total Distance was in kilometers and converted to miles by dividing by 1.609. If your distance of measurement is kilometers instead of miles simply use “goal” without dividing 1.609. Substring expressions were used to clean up the end result.
Figure 11 – Depicting Goal Specifics within the Workout Metadata Query, through DB Browser.
Shown in Figure 12, line 105 correlates all Workout metadata with the device it was generated through, in this case, a Series 3 Apple Watch. This query line is also relevant when looking at distance moved and steps taken when differentiating the origin from the Apple iPhone versus the Apple Watch, data type areas which will be covered in greater detail in this article.
Figure 12 – Depicting the Workout Data Origin Product within the Workout Metadata Query, through DB Browser.
Shown in Figure 13, lines 106 through 109 obtain Time Zone, Temperature in Degrees Fahrenheit, and Percent Humidity. Again, just like with Latitude and Longitude, the “metadata_key” table Key column value is used and the pertinent data is pulled from the “metadata_values” table of the database.
Figure 13 – Depicting Time Zone, Temperature, and Humidity within the Workout Metadata Query, through DB Browser.
Shown in Figure 14, lines 110 through 118 conclude the query with the required joins for the workout metadata, a where clause to narrow to workout data, and a group clause to combine data based on the workout activity type. The group clause and the max functions within the query work together to align all data to one row for each workout instead of having multiple rows of duplicated values and numerous null value blocks.
Figure 14 – Depicting the end of the Workout Metadata Query, through DB Browser.
A copy of the full Workout Metadata query is available in .sql and .txt files here [See LINK ONE Folder on OneDrive]
Additional Watch Location Data:
The primary Location Data is stored within the healthdb_secure.sqlite database; however, an additional file stores location data for the most recently completed workouts. The healthdb_secure.hfd file stores a timestamp, latitude value, and longitude value each second within the most recent workouts where a “Distance Traveled” value was present. Within the reviewed data, this location data was present for an average of eleven workouts – six of which had distance traveled data and subsequent location data within the main dataset. It should be noted, the manual extraction of this data could consume a moderate amount of time so a review of the location data from the most recent workouts will likely deem whether this review is pertinent to the examination.
Looking at the data:
By opening the healthdb_secure.hfd file in HxD and scrolling toward the bottom of the file, we can see the most recent data recorded. The data is consistent in that the timestamp value is present after the following: a period of “00 00” null hexadecimal data prior to the hex “A7 03” followed by six hexadecimal null values of “00” and an additional four hexadecimal values.
Figure 15 – Depicting pertinent Location, Latitude, and Longitude Data, through HxD.
Highlighted in Figure 15, we see the additional length four hexadecimal values are “0F B7 DB 7A” prior to our first timestamp (all timestamp values outlined in red), latitude value (all outlined in green), and longitude value (all outlined in orange). Following the longitude value, typically, are 29 hexadecimal values before the next timestamp. The data will sometimes break to “00 00” null hexadecimal values following timestamp and location data but will lead with the starting “A7 03 00 00 00 00 00 00 XX XX XX XX” before the next timestamp value.
The timestamp, latitude, and longitude values are little-endian double precision 64-bit hexadecimal expressions which need to be converted to a decimal floating-point value, providing the number of seconds since Jan 1, 2001 for the timestamp and the latitude/longitude values. The full conversion steps are as follows:
First, the hexadecimal value has to be changed from big-endian to little-endian:
Figure 16 – Depicting Big-Endian to Little-Endian.
Next, the hexadecimal expression is converted into binary:
Figure 17 – Depicting Hexadecimal to Binary.
This value is then broken down prior to conversion to decimal:
Figure 18 – Depicting Binary to Decimal.
The Bit 63 Sign Bit is “0”, because the hexadecimal value is not negative.
Bits 62 – 52 of the total binary value are “10000011100”, which is also the binary value for the first three digits of the hexadecimal expression, “41C”. The binary value “10000011100” converted into decimal is “1052”. “1023”, a constant value, is subtracted from “1052” to obtain an exponent, “29” in this case.
Bits 51 – 0 encompass what is called the “significand”. The significand is composed of the remaining binary value with an added whole number before the decimal point. The remaining “391757E5224AB” of the hexadecimal is converted to a binary value, which results in “0011101100001011000001110000100000000000101000110001”. From this, the significand is “1. 0011101100001011000001110000100000000000101000110001”. The significand converted to decimal is “1.2306370157754254”.
Putting it all together, we multiply “2” by the exponent “29” which is then multiplied against the significand.
Figure 19 – Depicting the Significand Multiplication to a Time Value.
660693217.00031100 is the number of seconds since midnight on 1 Jan 2001 for our timestamp, or 21:53:37 PM(UTC+0), Dec 8, 2021. It should be noted that all numbers after the decimal point in the final solution are not required for a conversion to the final timestamp, the date/time reached is the same with or without them.
The conversions for Latitude and Longitude values are completed through the same process, and are as follows:
Figure 20 – Depicting the full Conversion for Latitude.
The Latitude Value is 32.412829640421110.
Figure 21 – Depicting the full Conversion for Longitude.
The Longitude Value is -84.943728357701930. Combined, we can see the user of the Apple Watch was at 32.412829640421110, -84.943728357701930 at 21:53:37 PM(UTC+0), on Dec 8, 2021. Present within the healthdb_secure.hfd file were an additional 63 timestamp and location data points which encompassed the entire 1 minute and 4 second long test workout. In reviewing the open extraction through forensic software, Magnet Axiom was not able to parse this particular location data while Cellebrite Physical Analyzer was able to parse this data, displaying the timestamp alongside the location data. “Carve Locations” must be selected within Examination Tools in the Case Wizard while opening the extraction to obtain this data through Cellebrite. Cellebrite Physical Analyzer displayed a confidence of 90 for each location and they were found to be precise based on the known route traveled.
Besides workout data, the watch records additional data which can be significant for investigations, namely Heart Rate data, Steps Taken, Distance Traveled, Flights Climbed, and Energy Burned. Interestingly enough, the Apple iPhone can gather some of this same data as well, to include Steps Taken, Distance Traveled, Flights Climbed, and some instances of Energy Burned. This data can be significant in death investigations and to support possible alibi information, i.e., individual says they were sleeping at the time of the incident but watch and/or iPhone data differs. Both Cellebrite and Magnet Axiom can parse these data groups; however, if you do not have access to this software this will cover obtaining the data, or validating the data if the software is available. Keep in mind, all queries executed through DB Brower can be exported as a .CSV file or saved in view as an image.
Heart Rate Data:
Heart Rate data can assist an examiner/investigator in obtaining a likely time of death or activity which appears strenuous due to high beats per minute (BPM) data. The query for obtaining Heart Rate data is much simpler than the above workout metadata query, pulling just a timestamp and the heart rate value. The where clause is necessary here to pull only Heart Rate data, which is paired to the Samples table, data_type column value “5”.
Figure 23 – Depicting the Heart Rate Query, Displayed through DB Browser.
This query by itself will provide a return within DB Browser similar to the following:
Figure 24 – Depicting the Heart Rate Result, Displayed through DB Browser.
DB Browser possesses an export to .CSV option which is beneficial for generating charts to better visualize data. The issue in directly moving from this data export to a chart within Microsoft Excel is that Excel does not read the date/time value as a date/time but rather as a general integer. So “5/26/2021 6:19:09 PM” is read as “44342.7632986111” instead of a timestamp and creates an issue when looking at heart rate data in comparison to time. To correct this issue the following query is beneficial:
Figure 25 – Depicting the Heart Rate Query for Charts, Displayed through DB Browser.
In this query, the substring, or substr, function pulls solely the time value from the timestamp and the data is paired with the Heart Rate data and the timestamp for date information if looking at more than one day of data. This query provides the following DB Browser return:
Figure 26 – Depicting the Heart Rate Result for Charts, Displayed through DB Browser.
This data exported from DB Browser as a .CSV file, opened in Excel, and generated into a chart from solely the Time and Heart Rate columns creates the following chart:
Figure 27 – Depicting an example Heart Rate Chart, Displayed through Excel.
Exporting the data from this query makes generating a chart extremely easy. This chart type is “Scatter with Straight Lines and Markers” but other chart types could be used for personal preference or organizational standard operating procedure. If needed, this same query format can be used for the subsequent data types as well.
A copy of the Heart Rate query and Heart Rate – Chart query are available in .sql and .txt files here [See LINK TWO Folder on OneDrive]
Steps Taken Data:
Steps Taken is just what it sounds like, the amount of steps the user takes while traveling. This is significant for showing movement, or lack of movement, during a specific time. The query for Steps Taken is similar to Heart Rate data. The samples.data_type changes, a conclusion time is present, and both the Apple Watch and Apple iPhone can store Steps Taken data. The Samples table, data_type column value changes from the heart rate value of “5” to “7” for the steps taken value. The Samples.end_date value quantifies the end of the user’s step duration and a portion of the workout metadata start/stop time duration can be used. Lastly, data was obtained to differentiate data stemming from the iPhone or the watch so these figures are not counted twice when there is a duplication or overlap. To isolate results to just the watch or the iPhone one can add the following to the “Where” command, “and (data_provenances.origin_product_type like "iPhone%%%%")”. The query for Steps Taken is as follows:
Figure 28 – Depicting the Steps Taken Query, Displayed through DB Browser.
Executing the query within DB Browser produces this result:
Figure 29 – Depicting the Steps Taken Result, Displayed through DB Browser.
A copy of the Steps Taken query is available in .sql and .txt files here [See LINK THREE Folder on OneDrive]
Distance Traveled Data:
Distance Traveled is similar to Steps Taken except that the distance covered is obtained instead of the pace count associated with the distance. The query for Distance Traveled is very similar to that of Steps Taken except for an additional conversion to the default “quantity_samples.quantity” value. Distance Traveled is stored in meters so a conversion line was added to convert this unit to feet. The Samples table, data_type column value changes to “8” for the Distance Traveled. The query for Distance Traveled is as follows:
Figure 30 – Depicting the Distance Traveled Query, Displayed through DB Browser.
Executing the query within DB Browser produces this result:
Figure 31 – Depicting the Distance Traveled Result, Displayed through DB Browser.
A copy of the Distance Traveled query is available in .sql and .txt files here [See LINK FOUR Folder on OneDrive]
Flights Climbed Data:
Flights Climbed data covers the user traveling on stairwells, in numbers of floors. The query for Flights Climbed is very similar to that of Distance Traveled. The Samples table, data_type column value changes to “12” for the Flights Climbed. The query for Flights Climbed is as follows:
Figure 32 – Depicting the Flights Climbed Query, Displayed through DB Browser.
Executing the query within DB Browser produces this result:
Figure 33 – Depicting the Flights Climbed Result, Displayed through DB Browser.
A copy of the Flights Climbed query is available in .sql and .txt files here [See LINK FIVE Folder on OneDrive]
Energy Burned Data:
Energy Burned data is measured in units of kilocalories. One kilocalorie is equal to one calorie in nutrition4, so one kilocalorie recorded is one calorie burned. Again, the query is similar to the above queries. The Samples table, data_type column value changes to both “9” and “10” for the Energy Burned. The query for Energy Burned is as follows:
Figure 34 – Depicting the Energy Burned Query, Displayed through DB Browser.
Executing the query within DB Browser produces this result:
Figure 35 – Depicting the Energy Burned Result, Displayed through DB Browser.
A copy of the Energy Burned query is available in .sql and .txt files here [See LINK SIX Folder on OneDrive]
Height and Weight Data:
The user of the watch is able to input personal information such as height, weight, gender, age, and wheelchair status to calculate how many calories you burn, how far you travel, and other data5. Of these areas, you can query to obtain all entered height and weight values by the user, as well as the timestamp when the data was entered. This could be particularly beneficial when considering user attribution of the primary user of the device between a man and woman sharing a household, who typically would vary in height and weight values.
Height is stored in meters so a conversion was added to convert this unit to feet. This conversion was tested for all meter equivalents to feet from 3’0” to 7’0”. The Samples table, data_type column value changes to “2”. The query for Height is as follows:
Figure 36 – Depicting the Height Query, Displayed through DB Browser.
Executing the query within DB Browser produces this result:
Figure 37 – Depicting the Height Result, Displayed through DB Browser.
A copy of the Height query is available in .sql and .txt files here [See LINK SEVEN Folder on OneDrive]
Weight is stored in kilograms so a conversion was added to convert this unit to an approximate value in pounds. The Samples table, data_type column value changes to “3”. The query for Weight is as follows:
Figure 38 – Depicting the Weight Query, Displayed through DB Browser.
Executing the query within DB Browser produces this result:
Figure 39 – Depicting the Weight Result, Displayed through DB Browser.
A copy of the Weight query is available in .sql and .txt files here [See LINK EIGHT Folder on OneDrive]
Conclusion:
An Apple Watch by itself offers limited extraction options, but as shown in this article the paired device can provide significant data for today’s criminal investigations. Beneficial location data and activity/heart rate data is maintained within the paired device. The knowledge in locating, reviewing, and displaying this data in a readable format can provide the picture required to aid investigations. The onus of knowing where to look and how to obtain the required data is on the examiner/investigator and the above data will greatly assist any examination involving Apple Watch forensics.
Acknowledgments:
The inspiration for this work is due largely to prior productions by Sarah Edwards/ @iamevltwin's APOLLO Modules6 on the Apple Health Application.
Special thanks to SA Keyomie Townsend and SA Troy Pugliese, both fellow Digital Forensic Examiners with the United States Army, for their support and review of this work. SA Pugliese assisted greatly with formatting of time duration queries.
Sources/References:
1. Lieu, J. (2018, April 3). Police use Apple Watch health data as evidence in murder case. Mashable. https://mashable.com/article/apple-watch-murder-case
2. CNN, A. W. (n.d.). Police use murdered woman’s Fitbit to charge her husband. CNN. https://edition.cnn.com/2017/04/25/us/fitbit-womans-death-investigation-trnd/index.html
3. Current UTC — Coordinated Universal Time. (n.d.). Www.timeanddate.com. https://www.timeanddate.com/worldclock/timezone/utc
4. What’s the Difference Between Kcal and Calories? (2021, March 7). Healthline. https://www.healthline.com/nutrition/kcal-vs-calories#differences
5. Adjust the workout settings on Apple Watch. (n.d.). Apple Support. Retrieved February 9, 2022, from https://support.apple.com/guide/watch/adjust-your-workout-settings-apde0be691be/8.0/watchos/8.0
6. mac4n6. (2022, January 31). Apple Pattern of Life Lazy Output’er (APOLLO). GitHub. https://github.com/mac4n6/APOLLO
DFIR Review
This article is very relevant to practitioners given the type and amount of data extracted by the author. Heart rate data, height, weight, steps, location, etc., are all valuable forensic artifacts. Reviewers suggested focusing more on the novel part of the paper. Some suggestions were offered related to make the paper easier to read.
The “Background” section states that “This article will focus entirely on the data gained from an Apple Watch through a Full File System Extraction/Advanced Logical File System Extraction of the paired Apple iPhone" but it is important to clarify that an Advanced Logical extraction will only pull the health databases if the backup is encrypted. Also, the health databases can be obtained through an iTunes backup, as long as encryption is enabled for the backup (and no forensic tools are required).
It was suggested that the section title “Location of the Health Application” be changed to “Location of the Health Database Files” since this is not where the application files are stored.
Future Work
Future work could include determining if results change based on different models of hardware and the software version being utilized. Integration with log2timeline might be helpful to decipher the context around the activity. Reviewers suggested a tie in with APOLLO as well as trying to understand the .hfd file and references to it from the main healthdb. Reviewers also recommended providing a script or a plugin to format and display the data/artifact.
When the “Carve locations” option in Physical Analyzer (v 7.55) was selected, it parsed locations from healthdb_secure.hdf, however some timestamps were incorrect. This could require further analysis to determine if the database contains incorrect timestamps or if Physical Analyzer is improperly parsing the data.
Creative Commons Attribution 4.0 International License (CC-BY 4.0)
