Using SQL data to label time series (via v7 transformations?)

[svetb]

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 Time, system_id, and 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 system_id and 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_id as 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

[svetb]

I recently came back to this, as getting it working would be a game-changer for us. I made some good progress, and can more or less see a path to success, but am stuck on one last hurdle.

Firstly, the correct transformation to use is “Merge” and not “Outer Join”. To illustrate how this works:

1. In tabular form, the time-series data looks like this:
   
   
2. …and the label metadata looks like this:
   
   

Note that in the above we have two systems (assets), where each has a UUID (asset_id, and a user-friendly name (asset_name). After querying time-series data (a set of battery voltages, batt_V) for two different assets, we’d like to plot it, and label each series with the user-friendly name.

Applying the Merge transform on these two datasets yields something very close to what we need, at least in tabular form:

In the above each row contains a timestamp, the value we want to plot (batt_V), and the asset name that we want to use as series label.

Now, simply switching from a Table to a Graph visualization doesn’t really yield what we want, since all the values are in a single batt_V series, rather than split by asset:

(lol)

Based on the docs, it seems that the Labels to fields transformation is what I want to apply at this point, at least in theory. I should be able to say select asset_name as the “Value field name”, and use that to split my batt_V values into separate series, which I can then plot.

Unfortunately when I try that, I get “No options found”:

I’ve read through a few related Github issue descriptions/comments, and am none the wiser about what’s going on. This comment by Torkel states that “Labels to fields” only really works on time-series data, and this issue seems to confirm that it doesn’t work on SQL data. Indeed the documentation also says “This transformation changes time series results […]” - but it’s not clear to me what counts as “time series results”.

I played around quite a bit, e.g. with setting the InfluxDB query type to “Time series” and removing the tabular metadata altogether, but could not get the “Labels to fields” drop-down to display anything other than “No options found”.

This is on Grafana v7.5.4.

[svetb]

Quick update to the above. The “Labels to fields” transform I mentioned does now appear to also work for InfluxDB data (per my post here).

Unfortunately this doesn’t actually solve the issue, since the “labels” in “Labels to fields” need to come from a time-series database, and in the above example they don’t (that’s kind of the point).

It does appear that the missing piece is what’s described in this (currently open) issue: Support Graphing of Long (a.k.a. Tall) and Wide Format Time Series Dataframes · Issue #22219 · grafana/grafana · GitHub. I.e. what’s needed is the ability to obtain metric names from tabular data. For context, Grafana’s data frames documentation does a nice job of laying out the different possible data structures.

[dpy0929]

I seem to have figured out the solution here:

Firstly, thanks to your earlier experiments and the inspiration from @rookie099 on Stack Overflow:

Grafana state timeline panel with values (states) supplied by label

Solution:

1. Merge the two tables from your data value and label name queries (as in your posts above) to generate a table with both the user-unfriendly and user-friendly labels, which are Tagname and Name in the screenshot below (or asset_id and asset_name in your case). Format both queries as Table (not Time Series).
   

   

2. Instead of using the Label to Fields transformation, Grouping to Matrix is the one that should indeed be used. What it does is sorting the Time and Value in the merged table into new columns, based on the Column chosen (in this case, we want the Time and Value sorted by the user-friendly Name).
   

   

[dpy0929]

3. However, it seems a formatting issue with the Time field occurred during the transformation (as shown above). Therefore, I applied Convert Field Time subsequently to convert the Time field back to the format recognisable by Grafana Time Series.
   
   

[dpy0929]

4. The resulting data table now looks like a proper Time Series and can be visualised by both Time Series and State Timeline (which is my application in this case). Value mappings and thresholds can be applied normally to filter data.

Time Series:

State Timeline:

[yosiasz]

Have you looked at what influx has to offer as far as querying sql data from influx? And do all your merging in influx itself and not need to do post query data manipulation from 2 disparate database types- influx & sql

Query SQL Server | Flux 0.x Documentation

[gaborhodi]

The mentioned issue is closed now and according to @svetb , their solutions works: Lookup of device IDs for legend - #6 by svetb