Pulling out data from mysql server in Grafana

Does anyone have some example syntax of how to pull out time series from the mySQL db to show it in Grafana?

Looking to get out time series from device speed etc.
I am totally at scratch here, but i got the things talking to each other, just need the data.

You can look at database.selectPositions query in the configuration file.

Ok thanks, i get the speed out now. I guess will find out how to sort out which device to look at because this string showing all devices at the same time:

SELECT
  UNIX_TIMESTAMP(devicetime) as time_sec,
  (speed) * 1.852 as value,
  (deviceId) as metric
FROM positions
WHERE $__timeFilter(devicetime)
ORDER BY deviceId ASC

In attributes table the values are a bit more tricky to get out, because all attributes is stored as a long string. Any tip would be helpful here too.

You can use function from here:

https://stackoverflow.com/a/31127857/2548565

Thanks for the tip Anton

The query to extract "io254" from the Attributes table will be:

SELECT
 UNIX_TIMESTAMP(devicetime) as time_sec,
 json_extract (attributes, '$.io254' ) as value,
 deviceId as metric
FROM positions
WHERE $__timeFilter(devicetime)
ORDER BY devicetime ASC

Still i get all devices displaying up in the graph, how to avoid that, anyone?

Well, you need to filter it by device id. The example from config has it.

Ok, In general that works(below), but the device ID seems to be the number-order from when they entered database. It will be difficult when you get more devices. It guess it must be somehow linked to device identifier number (the imei) or nickname of device.

SELECT
  UNIX_TIMESTAMP(devicetime) as time_sec,
  json_extract (attributes, '$.io254' ) as value,
  deviceId as metric
FROM positions
WHERE deviceId = 1

ORDER BY (devicetime) ASC

You don't need to guess anything. You can find id in the devices table.

Ok, i see it. Then just need to write a query that takes device name from devices table to the correct ID over in the positions table, within the first query.

Got it now. The query will look something like this:

SELECT
  UNIX_TIMESTAMP(devicetime) as time_sec,
   (speed) * 1.852 as value,
  deviceId as metric
FROM positions
WHERE deviceId = (SELECT id from devices where name= 'SBH Iphone')

ORDER BY (devicetime) ASC

Hello Anton
Can you please tell me if and where IP address for every position update is stored?

It's stored in attributes, but only if you enable it.

positions -> attributes right ? nothing there for now.

How to enable/disable?

Please read documentation:

https://www.traccar.org/configuration-file/

Its this one:

<entry key='processing.remoteAddress.enable'>true</entry>

but which table does the data go to?