Getting data with last values of different fields

Fluxlang
1

Hi,

I’m trying to pull data from InfluxDB 2.0 . Expecting it to be one table grouped by DeviceId and having columns with last each device GPS position Latitude, Longitude and Temperature. Like this:

_time, Device, Latitude, Longitude, Temperature
2019-11-11 08:36:45 GMT+2, 866425030437538, 54.234, 25.965, 17
2019-11-11 08:36:45 GMT+2, 111425030437539, 54.134, 25.165, 22

I pushed data to InfluxDB using line protocol:

box,DVC=“866425030437539”,CNTR=“LV”,CNT=“Riga”,CTY=“Riga”,STR=“Briviba” OPR=“21601”,NTBND=“GSM”,RSSI=-60i,ACDC=true,BT1=4260i,BT2=100i,LAT=54.234,LNG=25.965,ACC=539i,UPTM=844i,TMP1=17.0,TMP2=12.0

My flux request looks following:

from(bucket: “boxBucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “box” and
(r._field == “LNG” or r._field == “LAT” or r._field == “TMP1”))
|> group(columns: [“DVC”])
|> pivot(
rowKey:[“_time”],
columnKey: [“_field”],
valueColumn: “_value”)

My problem is that I cannot figure out how to show only last device position. last() does not work for me like I think it should

2

So the answer was:

  1. group by DVC (this will split answer to 1 table per each DVC)
  2. include DVC column in each table
  3. limit to 1 row per table
  4. and ungroup back to single table

from(bucket: “boxBucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r._measurement == “box” and
(r._field == “LNG” or r._field == “LAT” or r._field == “TMP1”))
|> group(columns: [“DVC”])
|> pivot(
rowKey:[“_time”,“DVC”],
columnKey: [“_field”],
valueColumn: “_value”)
|> limit(n:1)
|> group(columns: )

1 Like