AWS Timestream uses a query language based on SQL. I have a Timestream database table that is receiving a heartbeat from senders every 4 minutes. The thought is to detect is something is down. The data looks like this:
Thing measure_name time measure_value::bigint
Machine1 Time 2022-06-10 20:27:51.095000000 1654892871095
Machine1 Time 2022-06-10 20:27:29.676000000 1654892849676
Machine2 Time 2022-06-10 20:25:31.654000000 1654892731654
Machine2 Time 2022-06-10 20:25:30.689000000 1654892730689
The data drops out of the table after a week.
I need to somehow write a query that determines for all things in the table if each thing is in the table does not have any heartbeats in the last x minutes. I feel like this is possible but I just don't know how. I have written a static query for a particular thing like so:
SELECT
CASE
WHEN count(Thing) > 0 THEN 'Up'
ELSE 'Down'
END AS Status
FROM "Uptime"."Uptime" WHERE time between ago(15m) and now() and Thing = 'Machine1'
It has some weaknesses though. I don't want to write a new query for each thing as they are created and remove a thing specific query when the thing is destroyed.
I guess I want to get all the things in the table and then do a query for each thing and return a list of things that are down. I just need to do this in a single query.
How?
OK, I have an answer (not necessarily the right answer). What I needed to do in a sub-query and then a main query. In the sub-query I would get the max time grouped by thing. IN the main query get the count of the things that have a max time of less then x seconds. The query looks like this:
select count(Thing) from
(
select distinct Thing, max(time) as last
from "Uptime"."Uptime"
group by Thing) as t1
where t1.last < ago(5m)
This gives a count of the number of things that we have not heard from within the last 5 minutes.