Operato Windrose with influxDB query not showing values

Hi,

I am trying to setup one Operato Windorose using influxDB query but although the query it self gives back values for wind_speed and wind_direction it is not shown in the windrose, se my query:

from(bucket: "test_enviro")
  |> range(start: -1d)
  |> filter(fn: (r) => r._measurement == "rpi-bme280")
  |> filter(fn: (r) => r._field == "wind_speed" or r._field == "wind_direction")
  |> filter(fn: (r) => r.location == "xxxx")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")

In the description of the plugin it mentioned SQL query:

SELECT 
    wind_weather_data.speed AS wind_speed, 
    wind_weather_data.direction AS wind_direction
FROM wind_weather_data
WHERE wind_weather_data.time BETWEEN '2023-05-02T00:00:00Z' AND '2023-05-02T023:59:59Z'

According to the description wind_speed and wind_direction are " …are fixed and cannot be changed". And I do have the same variables names as we can see in the influxDB query.

Update: table view of the query in my influxDB

and the query in influxDB:

What do I miss?

can you show is the table view of your flux query?

Are those new pictures what you where asking for in influxDB?

1 Like

Have a similar issues with that.

Guess, this plugin doesn’t work with InfluxDB flux language results?

from(bucket: "smarthome")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "climate")
  |> filter(fn: (r) => r["_field"] == "windstrength")
  |> map(fn: (r) => ({r with _value: r._value * 0.27778}))
  |> map(fn: (r) => ({r with _field: "wind_speed"}))
  |> last()
  |> keep(columns:["_time", "_field", "_value"])
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")

from(bucket: "smarthome")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "climate")
  |> filter(fn: (r) => r["_field"] == "windangle")
  |> map(fn: (r) => ({r with _field: "wind_direction"}))
  |> last()
  |> keep(columns:["_time", "_field", "_value"])
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")

Now that is a disappointment :frowning:

maybe you want to use

or dynamic text plugin with polystat

So, after I saw also the Apache ECharts I took the the other way and now I have a dedicated mysql db only for wind data. My Windrose is ready only some fine tuning is needed for example how to have a different time range only for the windrose. I mean I would like to show only the wind data of the last 5m and not for the other panels the last 6h?

Any idea?

image

OK, I got something working now:

SELECT 
  $__timeGroup(time_dt, '1m') AS "time",
  wind_speed,
  wind_direction
FROM windDB.wind_data
WHERE location = 'xxxx' AND time_dt BETWEEN (DATE_SUB(UTC_TIMESTAMP(),INTERVAL 10 MINUTE)) AND UTC_TIMESTAMP()
1 Like

You can still use influxdb

Hm, out of curiosity can you please post a solution for influxDB and Operato Windrose. Thanks in adv.

Hi, I’d really appreciate a working example for influxDB and Operato Windrose too. Every time I try influxDB I get an error, even if it’s a single line of code. Thanks in advance.

Hi @sailgrafana what do you mean by a working example for influxDB? Do you mean a working query and panel? or a working plugin?

Also it is best if in a future time you post the error you are getting since a screenshot can only show you there’s in a error but not what kind and there could be many many types of errors

Hi,

it would be interesting to know at all, whether there is anybody who have a working example with this plugin using

  • InfluxDB 2.x.y
  • Grafana 10.x.y
  • Using an Flux language query instead of InfluxQL (or SQL from a different data source)

Thanks for any response!

1 Like

With this query I was able to rename the _field according to the required naming

from(bucket: "smarthome")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "climate")
  |> filter(fn: (r) => r["_field"] == "windangle" or r["_field"] == "windstrength")
  |> map(fn: (r) => ({r with
    _field: if r._field == "windangle" then "wind_direction" else if r._field == "windstrength" then "wind_strength" else r._field
  }))
  |> keep(columns:["_time", "_field", "_value"])
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")

No error message appears anymore. But even the query provides the right data, the windows doesn’t show anything:

This is how my solution looks like:

from(bucket: "smarthome")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "climate")
  |> filter(fn: (r) => r["_field"] == "windangle" or r["_field"] == "windstrength")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> rename(columns: {windangle: "wind_direction", windstrength: "wind_speed"})
  |> keep(columns:["_time", "wind_direction", "wind_speed"])
  |> yield(name: "last")

Pivoting and renaming to the right columns were the key to resolution!

1 Like

Hi @academo , apologies. The actual error message was:

“Status: 500. Message: invalid: compilation failed: error @1:1-1:27: expected comma in property list, got STRING error @1:7-1:26: string literal key MyBoatData must have a value”

And yes, I meant a working example of Operato Windrose working with Grafana and InFluxDB.

Thank you

Your flux query is incomplete in both examples you provided

@djiwondee has solved this issue. You need to pivot so that you have the proper column names instead of _field

Hi djiwondee

I wondered if you’d be willing to help to fix my Operato WindRose as I’m new to InFluxDB

Unfortunately for me my wind data is stored as ‘measurement’ rather than ‘field’ and I have no control over this (see below):-

I tried using your working example and simply substituting ‘measurement’ for ‘field’ and renaming the bucket etc. but this didn’t work. I’m guessing that my syntax is to blame? Or perhaps the fact that there are two tables (0 and 1)? but the specific error was:

“Status: 500. Message: invalid: compilation failed: error @6:21-6:124: expected comma in property list, got DOT error @6:26-6:74: unexpected token for property key: DOT (.) error @6:79-6:123: unexpected token for property key: DOT (.)”

Any advise you can offer would be greatly appreciated!

from(bucket: “MyBoatData”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “self.environment.wind.speedApparent” or r[“_measurement”] == “self.environment.wind.angleApparent”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> pivot(rowKey: [“_time”], columnKey: [“_measurement”], valueColumn: “_value”)
|> rename(columns:{self.environment.wind.angleApparent:“wind_direction”,self.environment.wind.speedApparent:“wind_speed”})
|> keep(columns:[“_time”,“wind_direction”,“wind_speed”])
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> yield(name: “last”)

Hello @sailgrafana ,

your particular “problem” seems to be the value of _measurement

Status: 500. Message: invalid: compilation failed: error @6:21-6:124: expected comma in property list, got DOT error @6:26-6:74: unexpected token for property key: DOT (.) error @6:79-6:123: unexpected token for property key: DOT (.)”

I would avoid values with a “.” (DOT) in it. I guess, this will be not right processed in flux functions.

Try to substring the _measurement values

import "strings"
from(bucket: “MyBoatData”)
...
|> map(fn: (r) => ({r with _measurement: strings.substring(v: r._measurement, start: 22, end: 35)}))

Don’t know whether I have counted the number of chars for start/end correctly. See: strings.substring() function | Flux Documentation for reference.

You can also try to store only angleApparent resp. speedApparent into the Influx from your data source. In general the values angleApparent resp. angleApparent should be stored in the “_field” column. and _measurement should be more generic, e. g. “climate” or even “wind”. But if you can achieve this within your data load task into Influx DB, you could then solve the renaming of the values to *wind_speed" and wind_direction itself. You then only need the pivoting and renaming:

|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
...
|> rename(columns: {angleApparent: "wind_direction", angleApparent: "wind_speed"})

Thank you for your help, I’ll have another look at it