SQL Queries that used to work no longer do

Grafana PostgreSQL
1

  • What Grafana version and what operating system are you using?
    9.3.2

  • What are you trying to achieve?
    I’m trying to produce a time series with multiple metrics.

  • How are you trying to achieve it?
    This is the SQL query:

SELECT
  t.added AS "time",
  t.value,
  CONCAT(e.entity, '::', k.key) AS metric
FROM tsd t, entities e, keys k
WHERE
  e.id = t.entity_id
  AND k.id = t.key_id
  AND k.key ~ 'weather.aqi.*.avg'
  AND e.entity = '$hostname'
  AND $__timeFilter(t.added)
ORDER BY 1

  • What happened?
    I get 1 value, literally called “value” in the legend, instead of the expected (3 in this case) separate lines.

  • What did you expect to happen?
    I expect 3 lines, for the 3 different metric keys.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    No

  • Did you follow any online instructions? If so, what is the URL?
    I’ve used almost the exact same query in many other panels (set up quite some time ago) that work just fine.

It’s also worth noting that the table view shows exactly what I expect, in terms of the data and metric names.

Did something change, in terms of what Grafana expects for the “time”, “value”, and “metric” columns for a time series? I’ve been looking through the documentation and can’t find anything definitive.

Screenshot from 2023-05-18 16-31-53
Screenshot from 2023-05-18 16-31-531219×602 67 KB

2

Ok, it looks like the missing sauce here (for newer Grafana installs) is the “Prepare time series → Multi-frame time series” transformation.

One small problem after that is that the legend shows “value my_metric_name” instead of just “my_metric_name”, which is odd, but fixable. If someone knows why that is happening, I’d love to do the right thing rather than the rename fix.

1 Like
3

And for anyone that finds this later, you can rename the legend labels with the “Rename by regex” transformation with the match being value (.*) and replace being $1. Hacky, but I end up with the expected view.

4

@crustymonkey

make sure the query returns time series instead of table, no regex

image