Some useful SQL to look into upgrades

I was recently doing some work to understand when server upgrades happened and compare that to when users upgraded their devices… and just wanted to share some SQL with everyone in case it might be useful in your work (or if you already have a better way!).

While there’s no easy way to know exactly when a user upgraded their device, one close proxy is to look at Telemetry data since it stores the user’s current app version and is recorded daily (in v3.12+).

I’ve included two queries: The first is just an easy way to look back and see when server upgrades happened. The second can help you understand how long users are using an old version of the app even though a new version is available to them. Upgrades include important new features, performance enhancements, as well as bug fixes so it’s important that health workers can benefit from these as soon as possible.

Server Upgrade

horti-upgrade records store information about when server upgrades occurred.

NOTE: The time here is when the upgrade started. These tombstone records also include when each stage of the upgrade occurred, but the upgrade start date is easier to extract and a close enough proxy.


SELECT
	doc#>>'{tombstone,build_info,version}' AS app_version,
	to_timestamp(((doc#>>'{tombstone,created}')::bigint / 1000)::double precision)::date AS upgrade_started
	
FROM
	couchdb
	
WHERE
	doc#>>'{tombstone,_id}' = 'horti-upgrade'
	AND doc->>'type' = 'tombstone'
ORDER BY
	upgrade_started

Server Upgrade vs. User Telemetry

For a given CHT Version, the query below compares the Server Upgrade date with the date of user’s first recorded telemetry record for that version. The results will be formatted like the screenshot below.

In the example screenshot, we see:

  1. The upgrade to 3.15.0 was done on June 1st 2022
  2. 390 users started recording telemetry records with a version of 3.15.0 on that same day (June 1st)
  3. On June 6th, 51 additional users started recording telemetry records with 3.15.0 as the version
  4. By Jun 11th, a total of 747 users had already recorded telemetry records with 3.15.0.


WITH upgrade_CTE AS
(
	SELECT
		doc#>>'{tombstone,build_info,version}' AS app_version,
		to_timestamp(((doc#>>'{tombstone,created}')::bigint / 1000)::double precision)::date AS upgrade_started
		
	FROM
		couchdb
		
	WHERE
		doc#>>'{tombstone,_id}' = 'horti-upgrade'
		AND doc->>'type' = 'tombstone'
		
), earliest_by_user_CTE AS
(
	SELECT
		doc#>>'{metadata,versions,app}' AS app_version,
		doc#>>'{metadata,user}' AS cht_user,
		min(date(concat(doc#>>'{metadata,year}','-',doc#>>'{metadata,month}','-',doc#>>'{metadata,day}'))) AS telemetry_date
	    
	FROM 
	    couchdb_users_meta
	    
	WHERE
		doc->>'type'='telemetry'
		AND doc#>'{metadata}' ? 'day' --We only care about telemetry records that have a `day` value (ie 3.12.0+)
	
	GROUP BY 
		app_version,
		cht_user
)

SELECT
	upgrade_CTE.app_version,
	upgrade_CTE.upgrade_started AS instance_upgrade_date,
	earliest_by_user_cte.telemetry_date AS first_recorded_telemetry,
	(earliest_by_user_cte.telemetry_date - upgrade_CTE.upgrade_started) AS days_between,
	count(distinct(earliest_by_user_CTE.cht_user)) AS num_users,
	sum(count(*)) OVER (PARTITION BY upgrade_CTE.app_version ORDER BY upgrade_CTE.app_version, earliest_by_user_cte.telemetry_date ASC) AS running_sum_users
		
FROM
	upgrade_CTE 
	LEFT JOIN earliest_by_user_CTE ON (upgrade_CTE.app_version = earliest_by_user_CTE.app_version)

GROUP BY
	upgrade_CTE.app_version,
	instance_upgrade_date,
	first_recorded_telemetry,
	days_between

ORDER BY
	upgrade_CTE.app_version,
	instance_upgrade_date
	;

5 Likes

Hi @michael
THank you for sharing,
In our config-muso repo we have an issue reporting some users with a very high number of docs to sync, 6 users, but we do not know who they are or how many docs they have, here the issue,j this report seems to come from some monitoring software in Medic side

Using your queries for telemetry I have found that:

user_name period_start doc_count_on_local_db
heleineba 2022-11-06 39462
mariamco 2020-07-01 27140
boureimadj 2022-10-27 23041
isaieto 2020-07-01 22024
amisi 2022-11-08 21000
ousmanedi 2022-11-02 18362
seydoute 2022-10-31 14673

But when I connect with a google chrome incognito windows I found that server side, docs to sync, are about 5 000 for these users

We have contacted the users and all of them are saying that application doesn’t have special troubles, and that they are working with

How could we interprete these results ? any idea or suggestion ?

Hi @bamatic

There are a couple of probable causes for this:

  1. Contacts were moved (because of a hierarchy change for example) but these users didn’t re-sync.
  2. These users are creating docs that they are not allowed to upload to the server - for example - by a misconfiguration, they are creating reports that they are not allowed to upload.

There could be other causes, but this would be the most likely culprits.
Only inspecting the documents that actually exist on the device would help know the cause. Is it possible to inspect these devices?

Hi @diana
I think we can get access to one of them, the other ones belong to CHW that are in Bankass, an area in Mali with only internet connexion in the main village,
I will try to locate the user that works here in Bamako I’ll come you back if I get access to the smartphone
For other phones we will ask the user to erase all data and resync
Thank you

1 Like