Using mariadb as datasource

[andreasferm]

Good day!

I've have all my tracking data stored in a mariadb db. I can't seem to get any datapoints to be displayed, so i'm trying to figure out how the panel reads and uses data.

I've got a query set up that would report something like -> |longitude|latitude|unix timestamp|

| 58.394364339281324 | 15.556798987621917 | 1524717270 | | 58.39462016744349 | 15.568641038825959 | 1524720615 | | 58.39524232306565 | 15.568404374750246 | 1524722431 |

But i'm not sure if this is expected by the panel. I see a map but it's all zoomed out.

Should each column be called something special? What would the output be from a functional query from InfluxDB ?

[andreasferm]

Hmm... the issue is here i guess ->

if (data.length === 0 || data.length !== 2) {
  // No data or incorrect data, show a world map and abort
  this.leafMap.setView([0, 0], 1);
  return;
}

I'm not sure how things work with InfluxDB, but when using Myslq as a datasource (forgive me i'm relatively new to grafana) a typical query could look like this ->

SELECT UNIX_TIMESTAMP() as time_sec,

, FROM WHERE ORDER BY created ASC I'm not following where you get the time information to be honest.. I'll see if i can find a solution

pR0Ps commented 6 years ago

The panel currently expects lat and long as two separate fields, each as an array of [data, timestamp].

Ex ("datapoints" property added by Grafana):

[
  {"datapoints": [[lat1, time1], [lat2, time2], ...]},
  {"datapoints": [[lon1, time1], [lon2, time2], ...]}
]

The InfluxDB query that produces the correct data is something like:

SELECT median("latitude"), median("longitude") FROM "table" WHERE $timeFilter GROUP BY time($interval)

I'm open to ideas on better input format(s) for the panel data, I just did it like this this initially because it seemed reasonable and it worked. If there's a way that's easier for other database engines (and still works for InfluxDB) I'm not opposed to changing it.

I don't have a MySQL environment to mess around with at the moment so I can't test it, but the documentation makes it seem like it's only possible to select 1 metric per query which would be a bit limiting.

What you can try is inserting a console.log(data) statement at the start of the onDataReceived(data) and messing around with your query in Grafana until you find one that pushes all the required data to the panel (if that's possible). At that point I can help you work out some way to manipulate the data so it can be added to the coords array.

andreasferm commented 6 years ago

Thanks for the quick reply.

It seems that i cannot get an identical response for this specific datasource unfortunately (it's parsing a json string saved down from home assistant database, and i would prefer to keep it untouched)

I can however set up multiple queries, that both sends [long , time] and [lat, time] as 2 objects. Which i think could be made to work

It looks something like this ->

Where object A contains the long&tim and object B lat&time.

Now i wanted to check how this would look by logging out data with console.log(data). But i can't see anything in the grafana logs at least (set debug loglevel).

I only added the console.log part and restarted grafana. Is there anything else needed, or is this log printed somewhere else?

Sorry, i haven't done much with grafana plugins or npm... :)

andreasferm commented 6 years ago

.... and after restarting and testing my query... it seems to be working with sending two objects.... let me test around a bit...

pR0Ps commented 6 years ago

@andreasferm Let me know what you find so I can put something in the README for others

andreasferm commented 6 years ago

My apologies for the delay... i forgot.. :)

Well it seems to be working as expected. The way to get it working is basically have 2 queries (when using mysql plugin these are called A, B, C etc.. perhaps it's the same for influxdb, i don't know..

A:

SELECT

as value, UNIX_TIMESTAMP() as time_sec FROM

WHERE "some kind of criteria" ORDER BY ASC And B being identical apart from changing to the one containing the longitude. My actual query looks a bit different as i'm using data logged by a home automation system, but the above should suffice. Thanks!

• © Githubissues.
• Githubissues is a development platform for aggregating issues.

[pR0Ps]

The panel currently expects lat and long as two separate fields, each as an array of [data, timestamp].

Ex ("datapoints" property added by Grafana):

[
  {"datapoints": [[lat1, time1], [lat2, time2], ...]},
  {"datapoints": [[lon1, time1], [lon2, time2], ...]}
]

The InfluxDB query that produces the correct data is something like:

SELECT median("latitude"), median("longitude") FROM "table" WHERE $timeFilter GROUP BY time($interval)

I'm open to ideas on better input format(s) for the panel data, I just did it like this this initially because it seemed reasonable and it worked. If there's a way that's easier for other database engines (and still works for InfluxDB) I'm not opposed to changing it.

I don't have a MySQL environment to mess around with at the moment so I can't test it, but the documentation makes it seem like it's only possible to select 1 metric per query which would be a bit limiting.

What you can try is inserting a console.log(data) statement at the start of the onDataReceived(data) and messing around with your query in Grafana until you find one that pushes all the required data to the panel (if that's possible). At that point I can help you work out some way to manipulate the data so it can be added to the coords array.

[andreasferm]

Thanks for the quick reply.

It seems that i cannot get an identical response for this specific datasource unfortunately (it's parsing a json string saved down from home assistant database, and i would prefer to keep it untouched)

I can however set up multiple queries, that both sends [long , time] and [lat, time] as 2 objects. Which i think could be made to work

It looks something like this ->

Where object A contains the long&tim and object B lat&time.

Now i wanted to check how this would look by logging out data with console.log(data). But i can't see anything in the grafana logs at least (set debug loglevel).

I only added the console.log part and restarted grafana. Is there anything else needed, or is this log printed somewhere else?

Sorry, i haven't done much with grafana plugins or npm... :)

[andreasferm]

.... and after restarting and testing my query... it seems to be working with sending two objects.... let me test around a bit...

[pR0Ps]

@andreasferm Let me know what you find so I can put something in the README for others

[andreasferm]

My apologies for the delay... i forgot.. :)

Well it seems to be working as expected. The way to get it working is basically have 2 queries (when using mysql plugin these are called A, B, C etc.. perhaps it's the same for influxdb, i don't know..

A:

SELECT

as value, UNIX_TIMESTAMP() as time_sec FROM