AWS Timestream query to detect when I am not getting data

Question

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?

Answer 1

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.