Queries for On-going Heartbeat Monitoring

HB-monitoring.md

version: 1

Has Recent Data / Lag

# anything in the last 24 hours
# This should freak out if it's 0.
# CATCHES:  complete system failure

SELECT count(received_ts)
FROM heartbeat_answer
WHERE received_ts > Date_sub(now(), interval 24 hour);

Votes are Missing

# votes Missign
# should freak out if votes are 0 for any 'large' cell.

SELECT
sum(score is not NULL) as nvoted,
DATE_FORMAT(received_ts,'%Y-%m-%d') as ydm,
version,
channel,
100*sum(flow_began_ts > 0) / count(received_ts) as pct_began,
100*sum(flow_offered_ts >0) / count(received_ts) as pct_offered,
100*sum(flow_voted_ts > 0)/ count(received_ts) as pct_voted,
100*sum(flow_engaged_ts > 0) / count(received_ts) as pct_engaged,
count(received_ts) as N
FROM heartbeat_answer
WHERE
received_ts > DATE_SUB(now(), interval 1 day)
AND is_test=0
AND survey_id="heartbeat-by-user-first-impression"
AND (locale='en-us')
GROUP BY version, channel, ydm
HAVING
  N >= 50
  and nvoted = 0

ORDER BY
  channel,
  version,
  ydm
;

Expect Responses from Versions, but they are missing...

(This one needs to pull the list of 'current' releases and check that they all exist.)