Tuesday, September 24, 2024

Withings HealthMate on iOS

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:

  1. Account info
  2. Messages (between users that are connected to each other)
  3. Devices connected to the account 
  4. Measurements
    • Automatic/Cyclic measurements made by the device (e.g. steps, heart rate, location, SPO2, temperature)    
  5. 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:
  1. User ID
  2. First Name
  3. Last Name
  4. Short Name
  5. Birthdate (Stored as Apple Cocoa Core Data Timestamp - Local Time))
  6. E-Mail
  7. Creation Date (Stored as Unix Epoch Timestamp - UTC)
  8. 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:


FieldMeaningAdditional info
ZUSERIDUser ID of the account in the app8-digit
ZSENDERIDUser ID of the sender of the message8-digit
ZRECEIVERIDUser ID of the receiver of the message8-digit
ZSENDERLASTNAMELast name of the sender
ZSENDERFIRSTNAMEFirst name of the sender
ZDATETimestamp of the message Apple Cocoa Core Data Timestamp
In Local Time
ZWSMODIFIEDDATELast modification date of the messageApple Cocoa Core Data Timestamp
In UTC
ZEXPIRATIONDATESet expriation date of the messageFunction not tested yet. In my data there were still messages in the database after the expiration date. Could be just relevant for the UI.
ZTYPEMESSAGEType of the messageFour values in my data:
- Cheer
- Taunt
- Custom
- Message
ZMESSAGE2Content of the message


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:

FieldMeaningAdditional info
ZDEVICE_IDInternal Device ID8-digit
ZUSERIDUser ID of the sender of the message8-digit
ZCREATEDDate of the association of the device with the accountUnix Epoch Timestamp
In UTC
ZLASTCONNECTIONLast connection/sync with the deviceApple Cocoa Core Data Timestamp
In UTC
ZLAST_WEIGHINLast weigh in on the device - the last time the device got dataApple Cocoa Core Data Timestamp
In UTC
ZMACMAC address of the device
ZFIRMWAREFirmware version of the device at the last sync
ZLATITUDELatitude value of last syncNot too precise - dependent on the device and type of sync (via BT to App or via W-LAN)
ZLONGITUDELongitude value of last syncNot too precise - dependent on the device and type of sync (via BT to App or via W-LAN)
ZTIMEZONE

Timezone set on the device

Format: "Europe/Berlin"

ZISSYNCDISABLED

0 = Sync enabled
1 = Sync disabled




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:

FieldMeaningAdditional info
ZCATEGORYCategory of the data measured:
0 = Steps
2 = Heart rate
3 = ??
5 = Location
6 = SPO2
8 = ??
12 = Body temperature
For the values 3 and 8 I, at the moment, don't have any idea what they mean.

ZDEVICEIDDevice ID of the device that measured the dataCan be NULL if the device was the phone itself (e.g. data from Apple Health or from the HealthMate app -> GPS)
ZDURATIONDuration of the measurement Duration is in seconds
ZTIMESTAMPStart Time of the measurementApple Cocoa Core Data Timestamp
In UTC
ZSTEPS# of steps recordedOnly filled for category 0 - Steps
ZDISTANCEDistance recordedOnly filled for category 0 - Steps
The distance is calculated by the app - based on the size of the person and the # of steps
ZCALORIESEARNEDAdditional calories earnedOnly filled for category 0 - Steps
This value is calculated by the app based e.g. on the size and the body composition of the user
ZHEARTRATE1Measured heart rateOnly filled for category 2 - Heart rate
ZLATITUDELatitude valueOnly filled for category 5 - Location
As decimal value
ZLONGITUDE

Longitude value

Only filled for category 5 - Location
As decimal value
ZALTITUDE

Altitude Value

Only filled for category 5 - Location
In meters
ZDIRECTION

Direction the device is going to

Only filled for category 5 - Location
In degree (0 - 359)
ZRADIUS

Radius around the coordinates - Means the uncertainty
Only filled for category 5 - Location
In meters
ZSPEED

Speed of the device

Only filled for category 5 - Location
In miles per hour (mph)
ZSPO2

Quality of the SPO2 value

Only filled for category 6 - SPO2
Value is in percent
ZASCENT1

Not known yetOnly filled for category 8 - Unknown
ZTEMPERATURE

Body temperature

Only filled for category 12 - Temperature
In degree Celsius


Tracking / Activities

The last thing I looked at were the activities tracked by my devices.
The activities are tracked in different cases:
  1. 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.
  2. 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
Summary data of a day (24-hour timespan) is tracked (or better caluclated) for statistics and stored in the same database. Consist of e.g. total # of steps, spent time in heart rate zones, in/active minutes of the day.

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:

FieldMeaningAdditional info
ZDEVICEIDDevice ID of the source device

ZSUBCATEGORYID of the Subcategory - e.g.:
4 = Walking
16 = Others
18 = Running
22 = Cycling
The names of the subcategory can be joined from the table ZACTIVITYSUBCATEGORY from the same database
ZSTARTDATEStart time of the trackApple Cocoa Core Data Timestamp
In UTC
ZENDDATEEnd time of the trackApple Cocoa Core Data Timestamp
In UTC
ZREFERENCEDATEThe day the tracked data is referenced to.Apple Cocoa Core Data Timestamp
In UTC
ZMODIFIEDDATEDate of last modificationApple Cocoa Core Data Timestamp
In UTC
Also the app itself seems to modify anything - the timestamp is updated than. Almost all my Modified timestamps are younger than my created but I did not modify anything.
ZMANUALSTARTDATEStart time for manual tracking started e.g. on a watch Apple Cocoa Core Data Timestamp
In UTC
ZMANUALENDDATEEnd time for manual trackingApple Cocoa Core Data Timestamp
In UTC
ZSTEPSNumber of steps
ZCALORIESEARNED

Additional calories earned

ZISREMOVED

1 = Removed by user
0 = Default - Not removed

For my data I have entries that are set to 1 in the database. This means: Removed tracks are still in the database!
But these tracks are not shown in the UI anymore.
ZPAUSEDURATION

Pause in the tracking

In seconds
Only for manual tracking
ZNOTE

Added notes to the tracking by the user

ZTIMEZONE
Timezone where the device was in when tracking started.


ZCITY

Located city where the tracking was started

Only used when manual tracking is started

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 = 36

With 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 NULL


With 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 NULL

There 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

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. 









Monday, May 20, 2024

Android 14 - Battery Usage and App Usage Events

Moin! :-)

What is this post about?


I've switched my test device from Android 13 to Android 14 - finally - and so I have a lot of new stuff to look into.  I've done some analysis on the battery_usage_v9 database. It was introduced with Android 14.

On Android 12 and 13 there also seems to exist an older version of this database - battery_usage_v4 - Kevin Pagano (stark4n6) wrote a blog post about it and an ALEAPP parser in 2021. His blogpost can be found here.



What type of data to expect?


Like Kevin Pagano already wrote in his post - the data in this databases seems to correlate with the Android Battery Usage and Battery Settings Screens in the Android UI.
Can be found under Settings - Battery in Android and shows something like in the following screen shots to see.


Figure 1: Settings Services - Battery


You can get information on the current battery charging and health status and you can get information on the apps that used the battery, in foreground and in background.
So at least this information should also be in the battery_usage_v9 database.

Okay, let's start digging into the database and its data.

Test device and tested version


Test devices: Fairphone 3 and Fairphone 4
OS: Android 14


The tables


Table: BatteryState

This table already existed in the battery_usage_v4 database. But now it has a different structure. Here is a short overview:

  • mid  - just an iterating id for this table
  • uid  - the uid of the app
  • userId - the id of the user the app is running for (e.g. 0 for default user)
  • packageName - name of the package (e.g. com.android.settings)
  • timestamp - Timestamp event was written in epoch - this only happen every hour by the system
  • consumerType - int value - I have the values 1 and 3 - but I don't know what they mean.
  • isFullChargeCycleStart - Zero or One - in the UI it is said, that only the apps since the last full charge are shown - I guess that this field hold the value if the app shall be shown in UI or not. Double check required.
  • batteryInformation - No idea what this is - Sample data in it: 
CgYIYRACGAIQABi9sscTIg1FdXJvcGUvQmVybGluOhFHb29nbGUgUGxheSBTdG9yZVEAAAAAAAAA
AFkQG44jJZ9DP2EAAAAAAAAAAGj///////////8BcAB4AIEBAAAAAAAAAACJAQAAAAAAAAAAkQEA
AAAAAAAAAJkBAAAAAAAAAACgAQA=
          No idea what this is - when playing a bit with coding/decoding I can recognize                   strings in it after decoding with base64: 
         Europe/Berlin:Google Play StoreQ  
 
  • batteryInformationDebug - here we can find similar information stored like the one in the older database version
# com.android.settings.fuelgauge.batteryusage.BatteryInformation@ffa34700
app_label: "Google Play Store"
background_usage_time_in_ms: 0
boot_timestamp: 41015613
consume_power: 5.988055555555555E-4
device_battery_state {
  battery_health: 2
  battery_level: 97
  battery_status: 2
}
drain_type: -1
foreground_service_usage_time_in_ms: 0
foreground_usage_time_in_ms: 0
zone_id: "Europe/Berlin"
The batteryInformationDebug was empty on my Fairphone 4, but held this information on my FP3 - I don't know exactly why - I read something about new features coming with Android 14 for battery health monitoring and that to use them you need to allow it in the developer options. But I did not do this. Either way - let's go through data what it can mean.

    • app_label - Yeah - the app label - nothing to add from my side
    • background_usage_time_in_ms - the time in ms the app used battery in background
    • boot_timestamp - the time since last reboot of the device in ms
    • consumer_power - consumed power - perhaps in mAH - not sure
    • device_battery_state
      • battery_health - the health status of the battery, I tested three of them (ok/hot/cold) with my test device - and they map to the one from the Android developer guide Android Developer Guide - Battery 
        • 1 = Unknown
        • 2 = Good
        • 3 = Overheat
        • 4 = Dead
        • 5 = Over Voltage
        • 6 = Unspecified Failure
        • 7 = Cold
      • battery_level - Current charging level in percentage value
      • battery_status - Same values like Kevin Pagano described in his post - I come to the same conclusion with my testing
        • 2 = Charging
        • 3 = Discharging
        • 5 = Fully Charged
    • drain_type - No idea what exactly this is. Value is either -1 or between 1 and 16 in my data.
    • foreground_service_usage_time_in_ms - time in foreground in ms - not sure what "service" means - app have either, only foreground service time or foreground time - I don't see both values for the same app
    • foreground_usage_time_in_ms - time in foreground in ms - like written above - not sure what the difference to foreground service is. 
    • zone_id - the time zone set on the device
    • total_power - Missing in the data above - but existent in some others - I don't know the meaning at the moment - in my data the value is either 30 or missing at all
    • is_hidden - Also missing in the data above - but existent in some others - Also here - I am not sure what the value means - it is either True or missing at all

     I've written a parser for ALEAPP for this database and the data in batteryInformationDebug. The output is similar to the one from Kevin Pagano so it is better comparable. Thanks to him - I used part of his code.


Okay, but, there are also other tables in the database. For me one interesting one is the following:

Table AppUsageEventEntity

  • mId - just an iterating id for this table - no correlation found to the other table
  • uid - the uid of the app
  • userId - the id of the user the app is running for (e.g. 0 for default user)
  • timestamp - Timestamp event was written in epoch - but written in real time, not only every hour like in the other table
  • appUsageEventType - I have two values in my test data and found the following meaning by testing
    • 1 = Paused -> Moved to background
    • 2 = Resumed -> Moved to foreground
  • packageName -  name of the package (e.g. com.android.settings)
  • instanceId -> ID of the instance running - I have no idea how it is generated and what it means
  • taskRootPackageName - Package name of the package that started/called this app


 I tested a bit with opening/closing apps and so on. And I can see exactly what I've done in this table.

We already know similar artifacts showing this on Android - but this one was new for me. Always good to know different ways to show things that happened on a device to correlate and verify the data and finding the truth.

I've written a ALEAPP parser for the data from this database too.


The other tables hold also some data - but I cannot makes sense out of them or see any useful data.


How long does the data stay?

Before I forget - for how long is data in the database?

Well - in my cases: Since the last Android Update. For one device it was about 10 days - I did a recheck few days later and it was about 14 days.

But double check is needed here.


Conclusion

As always - it was fun digging into the data.
More work need for some table/fields/data. And, as far as I found information - Google will bring new features for battery monitoring and battery health monitoring for the users. Perhaps that is what I already found in the database - we'll see.


The data in the batteryInformation field drives me crazy - 
CgYIYRACGAIQABi9sscTIg1FdXJvcGUvQmVybGluOhFHb29nbGUgUGxheSBTdG9yZVEAAAAAAAAA
AFkQG44jJZ9DP2EAAAAAAAAAAGj///////////8BcAB4AIEBAAAAAAAAAACJAQAAAAAAAAAAkQEA
AAAAAAAAAJkBAAAAAAAAAACgAQA=

If any hint from you - feel free to contact me - I am happy to learn new things. I found more fields with similar data in them i the other tables.

I hope you had a fun read. As always - thanks for your time and if any questions or suggestions come up - feel free to contact me.

UPDATE 2024-05-20:

Thanks to Kevin Pagano (stark4n6):

The data in batteryInformation is BLOB -> base64 -> protobuf

After decoding we'll have:

{

    "1": {

        "1": 97,

        "2": 2,

        "3": 2

    },

    "2": 0,

    "3": 41015613,

    "4": "Europe/Berlin",

    "7": "Google Play Store",

    "10": 0,

    "11": 4558662229688196000,

    "12": 0,

    "13": 18446744073709552000,

    "14": 0,

    "15": 0,

    "16": 0,

    "17": 0,

    "18": 0,

    "19": 0,

    "20": 0

}


Looks like similar values as in the field batteryInformationDebug.

Thanks a lot - now I can take a further look also into the other fields. Pretty sure I will come with an update soon :-)

UPDATE 2 FROM 2024-05-20:

So, I did some recoding and analyzing for the column "batteryInformation".

Here you can find the mapping from the keys to the decoded data in the column, for a few I currently don't have a mapping or an idea what the values could mean:


{

    "1": { = device_battery_state

        "1": 97, = battery_level

        "2": 2, = battery_status

        "3": 2 = battery_health

    },

    "2": 0, = is_hidden

    "3": 41015613, = boot_timestamp

    "4": "Europe/Berlin", = timezone

    "7": "Google Play Store", = app_label

    "10": 0,

    "11": 4558662229688196000,

    "12": 0,

    "13": 18446744073709552000, = drain_type

    "14": 0, = foreground_time_in_ms

    "15": 0, = background_time_in_ms

    "16": 0,

    "17": 0,

    "18": 0,

    "19": 0,

    "20": 0 = foreground_server_time_in_ms


I've also updated the parser for ALEAPP. Like I have written above - one of my two test devices didn't gave me data in the batteryInformationDebug, only in batteryInformation. But both devices gave me data in batteryInformation. So I decided to write the parser parsing always the batteryInformation.









Sunday, May 5, 2024

Analysis of App K-9 Mail for Android

Hi everyone :-)

It's now a few years ago that I've written my last post here. But now, finally, I decided to restart my work and sharing here.

Today I want to share my knowledge or better analysis results on the app K-9 Mail for Android.
It's a nice, open source, multi-account mail program - developed by Mozilla - so the Android version of Thunderbird . In the last few years I had a few cases, where this app was used as mail app on Android devices.

Android-Version: Android 13

App-Version: 6.802

Used tools for analysis: DB Browser for SQLite, 010 Editor, File Browser, Python (for developing parser for aLEAPP)

Path

The path of the application data is:

/data/data/com.fsck.k9

I checked all folders inside this one.
The most interesting is the folder databases.


As you can see in the picture, in my system there is a preferences_storage SQLITE database and a few other. The others begin with an UUID it seems - I assume, because I have configured two different accounts, the databases correspond with my accounts.

Let's see if I am correct, first I'll take a look into the preferences_storage file.


Accounts

In this database I find the table preferences_storage with the following content (column1 = primkey, column2 = value):


As you can see the UUID that begins with 9003efac relates to one of the mail accounts. There are a lot of other values in the table, e.g. incoming server settings, in JSON format:


For the first mail account there is also the password stored, nice thing.
The other account (UUID begins with 37e364a0), that is a Gmail account, uses OAUTH for connection, there is no Password in the JSON because of the different used authorization mechanism.

Okay, so I know that the UUIDs relate to the accounts, let us see what is in the other databases.

Messages


In the table messages of the database (here for UUID 9003efac) we can find a lot of infos.


This table gives an overview of all messages for an account including a preview of the messages, the number of attachments, etc.

There is also a table folders - with that we can also see in which folder the mails are stored.



IN the database there are a lot more tables. For me most interesting was the table message_parts, that, like the name says, holds the Content parts of the messages.


The field root relates to the filed message_part_id in the table messages. 
I can also see attachments, or at least the reference to them, in this table. But where are these attachments stored?

Attachments

As I could see in the folder structure of the folder databases there are two folders, starting with the already known UUIDs and ending with "db_att". Well, it could be, that the attachments are in these folders.

There are a lot of files, just with an id. This id corresponds with the field id in the table message_parts.
I tested sending a png-file to one of the mail accounts. Finally the file landed there, but base64 encoded. So yes, attachments, but also message parts that are too big to store directly in the database, can be found in this folder.
When testing sending the png file I recognized when exactly the png was stored in this folder. I will explain now:

Handling of Mails and Attachments


I did a four step process:

1. E-Mail is just received on the device and opened.
2. The content is downloaded (by default K-9 Mail won't download any attachments or inserted images automatically, that's a security and privacy feature)
3. The attachments is additionally saved
4. The mail is deleted

Step 1: E-Mail received and opened

In the table messages the is the message itself and in the table message_parts there is the content and a row for the attachment, but the attachment content is not on the device.

Step 2: Content downloaded

Now the attachment is on the device in the folder for the attachments.

In the table message_parts the field data_location has changed from 0 to 2 for the attachment entry.

Step 3: Attachments locally saved

When saving an attachment the user can choose where to save. The png file is than saved there (base64 encoded). But in the app context I could not find any traces of this. So at least for now I would not know, the an attachments was stored in a different location on the device.

Step 4: E-Mail deleted

Finally I deleted the mail. 
The file of the attachment in the attachment folder is gone. I did not try to recover it.
And the entries in the database are gone.
I can only see the now missing id in the table messages. So I would at least know, that one message is missing.
Important to say: It is possible, that the deleting mechanism is dependent on the server settings. I had thought, that the deleted message would first be moved into the "deleted" folder of the account, but at least for my account here, this did not happen.

The field data_location

When testing I could see changes in the field data_location in the table message_parts.

For now I can say the following:

Value 0 = The attachment is not downloaded and so not on the device
Value 1 = The message part and/or attachment is stored directly in the table, field data
Value 2 = Attachment/message part is stored in the attachment directory
Value 3 = I don't know yet - it seems there is something left on the server but already something stored on the device - but for the moment I just cannot say more

Parsing and decoding

So, after my analysis I also wanted to automate the parsing of this data based on my analysis.
For this I decided to write a plugin/parser for aLEAPP. If you don't know it already -> pretty nice tool, created by Alexis Brignoni and maintained by a lot of cool people  from the DFIR field.
You can find the code and the releases following this link: https://github.com/abrignoni/ALEAPP
My pull request with this plugin/parser can be found  here: https://github.com/abrignoni/ALEAPP/pull/485

My parser will get out the accounts, including the password and the server settings. And than will get the messages per account.
At the moment it is a bit tricky with the decoding of the message content - I am not very familiar with mail encoding - but I will get it work soon - it is just another thing I need to understand how it works. Too many different encoding formats :-D

What is currently missing: 
The content of the attachments are not shown. The parser currently only give hint, that there are attachments.
But also here - refinement will come as soon as I have some more time to code it.

Conclusion

K-9 Mail is an app than be found on Android devices.
From my current knowledge not all commercial tools in the DFIR field can decode this app. 
When doing analysis it is now much easier to also create a parser/decoder, because aLEAPP (and also the other "LEAPPs") exist and give an easy way to decode and than visualize the data. 
I am just at the beginning. I already pushed my parser for the Withings Health Mate App last week - and now I created the next pull request for aLEAPP with this parser.
I want to get better - especially in the frontend - how to show the data in a better way, not just tables everytime ;-). It's everything there I need - now I just need to develope it. ;-)

Thx for your time, hope you had a nice read. 
Special thanks to the developers/contributors of ALEAPP.

For any questions and suggestions, do not hesitate to contact me or leave a comment.

Saturday, October 10, 2020

App HealthMate on Android Part 3 - Heart Rate, GPS, Steps

 

Okay, third part of my analysis.

The first part can be found at: https://bebinary4n6.blogspot.com/2020/10/app-healthmate-on-android-part-1-users.html

The second part can be found at:  https://bebinary4n6.blogspot.com/2020/10/app-healthmate-on-android-part-2.html

Base folder of the app: /data/data/com.withings.wiscale2 

This post is about the heart rate, step and GPS data tracked. So the detailed data for an activity and actions of a user in general.


Heart Rate

This data can be found in the database Withings-WiScale, table vasistas.

In this table is a field category. If it has the value -16 it is heart rate data.

So I do the following select (w is an alias for the database):
 
And get the following result:
 
 
We already know the format of the timestamp. 
I currently can just guess the meaning of the filed duration. It could be the duration of the measurement in milliseconds. This would mean for the first line, 113 seconds heart rate measuring. But here is double checking necessary, this is only an assumption. 

I just tested the device a few days but I have 1701 HR measurements. Withings says in there technical description that they measure the heart rate every 15 minutes if no activity is actively tracked. 

The field deviceId holds the ID of the source device because it is possible to attach more than one device at the same time.

Steps

This data can be found in the database Withings-WiScale, table vasistas.

In this table is a field category. If it has the value 16 it is step data.

So I do the following select (w is an alias for the database):
 
 
And get the following result:
 
 
Note quite sure what the value in ascent/descent means because the value is quite high for meters. 
But, as you can see, I only do a few steps in 60 seconds. Perhaps I should get more active.

GPS

Okay, let's see what we can find about GPS data.
We already see something like coordinates when starting an activity and when ending but not the tracing of it.
 
This data can be found in the table WorkoutLocation, database room-healthmate.db .

 
Here you see latitude, longitude, timestamp, altitude etc.
This data is only generated when a GPS device is connected and activity tracking is activated. This means, the watch is connected to a smartphone with GPS activated or the watch itself has GPS and the user explicitly starts an exercise.
 
In the shown data the device ID is 0, this means GPS data come from the Smartphone. 

Conclusion

HealthMate stores a lot of useful data.
One can use this data and visualize it, see when a person was active (both just walking around or doing an exercise)  or slept. Correlated with the heart rate data it can be possible to say if person person has an irregular heart rate at a specific moment.
GPS is only available if the tracker has a built in GPS function or the device has been connected  to a smartphone and activity tracking for an exercise was activated. 
 
So, now I would like to visualize the data in a nice way... damn, more stuff to do.
 
Hope you head a nice read.