Help in setting up grafana

Configuration
1

Hi All,

i setup Grafana on my RPI3 and a MariaDB 10 database on my synology Nas.
Now it is time to set up Grafana but i am not able to extract data from the DB on Synology: i can access it and connect to Grafana but i have problem creating the right query to retrieve the data.

I am trying to use this

SELECT UNIX_TIMESTAMP(created) as time_sec, 
'yr_temperature' as metric, 
CAST(JSON_EXTRACT(JSON_EXTRACT(event_data, '$.new_state'), '$.state') AS DECIMAL(4,2)) as value  
FROM `events` 
WHERE event_type = 'state_changed'
and JSON_EXTRACT(event_data, '$.entity_id') = 'sensor.yr_temperature'
and $__timeFilter(created)
ORDER BY created ASC

But as far as i understood JSON_EXTRACT is not available in MariaDB 10 (only since 10.2.1) and in fact it does not work.

Now what should be the equivalent of JSON_EXTRACT for MariaDB or any syntax which will correctly extract data?

Thanks in advance to everyone who will help

1 Like
2

no one? :frowning:

3

Hello. Did you succeeded meanwhile?

4

Why are you using JSON_EXTRACT?

I’m using Grafana along with Maria DB 10 and it works ok for me, here’s an example query that shows a temperature state:

SELECT
$__timeGroupAlias(created,$__interval),
entity_id AS metric,
sum(state) AS "state"
FROM states
WHERE
$__timeFilter(created) AND
entity_id = 'sensor.l_temp'
GROUP BY 1,2
ORDER BY $__timeGroup(created,$__interval)