Change Variable Names in InfluxDB Query of Two Measurements

Grafana InfluxDB
,
1

I’m running a local Grafana 11 and InfluxDB2. I am still learning the Flux query and script builder. I have a query of two measurements. I need to change the variable names to wind_speed and wind_direction as well as putting a math unit correction on both (from meters/sec to knots and from Radians to Degrees). How do I change this query to make those changes?

from(bucket: “austintatious”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “environment.wind.directionTrue” or r[“_measurement”] == “environment.wind.speedTrue”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “last”)

2

Welcome @bryanaustin58 to the Grafana forum.

Great explanation of your situation and problem. Pretty sure this will work?

import "math"

from(bucket: "austintatious")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "environment.wind.directionTrue" or r["_measurement"] == "environment.wind.speedTrue")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> map(fn: (r) => ({
    r with
    _value: if r._measurement == "environment.wind.speedTrue" then
        r._value * 1.94384  // Convert m/s to knots
    else if r._measurement == "environment.wind.directionTrue" then
        r._value * 180.0 / math.pi  // Convert radians to degrees
    else
        r._value
}))
  })
  |> yield(name: "last")

Also, the very first line ( import "math") is needed in the math.pi function above. If it gives you issues, you can skip the math package and instead use 57.2958

To rename the fields, you can use the Organize Fields transformation in Grafana. I also think it’s possible to rename the fields with the rename function in Flux.

Or just maybe this will work to do it all in one Flux function?

from(bucket: "austintatious")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "environment.wind.directionTrue" or r["_measurement"] == "environment.wind.speedTrue")
|> filter(fn: (r) => r["_field"] == "value")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> map(fn: (r) => ({
    r with
    _measurement: if r._measurement == "environment.wind.speedTrue" then
        "wind_speed"
    else if r._measurement == "environment.wind.directionTrue" then
        "wind_direction"
    else
        r._measurement,
    _value: if r._measurement == "environment.wind.speedTrue" then
        r._value * 1.94384  // Convert m/s to knots
    else if r._measurement == "environment.wind.directionTrue" then
        r._value * 57.2958  // Convert radians to degrees
    else
        r._value
}))
|> yield(name: "last")
3

Thanks for the prompt reply. I used your second suggestion with Flux. I see the math conversions and the field name changes worked. The two fields and values are being processed. However…

For further clarification, I am trying to use a Grafana Plugin called Operato Windrose (Operato Windrose plugin for Grafana | Grafana Labs). There are some specific instructions with the plugin we may still be missing.

4

Hi @bryanaustin58

So acc. to the link you sent, the field names must be wind_speed and wind_direction (the Windrose documentation calls them ‘variable’ names, but in Influx, these are called field names).

Your query has measurement names of wind_speed and wind_direction

Can you restructure how you are naming the measurement and field names? In my opinion, the measurement should be something like “WindData” and then you would have 2 field names ( wind_speed and wind_direction). The Flux query would be changed a bit to rename the fields (instead of the measurements).

5

I use pivot and then rename:

image
image947×679 63.5 KB

But in my case speed and direction are fields, just like @grant2 mentioned above.

1 Like
6

Can you give me the full query that makes this work? I just see starting with line 7. I tried to guess but failed…

7

This is just an example, because it depends on data arrangement in the database. In my case I have fields WindDirection and WindSpeed.

from(bucket: "WEATHER")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["device"] == "${DEVICE}")      
  |> filter(fn: (r) => r["_field"] =~ /^Wind*/)
  |> group(columns:["_field"])
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> keep(columns:["_field","_value","_time"])
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")  
  |> rename(columns:{WindDirection: "wind_direction", WindSpeed : "wind_speed"})