Dashboard

These queries generally need to be set up by an administrator but have been included here as a reference to demonstrate some of the mSupply cold chain data that can be made available on the mSupply Dashboard.

Sensor Graph

Query

WITH temp AS (
SELECT
s.name,
CONCAT(TO_CHAR(date,'YYYY-MM-DD'),' ', TO_CHAR(time,'HH24:MI:SS'))::timestamptz AS log_datetime,
tl.temperature
FROM temperature_log tl
JOIN sensor s ON tl.sensor_id = s.id
WHERE
CONCAT(TO_CHAR(date,'YYYY-MM-DD'),' ', TO_CHAR(time,'HH24:MI:SS'))::timestamptz >= $__timeFrom()
)
SELECT
$__time(log_datetime),
name,
temperature
FROM temp
ORDER BY log_datetime

Unacknowledged Breaches

Query

WITH breaches AS (
SELECT
store_id,
location_id,
sensor_id,
s.name,
SUM(CASE WHEN type = 'HOT_CONSECUTIVE' or type = 'COLD_CONSECUTIVE' then 1 else 0 end) AS count_consecutive
FROM temperature_breach
JOIN sensor s ON s.id = temperature_breach.sensor_id
WHERE CONCAT(TO_CHAR(start_date,'YYYY-MM-DD'),' ', TO_CHAR(start_time,'HH24:MI:SS'))::timestamp >= $__timeFrom()
AND acknowledged='false'
GROUP BY store_id, location_id,sensor_id, s.name)
SELECT COUNT(*) FROM breaches

Batches affected

Query

WITH batch_count AS (
SELECT
temperature_breach.store_id,
temperature_breach.location_id,
sensor_id,
s.name,
il.id,
SUM(CASE WHEN type = 'HOT_CONSECUTIVE' or type = 'COLD_CONSECUTIVE' then 1 else 0 end) AS count_consecutive
FROM temperature_breach
JOIN sensor s ON s.id = temperature_breach.sensor_id
JOIN item_line il ON il.location_id = temperature_breach.location_id
WHERE CONCAT(TO_CHAR(start_date,'YYYY-MM-DD'),' ', TO_CHAR(start_time,'HH24:MI:SS'))::timestamp >= $__timeFrom()
AND acknowledged='false'
GROUP BY temperature_breach.store_id, temperature_breach.location_id,sensor_id, s.name, il.id
)
SELECT COUNT(*) FROM batch_count

Sensor battery life

Query

select
sum(CASE WHEN batterylevel < 20 then 1 else 0 end) as "Low battery",
sum(CASE WHEN batterylevel < 50 then 1 else 0 end) as "Battery to be replaced within month",
sum(CASE WHEN batterylevel >= 50 then 1 else 0 end) as "Battery OK"
from sensor s
where s.is_active = true

Telegram notifications for Cold Chain

General instructions are here: https://gist.github.com/ilap/cb6d512694c3e4f2427f85e4caec8ad7

But note that you need to invite your bot, not the bot father to the channel

Temperature logs rely on the alternate server in mSupply Desktop to be synced from the tablet to the server. If the alternate server is not running then no logs will be synced (and hence nothing will show on the Dashboard).

Can see if the alternate server process is really running by checking port 8081 in Windows cmd with ​​netstat -ano | findstr :8081 In this case process with ID 2188 is running ok.