BTW my concern was with detecting issues with sensors. I had a script relying on last updated date. in my case it’s only for temperature. if no change with one of the sensor within 12 hours, it means it’s failing (temperature has to change for sure over 12 hours) so I get a notification with the list of failed devices.
Because ‘last updated date’ is wrong when you restart HA, I built another script, where I retrieve the temperature 12 hours ago and compare with current, and send a notification with the list when temperature is the same.
I use a SQL sensor (based on the SQL integration), instead or the history integration as I don’t want to create one history sensor per device!!
sharing with you the SQL, Just replace the 3 devices I kept to illustrate the script, with your IDs.:
SELECT
GROUP_CONCAT(meta.entity_id, ', ') AS state
FROM
states_meta AS meta
LEFT JOIN states AS curr ON curr.metadata_id = meta.metadata_id
AND curr.last_updated_ts = (
SELECT MAX(last_updated_ts)
FROM states
WHERE metadata_id = meta.metadata_id
)
LEFT JOIN states AS past ON past.metadata_id = meta.metadata_id
AND past.last_updated_ts = (
SELECT MAX(last_updated_ts)
FROM states
WHERE metadata_id = meta.metadata_id
AND last_updated_ts <= strftime('%s', 'now', '-12 hours')
)
WHERE
meta.entity_id IN (
'sensor.garage_temperature', //replace with yours
'sensor.jardin_temperature_temperature', //replace with yours
'sensor.salon_temperature_temperature' //replace with yours
)
AND curr.state == past.state;
and in the config set ‘state’ for column name, do not change the other fields (well you can but it’s optional).
The “SQLite Web” integration is nice to build and test queries!!
I named my SQL entity “list_of_dead_devices” so in your automation you can use sensor.list_of_dead_devices
if empty, no dead devices, otherwise it’s a list of failed devices, comma separated that you can simply add to your notification.
that’ my automation code( replace the name of the SQL entity by your name or just use mine)
alias: Detect sensor failure
description: >-
Checks if temperature sensor hasn't updated in 12 hours. Add new device to
list_of_dead_devices (SQL entity)
triggers:
- hours: "8"
minutes: "00"
seconds: "00"
trigger: time_pattern
conditions:
- condition: template
value_template: |
{{ states('sensor.list_of_dead_devices') not in ['unknown', 'None', ''] }}
actions:
- action: script.simple_notification
data:
title: Detection sensor failure
message: |
The following sensors have the same temperature as 12 hours ago:
- {{ states('sensor.list_of_dead_devices').replace('sensor.', '').replace(', ', '\n- ') }}.
mode: single
that are not required atm. A tick that ignores possible wrong stuff due to reboot/ some other process eg db related?