Label many series by result of second query

I have a query to display many series:

SELECT mean("vpd") FROM "autogen"."measurements" WHERE $timeFilter GROUP BY time($__interval), "mac"


I have another table in the database, sensenames, which contains colums mac and name. The given name associated with a mac can be retrieved with this query:

select "name" from "sensenames" where ("mac" = macgoeshere)

Effectively what I am trying to do is replace the labels of each series with the results of the above query. It could be accomplished easily if the above query could be pasted into the alias box of the visual editor, but it does not accept queries.

The ultimate goal being to have a web page where you type in mac:alias, it gets submitted to the sensenames table, and grafana automatically re labels each series (after refreshing the page)

Is there any way to accomplish this?

It sounds like you want to use chained variables. Here are some useful starting points and example dashboards in our public sandbox :+1:

i tried both putting the mac inside /^$mac$/ but it throws a different error. is it possible that its conflicting because the columns are named the same?

This is how the tables are setup:

table: measurements
columns: mac, humidity
example: FE92C828661B, 56

table: sensenames
columns: mac, name
example: FE92C828661B, outside

when i display the graph in the original post, i want to replace the label “ruuvi_measurements.mean mac FE92C828661B” with the corresponding name from the sensenames table, “outside”.

it’s possible this isn’t helping matters

The following approach might work. You’ll probably have to make some changes to account for your specific data source.

As an example, let’s start with something like this:

We have a single query that returns a Time field, a MAC field and a Value field. When plotting it as a time series (using the Prepare time series transform in my example) we get 2 series, each labeled by a different MAC value.

Now, let’s add another query that basically returns a lookup table from MAC to Name:

(Note that it doesn’t contain a Value or Time field)

This gives us two different tables, both of which have a MAC field:

By adding a Merge transform, we can add the Name column to the first table, where the values are mapped based on the common MAC values:

Now we can hide the MAC field using the Organize fields transform, and create a multi-frame timeseries that looks like this:

The result is identical to what we started with, except now the series names come from the Name field instead of MAC

thank you, i wish you wrote the manual! i will do some testing and get back if i find any flaws :wink:

1 Like