Plot multiply series with dynamic request

I’m using Grafana v10.4.0 on Debian 12
I have mysql database with 2 tables:
1st table contain timestamps
2nd table contains fields: ‘prefix’, ‘path’, and foreign key timestamp that points to timestamp table.


id    | timestamp          | path       | prefix             |
+-----+--------------------+------------+--------------------+
|   1 |                  1 |          1 |                603 |
|   2 |                  1 |          2 |                 80 |
|   3 |                  1 |          5 |                  1 |
|   4 |                  1 |          9 |                  1 |
|   5 |                  1 |         10 |                  1 |
|   6 |                  1 |         14 |                  1 |
|   7 |                  2 |          1 |                  1 |
|   8 |                  2 |          4 |                  1 |
|   9 |                  2 |          7 |                  1 |
|  10 |                  2 |          9 |                  1 |
|  11 |                  3 |          1 |                  1 |
|  12 |                  3 |          2 |                  1 |

It means that each timestamp can correspond to multiple measurements:

To get values for the graph I use query:
SELECT ep.prefix, pt.timestamp
FROM tb_prefix ep
INNER JOIN processing_time pt ON ep.processing_time_id = pt.id
WHERE ep.path = 1
ORDER BY pt.timestamp DESC
LIMIT 50;

This gives me 1 graph all timestamps and prefix for path = 1
Is there a way to draw graphs for all path values in the table?
Let’s say path can be in range from 1 to 20, in every timestamp not always all values from 1 to 20 will be present

Thank you

where is your value field in your query?

use this but you are missing a number field.

SELECT ep.prefix as metric, --for labels
       pt.timestamp as time , --for x axis
       numeric_field as value --for y axis
FROM tb_prefix ep
INNER JOIN processing_time pt 
  ON ep.processing_time_id = pt.id
WHERE ep.path = 1
ORDER BY pt.timestamp DESC
LIMIT 50;

so ep.prefix is the value that should be plotted.
and is used to show x axis

If I add 3rd value (for example ep.path)

SELECT ep.path as metric, --for labels
       pt.timestamp as time , --for x axis
       ep.prefix as value --for y axis
FROM tb_prefix ep
INNER JOIN processing_time pt 
  ON ep.processing_time_id = pt.id
WHERE ep.path = 1
ORDER BY pt.timestamp DESC
LIMIT 50;


I will get 2nd graph plotted that is always 0
You can see it in the bottom of the graph.

Now to plot several graphs I use several requests:

SELECT ep.prefix, pt.timestamp
FROM tb_prefix ep
INNER JOIN processing_time pt ON ep.processing_time_id = pt.id
WHERE ep.path = 1 --<<<<<<<<<<<<<< first graph
ORDER BY pt.timestamp DESC
LIMIT 50;

SELECT ep.prefix, pt.timestamp
FROM tb_prefix ep
INNER JOIN processing_time pt ON ep.processing_time_id = pt.id
WHERE ep.path = 2 --<<<<<<<<<<<<<< second graph
ORDER BY pt.timestamp DESC
LIMIT 50;

again you need to specify proper column names for time series to work

SELECT ep.prefix as value, pt.timestamp as time
--you need a metric string value, like name or something like that
FROM tb_prefix ep
INNER JOIN processing_time pt 
ON ep.processing_time_id = pt.id
--no need for where clause. if you had 1500 items, you wont have 1500 queries with where filter
ORDER BY pt.timestamp DESC
LIMIT 50;

can you post the second table data?

The other table (processing_time) looks as following:

id    | timestamp             | seconds      | nanoseconds
1       2024-05-03 22:17:35     1714774655      23168872
2       2024-05-03 22:18:32     1714774712      319246531
3       2024-05-03 22:19:04     1714774744      236276975
4       2024-05-03 23:37:54     1714779474      112480871
5       2024-05-03 23:39:21     1714779561      315402325
6       2024-05-03 23:39:35     1714779575      586298221
7       2024-05-03 23:40:48     1714779648      737917497
8       2024-05-03 23:42:16     1714779736      325795238
9       2024-05-03 23:42:35     1714779755      704314594
10      2024-05-03 23:43:44     1714779824      557428212
11      2024-05-03 23:45:12     1714779912      424215704
12      2024-05-03 23:45:35     1714779935      846853776

I agree that WHERE clause has to be removed, in fact ep.path has about 1K variations of
but if I put ep.path as metric - it is plotted as 0 value line
what metric should I use?

what is your metric column?

I use “prefix” column from the tb_prefix table as metric.

but here you said that prefix is the value? confused

sorry, I’m not very familiar with the terms.
Since this is my first complex graph.

Logic is following:
Every few seconds script retrieves data from the device and installs into two tables:

processing_time:

id    | timestamp             | seconds      | nanoseconds
1       2024-05-03 22:17:35     1714774655      23168872
2       2024-05-03 22:18:32     1714774712      319246531
3       2024-05-03 22:19:04     1714774744      236276975
4       2024-05-03 23:37:54     1714779474      112480871

tb_prefix

id    | timestamp          | path       | prefix             |
+-----+--------------------+------------+--------------------+
|   1 |                  1 |          1 |                603 |
|   2 |                  1 |          2 |                 80 |
|   3 |                  1 |          5 |                  1 |
|   4 |                  1 |          9 |                  1 |
|   5 |                  1 |         10 |                  1 |
|   6 |                  1 |         14 |                  1 |
|   7 |                  2 |          1 |                  1 |
|   8 |                  2 |          4 |                  1 |
|   9 |                  2 |          7 |                  1 |
|  10 |                  2 |          9 |                  1 |
|  11 |                  3 |          1 |                 50 |
|  12 |                  3 |          2 |                  1 |

timestamp field in tb_prefix refers to processing_time.id field

I want to plot a graph that will show value of prefix field through the time for each path value.
For example, graph 1:

timestamp     prefix
    1                 603
    2                   1
    3                  50

graph 2:

timestamp     prefix
    1                 80
--<< on timestamp 2 we do not have records
    3                  1

so I assume value is “path”, but don’;t know what should be used as metric

metric is the string name of thing you want to visualize

value goes on the y axis
time goes on the x axis

Ok, then if I understand correct -
Prefix is value
Timestamp is a time
path will be the metric
I need for every value of the metric new graph
But when I add metric to the sql request it’s plotted as a straight line with value 0

well some metric will plot as 0 because you might not have any values for that metric at certain times. the wider your time series the more none 0 data you will see

if you want a separate panel then look into the Repeat feature of grafana. search this forum for it. plenty of examples

Thank you. I think this is the closest solution to what I was searching for.
I didn’t know about this feature.
The best would be to have these graphs on one plot.
But SQL returns an error in that case.

we are unable to see the errors remotely :wink: