Moin! :-)
In the year 2020 I did an analysis on the HealthMate App of Withings on Android devices.
I decided that it is time to do the same for the corresponding app on iOS devices.
For my last posts I always wrote a parse for ALEAPP - because they were about Android or Apps on Android. So this time I also wrote a parser for iLEAPP (Github Link) - PR is created - should be merged soon.
In my experience there is always a lot of useful data stored in health apps. Data such like Activities (when, where, how intense), general movements, health status and and and...
My test device for this was an Apple iPhone 12 mini with iOS 17.5.1 installed. I used a Full File System Acquisition for my analysis.
The app is called: Withings Health Mate App (com.withings.wiScaleNG)
The app syncs the data from the devices to the Withings servers. I use the app and some of the Withings devices since 2019.
The Withings devices I use/used are the scale BodyComp and the watches ScanWatch, ScanWatch 2 and ScanWatch HR.
The data you can get out of the app is dependent on what data you track and what your devices can measure.
The following data can be found in general and are discussed in this post:
- Account info
- Messages (between users that are connected to each other)
- Devices connected to the account
- Measurements
- Automatic/Cyclic measurements made by the device (e.g. steps, heart rate, location, SPO2, temperature)
- Tracking / Activities
- Specific activities tracked manually or detected automatically (e.g. Cycling, Swimming, Running)
I only look at the data stored in the app. I won't take a look onto my Withings devices or into the data on the provider site.
Where is the data stored?
In the corresponding Sandbox Path of the App the interesting data is stored in the Folder "Library/Application Support" and its subfolders.
I will give the exact location in the sandbox path to every file in the corresponding section below.
Account Info
The used account of the app can be found in a JSON file.
Path: %SandboxPath%/Library/Application Support/
Name: account
You can get the following info out of the file:
- User ID
- First Name
- Last Name
- Short Name
- Birthdate (Stored as Apple Cocoa Core Data Timestamp - Local Time))
- Creation Date (Stored as Unix Epoch Timestamp - UTC)
- Last Modified Date (Stored as Unix Epoch Timestamp - UTC)
Additionally there is stored info about the used security mechanisms (Password/biometric), the used auhtentication tokens and some other stuff.
Messages
In the app it is possible to write messages to other users. To do this the users need to be connected. Connected means in the app that they are competing against each other.
As far as I know there are no group chats possible at the moment.
The message data ist stored in a SQLITE database.
Path: %SandboxPath%/Library/Application Support/coredata
Name: [User ID]_HM3Timeline.sqlite
Table of interest: ZHMTIMELINEEVENT.
In the field ZTYPE the value gives info what type of message it is. Message from users have the value "HMTimelineMessageEvent".
System messages, e.g. if a measurement was above normal level, the value is "HMTimelineTextEvent".
I am interested in the user generated messages now, so I focus on the first value here.
The other interesting fields in the table are:
Devices
Info to connected devices are stored in the following SQLITE database
Path: %SandboxPath%/Library/Application Support/coredata
Name: associated_devices.sqlite
Table of interest: ZWTDEVICE
Interesting fields:
Measurements
I use the term measurement for the values that are captured periodically and automatically by the app.
Path: %SandboxPath%/Library/Application Support/coredata
Name: [User_ID]_vasistas.sqlite
Table of interest: ZVASISTAS
In the table the field ZCATEGORY stores info about the category of the data. Dependent on the value in this field different other fields are filled with the data.
Please understand, that the values in ZCATEGORY are the ones in my data. It is possible, that there exist other values in the field ZCATEGORY, if other measurements are taken from other devices.
Overview of the interesting fields:
Tracking / Activities
The last thing I looked at were the activities tracked by my devices.
The activities are tracked in different cases:
- When I explicitly start tracking on one of my devices. e.g. when I go for a 5k run I use my watch and my phone connected to each other for tracking. - I can decide which type of activity is tracked in this case.
- When the device, mostly the watch in my case, recognize an activity - such as when I go by bike to my work place my watch recognizes this automatically. - What is detected works mostly fine on my side - but I had cases where something wrong was detected. So we need to keep this in mind when looking at the data. Sleeping is tracked the same way
Good to know: It is possible to delete tracked activities/sleep in the app. We will take a look at what this means for the data.
Path: %SandboxPath%/Library/Application Support/coredata
Name: [User_ID]_Tracks.sqlite
Tables of interest: ZTRACK
Overview of the interesting fields:
There a lot of other fields in there and additionally to this data one should take look into the table ZTRACKEXTENSION where specific data to the tracks can be stored.
Because of the amount of different types of data and fields stored in this database I decided to create SQL-queries for different result types
1. Tracked sleep
2. Tracked activities (manual and automatic)
3. Summary of the day
Tracked sleep
In the data to recognize sleep the following field values should be checked to identify the entries:
ZSUBCATEGORY IS NULL
ZTYPE = 36
My build SQL-Query with the relevant fields is than something like:
SELECT
ZDEVICEID,
DATETIME('2001-01-01', "ZSTARTDATE" || ' seconds') [STARTDATE],
DATETIME('2001-01-01', "ZENDDATE" || ' seconds') [ENDDATE],
DATETIME('2001-01-01', "ZREFERENCEDATE" || ' seconds') [REFERENCEDATE],
DATETIME('2001-01-01', "ZMODIFIEDDATE" || ' seconds') [MODIFIEDDATE],
DATETIME('2001-01-01', "ZMANUALSTARTDATE" || ' seconds') [MANUALSTARTDATE],
DATETIME('2001-01-01', "ZMANUALENDDATE" || ' seconds') [MANUALENDDATE],
ZLIGHTSLEEPDURATION,
ZREMSLEEPDURATION,
ZDEEPSLEEPDURATION,
ZDURATIONTOSLEEP,
ZTIMETOGETUP,
ZWAKEUPCOUNT,
ZWAKEUPDURATION
FROM ZTRACK
WHERE ZSUBCATEGORY IS NULL AND ZTYPE = 36With this data you have the information, when and how the user slept.
Summary of the day
For the summary of a day the following fields should be checked to identify the entries:
ZTRACKID IS NULL
DEVICEID IS NULL
My build SQL-Query with the relevant fields is than something like:
SELECT
DATETIME('2001-01-01', "ZSTARTDATE" || ' seconds') [STARTDATE],
DATETIME('2001-01-01', "ZENDDATE" || ' seconds') [ENDDATE],
DATETIME('2001-01-01', "ZREFERENCEDATE" || ' seconds') [REFERENCEDATE],
DATETIME('2001-01-01', "ZMODIFIEDDATE" || ' seconds') [MODIFIEDDATE],
DATETIME('2001-01-01', "ZMANUALSTARTDATE" || ' seconds') [MANUALSTARTDATE],
DATETIME('2001-01-01', "ZMANUALENDDATE" || ' seconds') [MANUALENDDATE],
ZDURATIONINACTIVE,
ZDURATIONINTENSE,
ZDURATIONMODERATE,
ZDURATIONSOFT,
ZSTEPS1,
ZDISTANCE1,
ZTIMEZONE
FROM ZTRACK
WHERE ZTRACKID IS NULL AND ZDEVICEID IS NULLWith this you can get an overview how active the user was on the day - more details for a day can be get out of the measurements and the tracked activities.
Tracked Activities
The tracked activities are built together from three tables from the database
ZTRACK - for the main data
ZACTIVITYSUBCATEGORY - for the type of the activity
ZTRACKEXTENSION - for additional data to a track
And ZSTEPS never seems to be empty (in my data)
My build SQL-Query with the relevant fields is than something like:
SELECT
t.ZDEVICEID,
sc.ZNAME,
t.ZISREMOVED,
t.ZPAUSEDURATION,
DATETIME('2001-01-01', "ZSTARTDATE" || ' seconds') [STARTDATE],
DATETIME('2001-01-01', "ZENDDATE" || ' seconds') [ENDDATE],
DATETIME('2001-01-01', "ZREFERENCEDATE" || ' seconds') [REFERENCEDATE],
DATETIME('2001-01-01', "ZMODIFIEDDATE" || ' seconds') [MODIFIEDDATE],
DATETIME('2001-01-01', "ZMANUALSTARTDATE" || ' seconds') [MANUALSTARTDATE],
DATETIME('2001-01-01', "ZMANUALENDDATE" || ' seconds') [MANUALENDDATE],
te.ZINTENSEDURATION,
te.ZMODERATEDURATION,
te.ZLIGHTDURATION,
te.ZMIN,
te.ZMAX,
te.ZAVG,
t.ZSTEPS,
t.ZDISTANCE,
te.ZMINSPEED,
te.ZAVERAGESPEED,
te.ZMAXSPEED,
te.ZDISTANCE,
te.ZSTARTCOORDINATELATITUDE,
te.ZSTARTCOORDINATELONGITUDE,
te.ZENDCOORDINATELATITUDE,
te.ZENDCOORDINATELONGITUDE,
te.ZREGIONCENTERLATITUDE,
te.ZREGIONCENTERLONGITUDE,
te.ZMINTEMPERATURE,
te.ZAVGTEMPERATURE,
te.ZMAXTEMPERATURE,
t.ZTIMEZONE
FROM ZTRACK t
INNER JOIN ZACTIVITYSUBCATEGORY sc ON t.ZSUBCATEGORY = sc.Z_PK
INNER JOIN ZTRACKEXTENSION te ON t.Z_PK = te.ZTRACK
WHERE t.ZSTEPS IS NOT NULLThere are quite a lot fields that are interesting. I recommend to filter for what you need.
Some things to know about the fields:
We have to fields with the label "ZDISTANCE".
The one from the table ZTRACK is the distance calculated without GPS.
The one from the table ZTRACKEXTENSION is measured via the GPS of the phone.
It depends on the tracked activity what type is used.
Conclusion and further work
Puh, lot of data in there. This was just a first view on the data and some extraction of them.
The data is stored a bit differently to how it is stored on Android - so I could not just copy and paste things ;-)
Analysis was fun. And I am not finished with the work yet.
Why?
1. Well some of the data I looked for, I could not find -
For example:
- The detailed GPS data for a track - not just the start, center and end coordinates
1. Well some of the data I looked for, I could not find -
For example:
- The detailed GPS data for a track - not just the start, center and end coordinates
2. After I did the acquisition of the data I connected Withings with Apple Health - I want to take a look what effect this has on the data - and also how does the Apple health data looks than.
3. My SQL-Queries are not fully finalized. I want to make them more versatile.
4. Some thoughts on visualization - at the moment just tables - but diagrams and maps would be fine.
I scripted different parsers for the Withings Health Mate App artifacts to be included in iLEAPP. I think it is important to give back and it is also a nice training for me.
Health Apps always store very interesting data. Data that can help to determine what a user did at a specific time. But, just like always - never look only on the data of one app or one type. Always try to correlate data and see if different data (types) lead to the same result.
Please - if you use my queries - double check everything. They should help to get a first view on the data. But look for yourself in the database to verify. Especially the query for the Tracked activities was tricky.
I am just happy that I had a bit of time to look into the app also on iOS :-)
Hope you had a nice read.
No comments:
Post a Comment