Plot more than one series on the same time series using Grafana and MySQL-CodePudding

I have multiple servers reporting a value(Temperature or reliability score). I want to plot them on the same time series. value over time for each metric.

the table looks like this

Total Reliability
Time                | value | metric
2021-12-07 08:24:20 | 0.994 | 2787
2021-12-07 08:25:18 | 0.996 | 3129
2021-12-07 08:25:34 | 0.994 | 2787
2021-12-07 08:26:31 | 0.996 | 3129
2021-12-07 08:26:48 | 0.994 | 2787
2021-12-07 08:27:44 | 0.996 | 3129

I read a few similar questions but their solution does not work for some reason

SELECT
time AS “time”,
reliability as value,
machine_id as metric
FROM machine
WHERE
$__unixEpochFilter(time)
GROUP BY machine_id,time
ORDER BY time asc

if I remove the grouping to plot the two columns

using Grafana v8.2.5 (b57a137acd) and MySQL Server 8.0.27

CodePudding user response：

Metric should be string. It looks like a integer in your case, so hack machine_id as metric:

CONCAT(machine_id , '') AS metric

Use Grafana MySQL macros for time agreggation. Then final Grafana SQL query with 5m avg aggregation should be:

SELECT
  $__timeGroup(time,'5m'),
  AVG(reliability) AS value,
  CONCAT(machine_id, '') AS metric
FROM machine
WHERE $__unixEpochFilter(time)
GROUP BY machine_id, time
ORDER BY time ASC

It still may need some small adjustements.

CodePudding user response：

I use this.

SELECT
  time AS "time",
  reliability AS value,
  CONCAT(machine_id, '') AS metric
FROM machine
WHERE
  $__unixEpochFilter(time)
ORDER BY time