Combine Influxdb data and mysql metadata in Grafana

Addition of metadata is not supported well in Grafana

InfluxDB enables efficient time-series data storage and retrieval and Grafana is a platform to create and share interactive, customizable dashboards. Tags, the metadata for sorting and grouping, are often not human readable and need enrichment. A simple example is a deviceid of a sensor which is stored in the database but you want to present the location or it’s nickname.

The standard solution is to centrally maintain a table with the extra metadata that is combined in the query and adds the needed datapoints to present in the dashboard. However InfluxDB is not like a traditional SQL database and finding no solutions on the internet it seemed too hard to realise.

The solution advised by Grafana and in forums is to add Transformations in Grafana to manipulate and enrich the metadata. These transformations need to be added to each dashboard and need to be repeated for each textstring that you want to add. It requires a lot of maintenance, leading to a higher chance of introducing errors and inconsistencies.

How to add metadata from another database and enrich your dashboard

However, I succeeded to combine InfluxDB data with metadata from a MYSQL database. Let’s first give you the example and explain the important parts so you can adjust it to your situation. In Grafana select as Data source “Influx-DB-Flux” and paste the code:

import "sql"
import "join"

username = "imayreadsql"
password = "donttellanybody"
mysql_server = "192.168.1.2:3306"

devices = sql.from(
  driverName: "mysql",
  dataSourceName: "${username}:${password}@tcp(${mysql_server})/sensor",
  query: "SELECT * FROM grafana",
)

data = from(bucket: "mytimeseries")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Temperature")
  |> filter(fn: (r) => r["_field"] == "temperature")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> group()

join.inner(
  left: data,
  right: devices,
  on: (l, r) => l.topic == r.topic,
  as: (l, r) => ({l with location: r.location}),
)
  |> group(columns: ["_time", "_value"], mode: "except")

Inside the “rabbit hole”

The libraries are needed to include a sql source and join tables.

The table “grafana” (line 11) in the mysql database “sensor” (line 10) contain a column “topic” (line 24 ‘r.topic’) and “location”. Change these lines with the SQL-query to suite your situation.

Now get your own Flux statement, put “data =” in front and replace the last line – containing |> yield(name="xxxx") – with the |> group() command as shown in line 19. This ungroups the data, which will be regrouped after the merge with the metadata.

The join.inner statement combines the data on the Tag topic l.topic – which existance was invisible for you in lines 14-19 but now you know – with the SQL field topic r.topic and in the output (line 25) the MYSQL-column location is included in the output.

The grafana table contains more metadata then you see now, but I decided to only expose the location, if needed you could add more columns from the table grafana: {location: r.location, description: r.nickname}.

Last line is too regroup the data into the relevant series.

Use of the metadata

An example to use the metadata is to change the Display name to ${__field.labels.location} (${__field.labels.topic}) which may result in “Kitchen (AB09)”

Further improvements

Removed from the example above, but nice to tell you: I don’t store and maintain my secrets in the code. Secrets can be stored in many ways and I chose to put the mysql database credentials as secret variables into InfluxDB. To retrieve them in Grafana simply change the code with these lines:

import "influxdata/influxdb/secrets"

username = secrets.get(key: "MYSQL_USERNAME")
password = secrets.get(key: "MYSQL_PASSWORD")
mysql_server = secrets.get(key: "MYSQL_SERVER")