SELECT with subquery seems to ignore GROUP BY

#1

Hi,

I want to create a report which lists devices that have not sent data to InfluxDB for 48 hours.

In our database, the __saved field is used to store the timestamp at which the measurement was written to InfluxDB; the measurement’s InfluxDB time (timestamp) field is the time at which the measurement was recorded by the device. (Our devices will internally buffer measurements if they can’t forward them to the server immediately.) The __devhash tag is used to uniquely identify the device; furthermore, we have separate databases for each device ‘type’, mainly because the set of fields logged by a device tends to vary somewhat from device type to device type.

So I begin with a query to get the most recent timestamp from all devices. Rather than show the results for all device types I’ll show just one of them. (The sort -u is to remove all but one of the CSV “column name” lines.)

jeremy@devtest$ influx -precision rfc3339 -database solarmon  -format csv -execute "SELECT __saved FROM battery GROUP BY __devhash ORDER BY time DESC LIMIT 1" | sort -u
battery,__devhash=265145C4C6F76C27082E0257E4EADEE6,2016-08-13T01:12:29Z,1493867511
battery,__devhash=5905043CCBF15F0113B4AD93B24D5D3B,2017-03-31T13:29:00Z,1493868682
battery,__devhash=A3A4850704CA39F3C733BDAD64741B07,2017-05-11T07:44:00Z,1494488687
battery,__devhash=AC530DCBCE7754FAC2E203824C5269A6,2016-02-18T00:04:00Z,1493866969
name,tags,time,__saved

So far so good, but I don’t want to see the devices which have logged data in the past 2 days. In the battery data above, only one device (A3A4850704CA39F3C733BDAD64741B07) has logged data recently.

I thought a subquery would do the job, but the results are confusing … surely there should be just one result per __devhash ?

jeremy@devtest$ influx -precision rfc3339 -database solarmon  -format csv -execute "SELECT * FROM (SELECT __saved FROM battery GROUP BY __devhash ORDER BY time DESC LIMIT 1) WHERE time<1494340200s"
name,time,__devhash,__saved
battery,2016-02-18T00:04:00Z,AC530DCBCE7754FAC2E203824C5269A6,1493866969
battery,2017-05-09T14:29:00Z,A3A4850704CA39F3C733BDAD64741B07,1494340156
battery,2017-03-31T13:29:00Z,5905043CCBF15F0113B4AD93B24D5D3B,1493868682
battery,2017-03-26T23:59:00Z,A3A4850704CA39F3C733BDAD64741B07,1493868655
battery,2016-08-13T01:12:29Z,265145C4C6F76C27082E0257E4EADEE6,1493867511
battery,2016-07-24T02:15:00Z,A3A4850704CA39F3C733BDAD64741B07,1493867457

The battery that is not of interest (i.e. the one with recent data, that I want excluded from the listing) is shown three times; the others are listed only once each and the correct measurement timestamp is shown for each. What’s going on?

Thanks
Jeremy Begg

#2

Minor correction to the description (above): we have separate measurements for each device ‘type’ (not separate databases for each, as I originally wrote).