Is it possible to check on a InfluxDB database when a record is missing?

Question

I have a problem concerning an InfluxQL request.

Here the thing, I have a database and I want to get the id of my device or the value 0 when it does not send bytes during an hour.

Here is an example of the structure of my database

time                 bytes_in   id
----                 --------   --
2021-01-01T05:06:18Z 37         274 
2021-01-01T05:07:52Z 37         274
2021-01-01T19:55:37Z 37         274
2021-01-01T20:55:37Z 37         274
2021-01-01T21:08:25Z 37         274
2021-01-01T21:10:00Z 37         274
2021-01-01T21:55:37Z 37         274
2021-01-01T22:55:37Z 37         274
2021-01-01T23:55:37Z 37         274
2021-01-01T23:57:14Z 37         274

So I have two tags (time and id) and one field (bytes_in). I tried different things to get my id when no any message is sent.

1. I tried this :

SELECT  id, SUM(bytes_in) as sum FROM db where sum = 0 GROUP BY id, time(1h) 

But with this I got an error : “mixing aggregate and non-aggregate queries is not supported”. I don’t understand why I can’t select the id when I group the data on it.

2. I tried to check when my sum is equal to 0 with a subquerie

SELECT total FROM (SELECT SUM(bytes_in) as total FROM db WHERE ((time>= '2021-01-01') AND (time <= '2021-01-02')) GROUP BY id, time(1h)) WHERE total = 0;

But this return nothing. I tried without the second WHERE condition but it was the same, the SELECT returns only the no-null data.

3. I tried the fill(0) solution (https://stackoverflow.com/a/52084689). But my database is big and the request ran to an out of memory error.

4. I tried to test on the returning value of the SUM() function.

SELECT  SUM(bytes_in) as total FROM db WHERE ((time >= '2020-01-01') AND (time <= '2021-01-02') AND (SUM(bytes_in) = 0 )) GROUP BY prod_id,time(1h)  

But with the request return an error : invalid function call in condition: sum(bytes_in). Is There a way to test in a WHERE clause the result of a function ?

If you want to try it by yourself, here is a micro part of my database on witch you can try :

curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609477578000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609477672000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609530937000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609534537000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609535305000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609535400000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609538137000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609541737000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609545337000000000'
curl -i -XPOST 'http://localhost:8086/write?db=mydb' --data-binary 'db,id=274 bytes_in=37 1609545434000000000'

I hope I am clear enough in my explanation.

Thanks !

Answer 1

You can use show tag values from db with key="id" to get all ids, and SELECT SUM(bytes_in) FROM db WHERE time > now()-1h GROUP BY id to get Sum. If id has values in last one hour,it will appear in select result,and you can get id from tags area in result.And if you use time group, you can get series when it exist in "where clause" you are searching .