Hi all,
System:
-Linux mint virtual machine (6 xeon cores, 12gb memory)
-telegraf, mqtt broker, influxdb 2.0 and grafana (7.0) all latest versions recently (+/- 3 weeks) installed on the VM. All data is collected in the influxdb 2.0 database and then Grafana is linked to this database (works all perfectly).
BACKGROUND: For the past couple of weeks I have been learning up on the entire flux quering in my influxdb 2.0 database (I have some MQTT feeds from my solar production and my energy meter). That has been working beautifully and managed to get several sorts of math calculations / joins etc.
Very excited to hear about the flux incorporation in Grafana 7.0, I started out by copying and pasting my flux queries
ISSUE: Now my problem, when copying simple flux queries from influxdb 2.0 script editor to grafana, everything works perfectly and results are identical (cfr snippet of code below that works).
from(bucket: “pfsense”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r["_measurement"] == “mqtt_consumer”)
|> filter(fn: ® => r[“MQT_values6”] == “home/solar/inverter”)
|> filter(fn: ® => r["_field"] == “AcPowerValue”)
However, as soon as I copy somewhat more difficult queries (which work perfectly in the flux query editor in influxdb2.0), I get error issues in grafana and no output with the metric request error (cfr 2 examples below).
What can/should I do to get this working in Grafana?
Many thanks for the read!
Kind regards,
Ben
Example 1:
CODE:
import “date”
month = date.truncate(t: now(), unit: 1w)
// Function definition
Kwhcalculation = (tables=<-, x) =>
tables
|> map(fn: ® => ({ r with _value: r._value / x}))
from(bucket: “pfsense”)
|> range(start: month)
|> filter(fn: ® => r["_measurement"] == “mqtt_consumer”)
|> filter(fn: ® => r[“MQT_values6”] == “home/solar/inverter”)
|> filter(fn: ® => r["_field"] == “AcPowerValue”)
|> timeShift(duration: 2s)
|> truncateTimeColumn(unit: 1s)
|> integral(unit: 3600s, column: “_value”)
|> Kwhcalculation(x:1000.00)
|> yield()
OUTCOME:
Metric request error
Object
status:500
statusText:“Internal Server Error”
data:Object
message:“Metric request error”
isHandled:true
message:“Metric request error”
Example 2:
CODE:
// Function definition
multByX = (tables=<-, x) =>
tables
|> map(fn: ® => ({ r with _value: r._value * x}))
Zonneenergie = from(bucket: “pfsense”)
|> range(start: -1h)
|> filter(fn: ® => r["_field"] == “AcPowerValue”)
|> keep(columns: ["_value", “_time”])
|> truncateTimeColumn(unit: 1s)
|> timeShift(duration: 2s)
Electriciteitterug = from(bucket: “pfsense”)
|> range(start: -1h)
|> filter(fn: ® => r["_field"] == “electricity_currently_returned”)
|> keep(columns: ["_value", “_time”])
|> truncateTimeColumn(unit: 1s)
|> multByX(x:1000.0)
ZEenET = join(tables: {Z1: Zonneenergie,E1: Electriciteitterug}, on: ["_time"])
Electriciteitheen = from(bucket: “pfsense”)
|> range(start: -1h)
|> filter(fn: ® => r["_field"] == “electricity_currently_delivered”)
|> keep(columns: ["_value", “_time”])
|> truncateTimeColumn(unit: 1s)
|> multByX(x:1000.0)
join(tables: {E2: ZEenET,E1: Electriciteitheen}, on: ["_time"])
|> map(fn:® => ({
r with
time: r._time,
nettoverbruik: r._value_Z1 - r._value_E1 + r._value}))
OUTCOME:
Metric request error
Object
status:500
statusText:“Internal Server Error”
data:Object
message:“Metric request error”
isHandled:true
message:“Metric request error”