Multiple time series in concatenated fields influxdb2

Grafana InfluxDB
, , ,
1

  • What Grafana version and what operating system are you using?
    Grafana V9.05 on Raspberry pi 3 a+ running ubuntu server 64x arm

  • What are you trying to achieve?
    I’m trying to make a easy viewable table from data from 3 different readings (temperature, air pressure and humidity) from 2 different sensors

  • How are you trying to achieve it?
    I copied query from Influxdb2 and put it in a table in Grafana and under transformations I choose concatenate fields

  • What happened?

    image
    image1829×509 45.5 KB

    I get time reading before each measurement and it’s taking too much space and its totally pointless… I have tried putting time readings at the end but it still makes the table too big since they are all the same and I need only one of them

  • What did you expect to happen?
    I need only one time stamp for each row

Influxdb query looks like this:

from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "${merilec}" )
  |> filter(fn: (r) => r["_field"] == "humidity" or r["_field"] == "pressure" or r["_field"] == "temperature")
  |> filter(fn: (r) => r["senzor"] == "crn" or r["senzor"] == "rdec")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

I would really appriciate some help :smiley:
thanks in advance

2

It would appear you have built 4 streams of data using the Flux query. I believe if your timestamps in each stream (table) match, then you can do a join() on the timestamp, but I am not sure. Maybe start with this video if you have not already seen it.

PS: Basically, my approach would be to work on the query, and leave out Grafana transformations. I too get frustrated with trying to work them out to my liking and prefer to just address it in the Flux query if possible.

3

So if I’m understanding correctly it’d be better to have muliple querys for each of the measurements? and then I should join them on time so I get only one table?
Also if I don’t concatenate querys then I get select pane with only name “raspberry” like 6 times because I have 2 sensors and 3 measurements for each one of them and I cant append name of measurement and sensor name so it would be like:
raspberry1 humidity crn
raspberry1 temperature crn
raspberry1 pressure crn
raspberry1 humidity rdec
raspberry1 temperature rdec
raspberry1 pressure rdec
…
here is a photo of how it looks without concantation:

image
image1558×550 27.3 KB

that right there is very unpractical for anything

4

I managed to achieve this by making 6 different quarries and outer joining them by time

see the result:

image
image1740×517 42.8 KB

I only wish there was a way to change those column names now to :
$merilec (variable name) temperaure crn
$merilec (variable name) humidity crn
$merilec (variable name) temperaure rdec
$merilec (variable name) humidity rdec

1 Like
5

maybe more tips with influxdb query and grafana :

You can reformat column name with label to field transformation (to unwrap it),
and override the names with organize transformation
and since is all about your rashberry 3 you could use $merilec to name the table panel

6

@skvarcmatevz Nice job. Can you post your Flux query just in case others have this issue in the future?

7

sure… I just made 6 of them switching field (temperature, pressure, humidity) and sensor so each of crn and rdec have those 3:
Query a:

from(bucket: "test")
      |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
      |> filter(fn: (r) =>  r["_measurement"] == "${merilec}" and r["_field"] == "pressure" and r["senzor"] == "crn")
      |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
      |>yield(name: "kind")

Query B:

from(bucket: "test")
      |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
      |> filter(fn: (r) => r["_measurement"] == "${merilec}" and r["_field"] == "temperature" and r["senzor"] == "crn")
      |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
      |> yield(name: "mean")

etc…
and then under transform I selected outer join by time like so:

image
image1535×469 46 KB

8

Hello @skvarcmatevz,

You can definitely get close enough sometimes with flux in the backend without having to “bother” the client side with this “post-processing” by using transformations.

With your original query, try adding the following after the ‘aggregation’ line:

  |> map(fn: (r) => ({ r with 
    _field: strings.joinStr(arr: [r._measurement, r._field, r.senzor], v: "_")  
  }))
  |> keep(columns: ["_time", "_measurement", "_field", "_value"])
  |> schema.fieldsAsCols()
  |> yield(name: "mean")
  1. Field names
    I used strings.joinStr() here through the map() function in order to rename each field in the way I think you wanted them…joined by underscores ‘_’. So with this you should hopefully get field names like: “raspberry3_temperature_crn” and so on…
  1. One time stamp for each row
    Later I used schema.fieldsAsCols() in order to transform everything into 1 table by flipping all the _field name values in to column names against the _time column.

Btw, these 2 functions need their own library, so add them in the beginning.
Here’s what it could look like in 1 whole flux-query:

import "strings"
import "influxdata/influxdb/schema"

from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "${merilec}" )
  |> filter(fn: (r) => r["_field"] == "humidity" or r["_field"] == "pressure" or r["_field"] == "temperature")
  |> filter(fn: (r) => r["senzor"] == "crn" or r["senzor"] == "rdec")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({ r with 
    _field: strings.joinStr(arr: [r._measurement, r._field, r.url], v: "_")  
  }))
  |> keep(columns: ["_time", "_measurement", "_field", "_value"])
  |> schema.fieldsAsCols()
  |> yield(name: "mean")

Maybe it’s not needed anymore, but let me know if it works when you get a chance.