I’m really glad I found this thread. Thanks for taking the time to figure that out!
Additionally, CASE and value comparisons seem to work pretty well. No need for additional templates, which wasn’t really suiting my needs. You could always do another query for the direct value should you need it.
queries:
- name: garage_tilt_sensor_x_axis
query: "SELECT CASE WHEN value < 1000 then 'closed' WHEN value > 1000 then 'open' END result FROM attributes where ieee = 'xxxxxxxx' and cluster = 64514 and attrid = 18 "
column: result
you’ll see your sensor state comes back as the value of ‘result’.
Do you know how to read attribute which is not in zigbee.db but has value when I look with ‘Manage Clusters’?
I have Schwaiger power socket which measures delivered energy but this attribute is not in zigbee.bd.
How can I read this value from device cluster in template?
With ‘Manage Clusters’ is possible read all clusters and attributes.
We just need some sort of query, like platform: sql, to read directly from zigbee devices.
Maybe there is already such way, but I can’t find it.
hello friend, you did a good job, thank you
do you know how to get WYZE LOCK status, like door is opened, closed, locked manually and unlocked manually?
thank you
@akhlghatyan Sorry, I don’t have this device so I can’t say for sure. I would recommend first checking in the device states under Developer Tools->States. Most things are tracked there and can be accessed with automation.
If you can’t find it there and it is a Zigbee device, you can check under Clusters under the zigbee integration… you’ll need to hunt for it.
Congig->integrations->zigbee->devices->(device you want to check)->Manage Clusters
You select Clusters then attribute to see what is in it.
I’m using Moes TRVs. However ZHA doesn’t support battery percentage.
In clusters I found that there’s a battery low warning (binary).
Now I want to make a binary sensor with this data.
Did anyone figure out how to do this?
This solution to read data from the smartthings sensors has been working great for over a year, but I just noticed the other day it stopped working. I’m thinking most likely it stopped after recently updating homeassistant.
Has anyone else seen this happen? It is still reading a value from the zigbee.db file but it never updates based on the orientation of the device.
I had been fighting with this issue after a recent update as well. Turns out ZHA updated their schema and the attributes table is currently called attributes_cache_v6.
So the query from above is now…
SELECT CASE WHEN value < 1000 then 'closed' WHEN value > 1000 then 'open' END result
FROM attributes_cached_v6
WHERE ieee = 'xxxxxxxx' and cluster = 64514 and attrid = 18
It looks like ZHA may be continuing with versioning their table names moving forward as well. I think I saw a reference to attributes_cached_v7 somewhere. So this issue may pop up again.
Unfortunately that didn’t work for me. It doesn’t produce any data. The values show up as “unknown”. When I go back to “attributes” it populates the values but I think they’re stale values.
Dang. I was hoping that would work for you. I’d also see about trying attributes_cache_v5. Depending on what version you’re on, ZHA may not be using v6 yet.
This solution worked GREAT and let me move my garage door (my last remaining sensor) off of SmartThings last year. Props to everyone above who came up with this workaround. But last weekend I came across a blog post (Using a SmartThings sensor in "garage door" mode with Home Assistant - David Boike's Blog) where they listen for a zha_event and triggered an automation if it came from the designated sensor and had values within their range. My implementation from that isn’t 100% identical as I deviated to match the initial SmartThings code for a sensor in garage door mode, but it works extremely well so far. If the v5 table doesn’t work and you’re open to an alternate way for doing this, it might be worth a look.
Thanks for the possiblities opened up here now I can check for some possible Zigbee command losses which are only reflected in not updated thermostat cluster attributes and do trigger automations based on that.
now I can check for some possible Zigbee command losses which are only reflected in not updated thermostat cluster attributes and do trigger automations based on that.