No Join function support in Grafana?

Grafana InfluxDB
1

  • What Grafana version and what operating system are you using?
    9.4.13

  • What are you trying to achieve?
    I’m triyng to set up an alert rule consisting of 3 querys and a math expression.

  • How are you trying to achieve it?
    One of the three queries consists the measured values, the second the max values the third the min values. The math expression will compare them and trigger the alerts.
    Due to the data structure in InfluxDB Bucket I have to use the join.inner() function within the query.

  • What happened?
    Error Msg:
    Failed to evaluate queries and expressions: input data must be a wide series but got type long (input refid)

  • What did you expect to happen?

The series getting displayed in the preview window like when I run those queries on the influxdb data explorer.

  • Can you copy/paste the configuration(s) that you are having problems with?

Query1 (Measured Values):

import “join”

AvgValues = from(bucket: “SmartGraph Sensor Platform”)
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “Avg”)
|> group(columns: [“_time”])

HystValues = from(bucket: “SmartGraph Sensor Platform”)
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “AlarmHyst”)
|> filter(fn: (r) => r[“_value”] == 10)
|> group(columns: [“_time”])

join.inner(
left: AvgValues,
right: HystValues,
on: (l, r) => l.SensorID == r.SensorID,
as: (l, r) => ({l with field1: l._value}),
)
|> group(columns: [“SensorID”])

Query2 (Max Values):

import “join”

AvgValues = from(bucket: “SmartGraph Sensor Platform”)
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “AlarmMax”)
|> group(columns: [“_time”])

HystValues = from(bucket: “SmartGraph Sensor Platform”)
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “AlarmHyst”)
|> filter(fn: (r) => r[“_value”] == 10)
|> group(columns: [“_time”])

join.inner(
left: AvgValues,
right: HystValues,
on: (l, r) => l.SensorID == r.SensorID,
as: (l, r) => ({l with field1: l._value}),
)
|> group(columns: [“SensorID”])

Query3:

import “join”

AvgValues = from(bucket: “SmartGraph Sensor Platform”)
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “AlarmMin”)
|> group(columns: [“_time”])

HystValues = from(bucket: “SmartGraph Sensor Platform”)
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “AlarmHyst”)
|> filter(fn: (r) => r[“_value”] == 10)
|> group(columns: [“_time”])

join.inner(
left: AvgValues,
right: HystValues,
on: (l, r) => l.SensorID == r.SensorID,
as: (l, r) => ({l with field1: l._value}),
)
|> group(columns: [“SensorID”])

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

see above

  • Did you follow any online instructions? If so, what is the URL?
2

Your problem is not the Join function, I had the same problem when I upgraded some version of Grafana with the new alert manager.

I don’t remember what I changed in the query to fix id but was related to the returned data, maybe it had null values or needed to convert _value column to something, let me check my backup and compare the queries before and after the update

3

Ok, I kind of need to see your result to have a better Idea but I have a hunch on the problem and the solution.

you have multiple columns which are the values you want per timestamp so it’s like a pivoted result, so you need to unpivot to add the _field and _value columns for each datapoint.

import "experimental"


      |> experimental.unpivot()

The only caveat is that unpivot is only available on Influx2.4 and above.

4

Hi fercasjr,

Thanks for your suggestion. It’s good to know that it’s not the join function that’s at fault.

I gave it a try but unfortunately i get an error message saying that unpivot cannot have _value or _field as an input…

What I’m trying to accomplish is basically selecting all data with the ValueType = Avg that also has Valuetype = AlarmHyst with the same SensorID… if it makes sense.

I think I have another idea how to solve it using the filter function

I’ll keep on tinkering.

Thx

5

drop _field and _value if you have those columns or rename it, also grouping may be necessary.

It will be easier to help you If you could share a screenshot of your data table output.

unpivot will take any column that you have and copy its name as _field and its _value as its value. then it will split on many data streams as columns you have. (Columns other than _time or any other than the group ones).

6

Also you maybe could use pivot instead of do 3 different queries and joining each time.

Its a guess because I do not know your data schema but try to do something like this:

 from(bucket: “SmartGraph Sensor Platform”) //Everythin is pulled from the same bucket
|> range(start: -3h)
|> filter(fn: (r) => r[“ValueType”] == “Avg” or r[“ValueType”] == “AlarmHyst” or  r[“ValueType”] == “AlarmMax” or  r[“ValueType”] == “AlarmMin”) //use or to select multiple "Tags" on the same query
|> group(columns: [“SensorID”], mode:"by") // group by sensorID, this way all will by grouped by SensorID
|> pivot(rowKey:["_time"], columnKey: ["ValueType"], valueColumn: "_value") //Use Pivot to have one column per previous result on the data tables streams
|> filter(fn: (r) => r[“AlarmHyst”] == 10 ) //use the filters that you need.
|> filter(fn: (r) => exists r[“AlarmMax”]) //if there is a chance you have null values use this
|> filter(fn: (r) => exists r[“AlarmMin”] ) //if there is a chance you have null values use this
|> filter(fn: (r) => exists r[“Avg”])           //if there is a chance you have null values use this
|> experimental.unpivot()    //unpivot will convert each column in its own data stream (_field. _value, _time)
|> yield(name: "custom-name") //display results
1 Like
7

this works out of the box, nice one without knowing the data structure :smile:

While I was tinkering I got it to work using the pivot function as well but the problem is with the pivot function i lose all the extra tags which I use as alerttemplates in the summary etc…

My initial query from the first post though… If i run it in InfluxDB UI I cannot see what’s wrong with the output data… if I drop _field and _value a whole lot of tables get generated with different tag combinations… I’m thinking I must be missing something very simple and it should work…

8

it seems like the pivot functions retains the group key, so I added all the additional tags to the group function and then added the pivot… it seems like it’s going to work, need to test if i can access the tags in the template variables…

Added this to my query:

|> group(columns: [“SensorID”,“SensorDescription”,“DeviceID”,“DeviceDescription”,“_measurement”])

|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)

Now it seems to work