# Using SQL data to label time series (via v7 transformations?) **URL:** \ **Category:** Time Series Panel\ **Tags:** postgres, influxdb\ **Created:** [August 6, 2020, 10:02pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774 "2020-08-06T22:02:18Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![svetb](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/svetb/32/3307_2.png) [@svetb](https://community.grafana.com/u/svetb)\ **Post date:** [August 6, 2020, 10:02pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/1 "2020-08-06T22:02:18Z")
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](https://github.com/grafana/grafana/issues/26316) ---
**Author:** ![svetb](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/svetb/32/3307_2.png) [@svetb](https://community.grafana.com/u/svetb)\ **Post date:** [May 28, 2021, 1:03pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/2 "2021-05-28T13:03:42Z")
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: ![Screen Shot 2021-05-28 at 2.20.20 PM](https://us1.discourse-cdn.com/grafana/original/3X/d/4/d457fa9772bdfad6157d8407da58fb6f32216e33.png) 2. …and the label metadata looks like this: ![Screen Shot 2021-05-28 at 2.21.30 PM](https://us1.discourse-cdn.com/grafana/original/3X/7/e/7ea48f3a0f3d8c8495c5661b58003e18f81e9e6e.png) 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: ![Screen Shot 2021-05-28 at 2.23.35 PM](https://us1.discourse-cdn.com/grafana/original/3X/3/5/3588e89fd1cdf1d484e96d24a45d1a15b1d0af69.png) 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: ![Screen Shot 2021-05-28 at 2.27.39 PM](https://us1.discourse-cdn.com/grafana/original/3X/7/e/7e6ef6d714323e90c364aba728a8bc75095b42e6.png) (lol) Based on the docs, it seems that the [Labels to fields](https://grafana.com/docs/grafana/latest/panels/transformations/types-options/#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”: ![Screen Shot 2021-05-28 at 2.43.43 PM](https://us1.discourse-cdn.com/grafana/original/3X/e/2/e232a9f2fc0dd2adf8ad2ea048c7321057d5ad5b.png) I’ve read through a few related Github issue descriptions/comments, and am none the wiser about what’s going on. [This comment](https://github.com/grafana/grafana/issues/26767#issuecomment-668121430) by Torkel states that “Labels to fields” only really works on time-series data, and [this issue](https://github.com/grafana/grafana/issues/29016) 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. ---
**Author:** ![svetb](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/svetb/32/3307_2.png) [@svetb](https://community.grafana.com/u/svetb)\ **Post date:** [July 27, 2021, 12:52pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/3 "2021-07-27T12:52:55Z")
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](https://community.grafana.com/t/example-of-labels-to-fields-transform-with-influx-data/48192/5)). 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](https://github.com/grafana/grafana/issues/22219). I.e. what’s needed is the ability to obtain metric names from tabular data. For context, Grafana’s [data frames](https://grafana.com/docs/grafana/latest/developers/plugins/data-frames/) documentation does a nice job of laying out the different possible data structures. ---
**Author:** ![dpy0929](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@dpy0929](https://community.grafana.com/u/dpy0929)\ **Post date:** [December 21, 2022, 10:47pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/4 "2022-12-21T22:47:47Z")
I seem to have figured out the solution here: Firstly, thanks to your earlier experiments and the inspiration from @rookie099 on Stack Overflow: > 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`). ---
**Author:** ![dpy0929](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@dpy0929](https://community.grafana.com/u/dpy0929)\ **Post date:** [December 21, 2022, 10:48pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/5 "2022-12-21T22:48:26Z")
1. 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**. ![After converting](https://us1.discourse-cdn.com/grafana/original/3X/e/e/ee5db43176ea5ac0c81011a30a2e4427d17c10db.png) ---
**Author:** ![dpy0929](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@dpy0929](https://community.grafana.com/u/dpy0929)\ **Post date:** [December 21, 2022, 10:48pm UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/6 "2022-12-21T22:48:58Z")
1. 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: ![Time Series](https://us1.discourse-cdn.com/grafana/original/3X/3/b/3b5f1e41555fdffd30adbdacf529602ba26d2021.png) State Timeline: ![State Timeline](https://us1.discourse-cdn.com/grafana/original/3X/6/0/607eadd77ed4a824817ef9451348222bc079bae0.png) ---
**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\ **Post date:** [December 22, 2022, 2:12am UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/7 "2022-12-22T02:12:03Z")
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](https://docs.influxdata.com/flux/v0.x/query-data/sql/sql-server/)** > > Use sql.from() with the sqlserver driver to query SQL Server. ---
**Author:** ![gaborhodi](https://avatars.discourse-cdn.com/v4/letter/g/ec9cab/32.png) [@gaborhodi](https://community.grafana.com/u/gaborhodi)\ **Post date:** [January 12, 2023, 9:52am UTC](https://community.grafana.com/t/using-sql-data-to-label-time-series-via-v7-transformations/34774/8 "2023-01-12T09:52:39Z")
The mentioned issue is closed now and according to @svetb , their solutions works: [Lookup of device IDs for legend - #6 by svetb](https://community.grafana.com/t/lookup-of-device-ids-for-legend/60401/6)