Breaking a phone is always something annoying but when it contains your longest bike ride statistics not yet synchronized, it's worst.
I then spend some time to think about how I could access to the detail of my activity. I was only able to see the distance and duration on the lock screen, but that was not enough for me.
The easiest solution
While I wasFor that I connected my phone via USB to my computer and used Vysor Chrome extension and adb. That's probably doable with any similar tools, but they can require to install an app on the device.
If the device is not Internet connected (like my phone that I used only to record bike rides), it may be necessary to enable WiFi with "svc wifi enable" command in adb shell.
If the phone is locked, it can be necessary to unlock it via adb: "input keyevent 66 && input text XXX && input keyevent 66" (XXX is the password, 66 is the key code for Enter).
The geek solution
When I thought about that issue, my first idea was to analyze files used by Strava on Android and my last option was dumping the memory to try to find some data related to my activity.First thing first, I started looking for files on the file system of my phone where I can find data related to my Strava activity.
root@mako:/ # find . 2> /dev/null -name *strava*
A search like the previous one was able to list many files:
./storage/emulated/0/Android/data/com.strava
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_labl_com.strava.m
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_com.strava.m
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_inaka_com.strava.m
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_com.strava.0
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_inaka_com.strava.0
./storage/emulated/0/.estrongs/.app_icon_back/ver/com.strava_419
./storage/emulated/0/.estrongs/.app_icon_back/ver/com.strava_512
./storage/emulated/0/.estrongs/.app_icon_back/ver/com.strava_516
./storage/emulated/0/.estrongs/.app_icon_back/com.strava.png
[...]
./data/data/com.strava/shared_prefs/com.strava.preference.excludeFromBackup.xml
./data/data/com.strava/shared_prefs/com.strava.preference.userPreferences.xml
./data/data/com.strava/shared_prefs/com.strava_preferences.xml
./data/data/com.strava/shared_prefs/apptimizecom.strava.xml
./data/data/com.strava/files/DATA_disk_creation_time_vts_labl_com.strava
./data/data/com.strava/files/DATA_disk_creation_time_vts_com.strava
./data/data/com.strava/files/DATA_disk_creation_time_vts_inaka_com.strava
./data/data/com.strava/files/event_store_v2_com.strava
./data/data/com.strava/files/DATA_ServerControlledParametersManager.data.com.strava
./data/data/com.strava/databases/strava
./data/data/com.strava/databases/strava-journal
./data/app/com.strava-1
./data/dalvik-cache/profiles/com.strava
./data/media/0/Android/data/com.strava
I started to analyze the cache directory. Each activity recorded by Strava created few files in that cache directory. Here is how the content looks like for 3 activities:
root@mako:/storage/self/primary/Android/data/com.strava/cache # ls -l
-rw-rw---- u0_a111 sdcard_rw 76181 2016-07-21 20:02 cache_bd.0
-rw-rw---- u0_a111 sdcard_rw 17540 2016-07-24 08:33 cache_bd.1
-rw-rw---- u0_a111 sdcard_rw 49152 2016-07-24 08:33 cache_bd.m
-rw-rw---- u0_a111 sdcard_rw 22528 2016-07-19 19:49 cache_its.m
-rw-rw---- u0_a111 sdcard_rw 22528 2016-07-19 19:49 cache_its_ter.m
-rw-rw---- u0_a111 sdcard_rw 23257 2016-07-21 20:24 cache_r.0
-rw-rw---- u0_a111 sdcard_rw 32768 2016-07-21 20:24 cache_r.m
-rw-rw---- u0_a111 sdcard_rw 4061958 2016-07-24 08:33 cache_vts_com.strava.0
-rw-rw---- u0_a111 sdcard_rw 40960 2016-07-24 08:33 cache_vts_com.strava.m
-rw-rw---- u0_a111 sdcard_rw 2827 2016-07-21 20:02 cache_vts_inaka_com.strava.0
-rw-rw---- u0_a111 sdcard_rw 40960 2016-07-21 20:02 cache_vts_inaka_com.strava.m
-rw-rw---- u0_a111 sdcard_rw 27648 2016-07-19 19:49 cache_vts_labl_com.strava.m
drwxrwx--x u0_a111 sdcard_rw 2016-07-19 16:53 debug
The find command also return a SQLite database (./data/data/com.strava/databases/strava) which is easier to understand and contain a lot of information.
Database description
This is a listing of the tables present in this SQLite database with a short description of their content when I was able to understand it. I didn't try to analyze everything as I mainly focus of the unsynchronized activity.- activities: Information of your user's friends activities
- activities_unsynced: Unsynchronized activities
- android_metadata: Only store the language
- annual_progress_goals: Annual total distance for running biking and swimming of user's friends
- athlete_clubs
- athlete_contact
- athlete_stats: Number of activities, distance, duration and elevation gain for running, biking and swimming for recent activities, for the year and since the user uses the app for him and all his friends. NB: the numbers can be not accurate (doesn't seem to be updated frequently)
- athletes: Details about the athlete profiles visited included the profile of the user (with more details than the other athletes)
- challenge_leaderboards: Summary of the leaderboards for each challenge subscribed by the user
- challenge_participants: Information about which user's friend is participating to which challenge
- challenges: Information about the challenges available
- clubs
- comments
- dorado_impression: A unique URL to a 1x1 gif used to track something (probably Premium ads printing)
- facebook_search: Information about the user's friends found via Facebook
- feed_entries: Information about activities shown in the app (via clubs, challenges for exemple)
- followers: Information about athletes following the user
- followings: Information about the athletes followed by athletes in the 'athletes' table
- froutes
- gear: List of gear (shoes, bikes) and distance traveled with
- kudos
- live_activities
- live_activities_points
- live_athletes
- live_events
- live_location_activities_gson
- live_matches
- live_tracking_contacts
- notification_settings
- notifications: Information about app notifications
- progress_goals: Progress goals about athletes from 'athletes' table
- promo_overlay
- related_activites
- routes: Route information when they have been printed on screen (for any kind of user)
- rts_logs: Geolocalisation data for unsynchronized activities
- segments: Information about activities segments
- sensor_datum: Information ('relative_altitude', 'pause_type') for unsynchronized activities
- streams
- training_videos
- unsynced_photos
- waypoints: Details (latitude, longitude, altitude, speed, elapsed time, speed) for unsynchronized activities
- zones
Let's get my data back
The previous tables listing shows some interesting tables, like "activities_unsynced" which contains some meta data about all unsynchronized activities:
sqlite> select * from activities_unsynced;
id|updated_at|json
3|1469393124028|{"activity_id":0,"auto_pause_enabled":false,"commute":false,"distance":84499.02644972557,"elapsed_time":0,"end_timestamp":0,"guid":"REDACTED","is_private":false,"live_activity_id":0,"row_id":3,"m_end_battery_level":-1.0,"m_initial_elevation":94.42694,"m_screen_on":false,"m_screen_on_start":53574812,"m_screen_on_time":723567,"m_screen_timer_invalid":false,"m_start_battery_level":0.99,"sync_state":"UNFINISHED","manual":false,"name":"2016-07-24","photos":[],"route_id":-1,"sensor_averages":{"1":{"a":10000,"b":0,"c":0.0,"e":true},"0":{"a":10000,"b":0,"c":0.0,"e":true},"10":{"a":10000,"b":0,"c":0.0,"e":true},"18":{"a":10000,"b":0,"c":0.0,"e":true}},"should_facebook_share":false,"start_timestamp":1469374573560,"type":"Ride","video_view_id":-1,"workout_type":-1}
- distance
- elapsed time
- start timestamp
- end timestamp
- guid (unique ID of the activity)
- name
- type
- synchronization state
NB: The dot in the timestamp is missing (1469374573560 -> 1469374573.560) and the distance is recorded in meters (even if the application is configured to use miles).
An other interesting table is "rts_logs" as it contains few GPS coordinates of the user during its activity:
sqlite> select * from "rts_logs";
id|updated_at|activity_guid|json
76|-1|REDACTED|{"m_activity_guid":"REDACTED","m_log_json":"{\"latlng\":[37.74XXXXXX,-122.43XXXXXX],\"timestamp\":1469374596336,\"failure_code\":1001,\"failure_status_code\":-1}","m_type":"request"}
[...]
sqlite> select count(id) from rts_logs;
count(id)
67
This table can be used to track the user using latitude, longitude and timestamp but 67 records for a 84km/52miles ride, it's not that much.
Hopefully another table contains more detailed information about the activity, and that table is "waypoints":
sqlite> select * from waypoints;
ride_id|pos|timestamp|latiude|longitude|altitude|h_accuracy|v_accuracy|command|speed|bearing|device_time|filtered|elapsed_time|distance
REDACTED|0|1469374597000|37.74XXXXXX|-122.43XXXXXX|18.60XXXXXXXXXXX|9.0|||7.0|86.3000030517578|1469374596293|0|10241|0.0
[...]
REDACTED|17356|1469393123000|37.74XXXXXX|-122.43XXXXXX|34.X|3.0|||0.0|0.0|1469393123969|1|15800446|84499.0264497256
sqlite> select count() from waypoints;
count()
17357
We now have 17357 GPS coordinates to recreate the activity map (using OpenStreetMap or Google Maps) and do some statistics!
I hope that this understanding of the Strava SQLite database can be useful for any person in a similar situation as me (broken phone with unsynchronized activity) or for forensics analysis (lot of GPS data in it)!
No comments:
Post a Comment