I recently changed my Home Assistant database from sqlite to mySQL and researched Grafana a bit and notice it supports mySQL as a data source.
Do I need to setup an InfluxDB in my use case and connect it to Home Assistant?
At the moment I have done the setup part, but noticed that when trying to use the import script for InfluxDB, the process on the Raspberry Pi 3 was killed after a while, so I wonder if I really need both databases if I only want to start to see what Grafana can bring to the table.
There’s no need for InfluxDB. You can use MySQL. This is a query i use:
SELECT UNIX_TIMESTAMP(created) as time_sec,
'Temperatuur' 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.sensebender_micro_1_1'
and $__timeFilter(created)
ORDER BY created ASC
InfluxDB will probably faster but at least it works.
I installed Hass.io on DSM Synology using the new package.
I’am also using MySQL/MariaDB and want to extract data with Grafana.
First, thanks for this query but was not working (probably due to latest upgrade). You can uses this one, more optimized :
SELECT UNIX_TIMESTAMP(E.created) as time_sec,
'Temp' as metric,
CAST(JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(E.event_data, '$.new_state'), '$.state')) AS DECIMAL(4,2)) as value
FROM `events` as E
INNER JOIN `states` as S
ON E.event_id = S.event_id
WHERE E.event_type = 'state_changed'
#and JSON_EXTRACT(E.event_data, '$.entity_id') = 'sensor.sensebender_micro_1_1'
and S.entity_id = 'sensor.sensebender_micro_1_1'
and $__timeFilter(time_fired)
ORDER BY E.created ASC
So everything went good for me and seems to be fast.
The above queries didn’t work for me but @dav27 gave the right pointer.
Just adding a couple samples here that took me sometime to figure out but worked fine in my case (being new to Grafana, and having almost zero knowledge of SQL).
This works really fast - with mysql running in my asus router. It’s amazing how powerful grafana is. I still remember the days of MRTG
Converting a Temperature sensor reading
SELECT UNIX_TIMESTAMP(E.created) as time_sec,
'Temp' as metric,
CAST(state AS DECIMAL(4,2)) as value
FROM `events` as E
INNER JOIN `states` as S
ON E.event_id = S.event_id
WHERE E.event_type = 'state_changed'
#and JSON_EXTRACT(E.event_data, '$.entity_id') = 'sensor.sensebender_micro_1_1'
and S.entity_id = 'sensor.office_temperature'
and $__timeFilter(time_fired)
ORDER BY E.created ASC
Converting “Switch State” (on/off) to 1/0 for Grafana
SELECT UNIX_TIMESTAMP(E.created) as time_sec,
'Server State' as metric,
CAST( (CASE WHEN state = 'on' THEN 1 ELSE 0 END) AS DECIMAL(4,2)) as value
FROM `events` as E
INNER JOIN `states` as S
ON E.event_id = S.event_id
WHERE E.event_type = 'state_changed'
and S.entity_id = 'switch.htpc_server'
and $__timeFilter(time_fired)
ORDER BY E.created ASC
Wondering if anyone succeeded in creating a “moving average” series from HA sensor data in Grafana, with a mysql source.
I tried things like the following without any luck (my SQL skills are almost zero)
SELECT UNIX_TIMESTAMP(E.created) as time_sec,
'moving average' as metric,
AVG( CAST(state as DECIMAL(4,2)) ) as value
FROM `events` as E
INNER JOIN `states` as S
ON E.event_id = S.event_id
WHERE E.event_type = 'state_changed'
and S.entity_id = 'sensor.rpi_load_1m_rounded'
BETWEEN (E.created - INTERVAL 15 MINUTE)
AND E.created
GROUP BY E.created
ORDER BY E.created ASC
any pointer or reference on this? I found a grafana plugin that appears to fit this need (MetaQueries) but it seems it doesn’t work yet with Grafana 7.x (current)
