Grafana and MySQL Automation Annotations

Third party integrations
1

Hi,
This is my first post here, thanks a lot to the community and devs for this great, fun, flexible platform.

I would like to share my use of prometheus and grafana to monitor home assistant, this helped me a lot to troubleshoot and ensure my automation rules are working properly and find what rule was triggered in correlation with entities state changes.
I wanted to see what rule caused a state to change without checking the logbook or notify on every rule.
I used mysql data source to query hass mysql db to get the automation rules’ last changed states.

Screenshot from 2017-09-08 11-15-19
Screenshot from 2017-09-08 11-15-19717Ă—598 31.9 KB

This the the result when annotations are turned on.

automations_annotations
automations_annotations1920Ă—985 74.3 KB

4 Likes
2

Hi, is it possible you give an example of the query you used? I’m new to this and that would give me something to start with!

3

If someone wants to cut and paste

SELECT
    UNIX_TIMESTAMP(CONVERT_TZ(last_updated, '+00:00', @@session.time_zone)) as time_sec, 
    entity_id as title 
FROM states 
WHERE entity_id like 'automation%' 
AND (UNIX_TIMESTAMP(last_updated) > UNIX_TIMESTAMP(last_changed))
ORDER BY last_updated DESC 
LIMIT 100;
4

This started generating an error on more recent Grafana versions.
entity_id alias should be now “text” instead of “title”

SELECT      
	UNIX_TIMESTAMP(CONVERT_TZ(last_updated, '+00:00', @@session.time_zone)) as time_sec,         
	entity_id as text
FROM states  
WHERE entity_id like 'automation%'  
AND (UNIX_TIMESTAMP(last_updated) > UNIX_TIMESTAMP(last_changed)) 
ORDER BY last_updated DESC  
LIMIT 100;
5

Not to high jack this thread but if someone knows how to do annotations with data from influxdb please start another thread with how!!

1 Like