We have a pretty straightforward (and I suspect common) use case:
- There is a timeseries (Influx) database that stores a bunch of metrics. These are tagged with the IDs of the systems they correspond to
- In a separate SQL (Postgres) database there is a table that maps each system’s ID to its name
- In Grafana, we would like to display some timeseries metrics, labeled with the corresponding system names from the SQL table
We’ve previously implemented something like this on a limited basis by having the SQL query feed a template variable. But that only really works when you are showing data for a single system ID (itself selected via a template variable) and you just need to grab a single label. It doesn’t seem to work for dynamically labeling multiple IDs.
I was excited to see whether Grafana v7’s Transformations functionality might enable this - and on the surface it looks like it should. After all, we’re basically just looking to do a join. But after some fairly comprehensive poking around, it doesn’t really get us there. Does anyone have any pointers on whether (and how) it’s possible to achieve the above?
For what it’s worth, the attempt that seemed most promising looked as follows:
- In the Query tab we go into Mixed database mode.
- We obtain a timeseries dataset with fields
value. There are let’s say 100 readings across three different system IDs. This is loaded (“formated”) as a table.
- We obtain a SQL table with the fields
name. It contains a dozen values, including all the system IDs present in the timeseries data
- In the Transform tab we select “Outer join”, and select
system_idas the field name to join on.
==> This results in a table that has a single timestamped value for each system - with the rest of the timeseries data discarded. In other words, it looks like what we get is a right join on the SQL table, rather than an outer join. In fact, it looks like this has already been reported as an issue: Transformations: Implement proper outer join · Issue #26316 · grafana/grafana · GitHub