I want a single panel that can display multiple metrics simultaneous using a variable.
I set up the variable, it works, I see the tables being returned when I run the query from the variable settings page.
In the panel query only one metric is displayed at a time. I do have the variable settings enable for multi-value.
This allows me to select more than one metric but the panel will only display one no matter how many are selected.
Does anyone know how to make this work?
Thanks.
So in Table View of panel you can see several rows? If you are using Stat panel try changing Show to All values:
I want to be able to select multiple metrics from the drop down and have them be displayed simultaneously.
This feels a bit hacky but it works. If you duplicate the variable and have a panel query for each variable you can have multiple metrics displayed at the same time.
what is your datasource and please share your query. the issue is usually a filter clause for your datasource that does not properly account for multi values (usually not a grafana issue)
You have a query error when you choose several values (notice red triangle in the top left corner).
Depending upon your datasource and how your query is written, using advanced variable format options (i.e. '${CM_Metrics:regex}') may help
The queries are postgresql queries.
This is the query I’m using now for a single variable and it works.
SELECT
t.time AS time,
AVG(t.value) OVER (ORDER BY t.time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS “Value”,
‘${m1}’ AS “Metric”
FROM
${m1} AS t
WHERE
$__timeFilter(t.time);
When I enable mult select for the variable it fails.
Is it something with my query and the filter? What should it be? Thanks.
click on query inspector and see what your query looks like
the design decision used to have separate table for each metric is coming back to bite you
will your be adding new tables for new metrics in the future?
The metric m1 is a reference to many tables in a single postgres db. There will be more tables in the future.
Here is the query inspection:
{
“request”: {
“url”: “api/ds/query?ds_type=grafana-postgresql-datasource&requestId=Q122”,
“method”: “POST”,
“data”: {
“queries”: [
{
“refId”: “B”,
“datasource”: {
“type”: “grafana-postgresql-datasource”,
“uid”: “cduv6rds4gnpcb”
},
“rawSql”: “SELECT\n t.time AS time,\n AVG(t.value) OVER (ORDER BY t.time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "Value",\n ‘‘cm_adractcnt_1d’,‘cm_adrbal1in100kcnt_1d’’ AS "Metric"\nFROM\n ‘cm_adractcnt_1d’,‘cm_adrbal1in100kcnt_1d’ AS t\nWHERE\n $__timeFilter(t.time);\n”,
“format”: “table”,
“datasourceId”: 7,
“intervalMs”: 86400000,
“maxDataPoints”: 892
}
],
“from”: “1567166689348”,
“to”: “1725019489348”
},
“hideFromInspector”: false
},
“response”: {
“results”: {
“B”: {
“error”: “db query error: pq: syntax error at or near "‘,’"”,
“errorSource”: “”,
“status”: 500,
“frames”: [
{
“schema”: {
“refId”: “B”,
“meta”: {
“typeVersion”: [
0,
0
],
“executedQueryString”: “SELECT\n t.time AS time,\n AVG(t.value) OVER (ORDER BY t.time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "Value",\n ‘‘cm_adractcnt_1d’,‘cm_adrbal1in100kcnt_1d’’ AS "Metric"\nFROM\n ‘cm_adractcnt_1d’,‘cm_adrbal1in100kcnt_1d’ AS t\nWHERE\n t.time BETWEEN ‘2019-08-30T12:04:49.348Z’ AND ‘2024-08-30T12:04:49.348Z’;\n”
},
“fields”:
},
“data”: {
“values”:
}
}
],
“refId”: “B”
}
}
}
}
We can see it’s saying syntax error but the error only occurs after enabling multi value select in the grafana variable settings.
If I disable multi value select the same query works for a single value.
Not sure what the solution is or perhaps postgres can’t do work like this with variables? I would find that surprising, but not sure.
so grafana will not magically fix your pg query with the wrong use of multi select variables. see the as metric and the FROM section in the generated query
It’s a limitation of sql query?
please answer this question
also why did you opt to have a separate table for each metric
it is solvable but we wont just post a solution you can copy pasta. we are going to do guide you
no, its your incorrect use of multi select variable and a bug in the query you wrote
Yes, there will be more tables in the future.
Is there a sql query that would work? Thanks.
there is but you first need to understand the buggy query you wrote
why did you opt for separate tables for each metric
Each metric is it’s own table because I thought having multiple tables would scale better.
Each metric/table contains ~14 years of multiple values per day. I think putting all that in a single table would break.
For other reasons outside of grafana it’s more manageable.
Thank you. I understand, I started looking into unions.
Also looked at partitions and few other things for scale.
Bottom line is the tables need to viewed as one for it to work. I’ll work on it over the next few days.
Appreciate your input, I’ll post back when I get it working.
use the power of system tables to create that view dont carve it by hand
