Graph panel for gokart telemetry / global parameter / vertical marker

Grafana Time Series Panel
1

Hello,

Please excuse my newbie questions… I’m quite new to Grafana.

I’m currently using it with PostgreSQL + TimescaleDB as datasource to plot data from an electric gokart with custom Arduino Mega / XBee Pro telemetry (and a Go script to fill database).

Database is composed of 3 tables (see SQL create statements)

CREATE TABLE syst_evt (
  time TIMESTAMPTZ NOT NULL,
  id INTEGER NULL,
  millis INTEGER NULL,
  msg VARCHAR
);

CREATE TABLE line_evt (
  time TIMESTAMPTZ NOT NULL,
  id INTEGER NULL,
  millis INTEGER NULL,
  line SMALLINT NULL
);

CREATE TABLE data_evt (
  time TIMESTAMPTZ NOT NULL,
  id INTEGER NULL,
  millis INTEGER NULL,
  
  rpm DOUBLE PRECISION NULL,
  speed DOUBLE PRECISION NULL,
  torque DOUBLE PRECISION NULL,

  vbat DOUBLE PRECISION NULL,
  ibat DOUBLE PRECISION NULL,

  tvar DOUBLE PRECISION NULL,
  tmot DOUBLE PRECISION NULL,

  ax DOUBLE PRECISION NULL,
  ay DOUBLE PRECISION NULL,
  az DOUBLE PRECISION NULL
);

The first table sys_evt contains system events (when telemetry is switched on for example)

The second table line_evt contains line events ie when gokart is crossing lap line (we measure this using a reed switch and a magnetic band embedded in the track)

The third table data_evt contains data event (essentially data from speed variator : motor speed, temperature of motor, of variator, voltage, current…)

id is an identifier for each gokart (currently we only have one gokart in this system but it could evolve)

On a dashboard I have been able to simply add a graph panel to draw rpm as a function of time.

SQL query looks like:

SELECT
  "time" AS "time",
  rpm
FROM data_evt
WHERE
  $__timeFilter("time")
ORDER BY 1

That was quite easy but I would like now to parametrize this graph by id (ie I want to be able to first choose a given id and then graph should only plots rpm for gokart of this given id.

I can manually change query to

SELECT
  "time" AS "time",
  rpm
FROM data_evt
WHERE
  $__timeFilter("time") AND
  id = 12345
ORDER BY 1

But if I plot several graphs (rpm, ubat, …) I won’t change id on several graph… I’m looking for a way to do it more “globally”.

Do you have any tips to achieve this?

An other point
I want to add on this graph, markers (vertical lines) when gokart is crossing line but I don’t know how to add such markers on graph. Any idea?

Kind regard

2

I’ve found that the first part of my question can be answered using variables http://docs.grafana.org/reference/templating/
thanks for this great doc…

unfortunately I’m can find a solution on the last part of my question (vertical markers on graph)

3

you could try adding an annotation as markers, and possibly alerts to get the vertical lines.

4

Thanks @bkgann I will have a deeper look at http://docs.grafana.org/reference/annotations/
and try to see how to create them automatically using a SQL query from line_evt table (using only time column).

Unfortunately, I haven’t understand what alerts are… to my understanding alerts are a method to trigger action when a value is below (or over) a given value so I see alerts more as an horizontal lines in a timeseries plot.

5

Regarding parameterizing the gokart id, you can use template variables.

Do get it working with a simple test, create a new variable like this:

image
image1059×645 28.1 KB

on your dashboard you will see a selector:
image

then in your query, change the id to use the variable $GOKART

SELECT
  "time" AS "time",
  rpm
FROM data_evt
WHERE
  $__timeFilter("time") AND
  id = $GOKART
ORDER BY 1

That should let you switch between different id’s. The next step would be to edit the template variable so it queries for the id numbers vs a static list, then it would be dynamic as you add more metrics.

1 Like
6

It works fine. I also created a view from line_evt to create annotations. Thank you @bkgann for your help.