PostgreSQL WHERE Clause Variable

Grafana PostgreSQL
, ,
1

Hello everyone, I believe my query should be reasonably straight forward so i’ve skipped out a couple questions from them template to cut to what matters. I’m still quite new here and if theres a similar discussion that i could not find on this then i’d greatly appreciate a redirection.

  • What are you trying to achieve?
    Using Grafana’s variables to visualise multiple time-series graph on the same panel.

  • How are you trying to achieve it?
    In the database are two tables: grpc and nodes. I have connected the name of the nodes to the data to achieve the following table via the below SQL

image
image1369×996 77.5 KB

There are currently two (satellite) nodes: “NS” and “AL”.

  • What happened?
    Grafana merges the two graphs into one, as it does not separate on the node_name. See below screenshot.
    image
    image1332×998 81 KB

The two nodes’ data can be viewed separately with multiple queries with a where clause on the node_id. However, as i want to eventually be able to use variables this is not scalable. Also, even if i did leave it “merged” in one query, the title of the legends and the same - pop_ping_latency meaning you cannot hold ctrl+click and hide them as you normally would.

  • What did you expect to happen?
    Something like this (please note that the data itself is different, but the way that it presents under different colours and the legend having the name of the nodes is what’s desired):
    image
    image2294×595 47.4 KB

One solution would be to have each property of the nodes in separate tables and add the template variable into the SELECT part of the clause instead of the WHERE. For example, SELECT datetime, $nodes FROM latency, however for this specific database that is not possible. All the data i need is in front of me in Grafana on that table in the first screen shot. Is there no way to have multiple time series graphs on the same panel if the variable its selecting by is in the WHERE part of the SQL query? Thank you!

2

image

try this query instead

select mesaure_time as time, 
node_name as metric, 
pop_ping_latency_ms as value
3

Thank you for your response yosiasz, however i do not believe that this solves my problem of separating one column into mutliple series based on node_id. Here is what happens when i use your query:

image
image1247×853 62.4 KB

1 Like
4

I don’t know if this will shed some more light on how to solve the issue however i also tried this prompt to no avail, it simply merges them like my initial query:

image
image1225×1119 80.8 KB

SELECT
grpc.measurement_time,
nodes.node_name,
grpc.pop_ping_latency_ms,
CASE
WHEN grpc.node_id = 1 THEN ‘Node 1’
WHEN grpc.node_id = 2 THEN ‘Node 2’
ELSE ‘Other’
END as node_identity
FROM
grpc
INNER JOIN
nodes ON grpc.node_id = nodes.id
WHERE
grpc.node_id = 1 OR grpc.node_id = 2

5

what version of grafana are you on?

SELECT
grpc.measurement_time as time,
nodes.node_name as metric,
grpc.pop_ping_latency_ms as value

how about this?

6

Why you use “Table” as format and not “Time series”?
Or am I on the wrong path?

Jo

1 Like