Friday 26 March 2021

Monkey Test Drives a Honda Accord

 

"The red ones go faster!" - original picture sourced from caranddriver.com

Monkey recently "test drove" ("test-parsed"?) a data dump from a 2016 Honda Accord (USA).
This post will describe that wonderful journey.

Special Thanks to Manny Fuentes who generously shared his Honda data. Without this data, this post and associated scripts would not exist.

The scripts are available from GitHub.

Parsing the dump with X-Ways Forensics showed 7 partitions, 6 of which contained the EXT4 filesystem. The first partition was not recognized by X-Ways.

Here is the breakdown according to X-Ways:

Partition1 = 251 MB Unknown
Partition2 = 879 MB EXT4 (System part1, ~8332 files)
Partition3 = 251 MB EXT4 (System part2, ~1275 files)
Partition4 = 1.7 GB EXT4 (User data, ~15115 files)
Partition5 = 251 MB EXT4 (21 files, contained timestamped logs)
Partition6 = 1.1 GB EXT4 (49 files, appears to contain Speech related data)
Partition7 = 125 MB EXT4 (966 files, mostly stored in "data_org.tar.gz")

Note: Two partitions (Partition2 and Partition3) contained /system directories.

Based on strings found in Partition2:\system\build.prop
The system was running Android 4.2.2 (ro.build.version.release=4.2.2) and it seems to be made by Clarion (ro.product.manufacturer=Clarion, ro.board.platform=r8a7791
ro.build.description=T2X-user 4.2.2 9TXX9211 211 release-keys).
The build date was 6AUG2015 16:57:16 UTC (ro.build.date.utc=1438880236).
Partition2:\system\app also contained various .apk and .odex files.

Partition3:\system\build.prop similarly confirmed the previous Android properties
ro.build.version.release=4.2.2
ro.build.date=2015
ro.build.date.utc=1431482880
ro.product.model=MY15ADA
ro.product.brand=Honda
...
ro.product.manufacturer=Clarion
ro.board.platform=r8a7791

Partition3:\system\alps\evolution\paired_device_list.txt contained ASCII text listing various BT addresses and their device names. Was consistent with data found in Partition4's bluetoothsettings.db.

Partition4 contained Android /user data (mainly under com.android, com.clarion, com.honda directories)
Also found on Partition4:

\property\persist.sys.timezone [contained ASCII text set to "US/Central"]

\system\usagestats\usage-history.xml [contained a log of various timestamped Android Activitys. Not validated]

\data\com.android.settings\shared_prefs\bluetooth_settings.xml [contained a timestamp string value for "last_discovering_time". Appears to be millisecs since 1JAN1970. Not validated]

\data\com.clarion.displayaudio.apps.generalsettings\shared_prefs\com.clarion.displayaudio.apps.generalsettings_preferences.xml
[contained string values for "currentTimeZoneIdNotDaylight" (e.g. "US/Central") and "currentTimeZoneName" (e.g. "CST UTC-6")]

\data\com.clarion.displayaudio.apps.telephonyapp\shared_prefs\activity.PhoneTopActivity.xml
[contained the string "DEVICENAME" and what appears to be a MAC address - possibly the most used device?]

\data\com.honda.displayaudio.navi\Garmin\sqlite\quick_search_list.db [contained a "quick_search_list" table which was empty]

Partition5 contained various timestamped error logs (e.g. ErrorLevelPower.log, ErrorLevelSoft.log, ErrorLevelHard.log).

Partition6 appears to contain various Text To Speech related files.

Partition7 contained most of its files in "data_org.tar.gz". This appears to be a restoration backup of Partition4:/data.

The most interesting user related information was found in various SQLite databases under Partition4:/data.
For our data dump, there were 4 x SQLite databases which were of interest:

\data\com.honda.displayaudio.navi\Garmin\sqlite\RecentStops.db
\data\com.honda.telematics.core\databases\crm.db
\data\com.clarion.bluetooth\databases\phonedb.db
\data\com.clarion.bluetooth\databases\bluetoothsettings.db

Consequently, four Python3 parsing scripts were written/tested on Win10x64 running Python 3.9.
The four scripts work a similar manner - they take an input argument to the respective SQLite database and an output argument for the output TSV filename. They then run SQLite queries for the relevant data and output selected fields to the TSV file.

For our data, in addition to phonedb.db, there was a Write-Ahead-Log (phonedb.db-wal) in the same directory.
It is recommended to run the accord_2016_phonedb.py script twice in this type of scenario and compare the two outputs:
1. Run the script WITHOUT the phonedb.db-wal file present in the same directory as the specified phonedb.db
2. Run the script WITH the phonedb.db-wal file present in the same directory as the specified phonedb.db

Users do not have to specify the -wal file at the command line as SQLite will auto-magically incorporate the -wal file if present.
For our data, including the phonedb.db-wal file resulted in an extra 10 calls being found/output.

On to the scripts ... 


accord_2016_recentstops.py - reads RecentStops.db "history" table and outputs details to TSV file.
This table appears to document timestamped lat/long coordinates. We're not sure what triggers an entry.

RecentStops.db can be found at: \data\com.honda.displayaudio.navi\Garmin\sqlite\RecentStops.db

SQLite query used: "SELECT time, lat, lon, name FROM history ORDER BY time ASC;"
 
Usage example:

c:\Python39\python.exe accord_2016_recentstops.py -d RecentStops.db -o rsop.txt
Running accord_2016_recentstops.py v2021-03-22

Processed/Wrote 26 entries to: rsop.txt

Exiting ...


Output TSV format:

time    lat    lon    name

Notes:
time is displayed as ISO formatted string interpreted as Garmin secs since 31DEC1989 (UTC).
lat & lon has the scaling factor applied = 180 / 2^31 to convert to degrees.
name can include cross-street or location strings which can help confirm the calculated lat/long.


accord_2016_crm_eco_logs.py - reads crm.db "eco_logs" table and outputs details to TSV file.
This table appears to log various timestamped journey legs (timestamped odometer / trip range measurements).

crm.db can be found at: \data\com.honda.telematics.core\databases\crm.db

SQLite query used: "SELECT _id, trip_date, trip_id, mileage, start_pos_time, start_pos_odo, finish_pos_time, finish_pos_odo, fuel_used, driving_range FROM eco_logs ORDER BY _id ASC;"
 
Usage example:

c:\Python39\python.exe accord_2016_crm_eco_logs.py -d crm.db -o crmop.txt
Running accord_2016_crm_eco_logs.py v2021-03-22

Processed/Wrote 300 entries to: crmop.txt

Exiting ...

 
Output TSV format:

_id    trip_date    trip_id    mileage    start_pos_time    start_pos_odo    finish_pos_time    finish_pos_odo    fuel_used    driving_range

Notes:
trip_date, start_pos_time, finish_pos_time are displayed as ISO formatted strings interpreted as millisecs since 1JAN1970 (UTC).
mileage, start_pos_odo, finish_pos_odo, fuel_used, driving_range are measured in unknown units but can be used for trending.


accord_2016_phonedb.py - reads phonedb.db "callhistory", "contact", "contactnumber" tables and outputs details to TSV file.
This database appears to log call history and contacts information.

phonedb.db can be found at: \data\com.clarion.bluetooth\databases\phonedb.db

Call History SQLite query: "SELECT _id, address, phonenum, calldate, calltype FROM call_history ORDER BY calldate ASC;"

Contacts SQLite query: "SELECT contact._id, contact.address, contact.firstName, contact.lastName, contact.phonename, contactnumber.number, contactnumber.numbertype FROM contact JOIN contactnumber ON contactnumber.contact_id = contact._id ORDER BY contact._id ASC;"

Usage example:

c:\Python39\python.exe accord_2016_phonedb.py -d phonedb.db -o op
Running accord_2016_phonedb.py v2021-03-22

Processed/Wrote 98 CALL entries to: op_CALLS.txt

Processed/Wrote 41 CONTACT entries to: op_CONTACTS.txt

Exiting ...

 
CALLS output TSV format:

_id    address    phonenum    calldate    calltype

Notes:
address appears to be a MAC address.
calldate is displayed as ISO formatted string interpreted as millisecs since 1JAN1970 (UTC). 
calltype appears to be 1,2 or 3. By using Call Charge Records or examining the devices, it should be possible to determine the significance of each value (e.g. missed, incoming, outgoing).

 
CONTACTS output TSV format:

_id    address    firstname    lastname    phonename    contactnum    contacttype

Notes:
address appears to be a MAC address.
contacttype was consistently set to 3 for all of our contacts.


accord_2016_bluetoothsettings.py - reads bluetoothsettings.db "bluetooth_device" table and outputs details to TSV file.
This table appears to log Bluetooth device names and MAC addresses.
bluetoothsettings.db can be found at: \data\com.clarion.bluetooth\databases\bluetoothsettings.db

Note: There was also a "speed_dial" table but it was empty so we're not sure about how this table is populated

SQLite query used: "SELECT device_bank, device_addr, device_name FROM bluetooth_device ORDER BY device_bank ASC;"
 
Usage example:

c:\Python39\python.exe accord_2016_bluetoothsettings.py -d bluetoothsettings.db -o btop.txt
Running accord_2016_bluetoothsettings.py v2021-03-23

Processed/Wrote 6 entries to: btop.txt

Exiting ...

 
Output TSV format:

device_bank    device_addr    device_name

Notes:
device_bank appears to be an index for each device entry. 
device_addr appears to be a MAC address.


Final Thoughts

If you have a Honda dump of similar vintage, we'd appreciate if you could run the scripts and let us know how it goes.
Obviously, as the scripts were written using one set of data, there may be bugs / mis-ass-umptions.

Or if you can shed any more light on a Honda Android dump, we'd appreciate hearing about your findings.

Finally, if you can share a dump for any another vehicle and would like us to write some parsing scripts, please let us know.

Comments and Suggestions are also welcome in the comments section below ...