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.

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

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

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

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 

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.