How can I read the "Last Seen" value of Zigbee device?

Configuration
,
16

I found a solution with an SQL query, but it needs one entity for each device :

Using SQL integration in the UI:

Database URL : sqlite:////config/zigbee.db
Column : last_seen
Select Query : SELECT last_seen FROM devices_v12 where ieee = ‘xx:xx:xx:xx:xx:xx:xx:xx’

sensor entity displays : 1690215703.74021

if you add the line

Value Template : {{ value | as_datetime }}

sensor entity displays : 2023-07-24 16:47:52.080090+00:00

Using configuration.yaml :

sql:
name: “SQL sensor name”
db_url: sqlite:////config/zigbee.db
query: “SELECT last_seen FROM devices_v12 where ieee = ‘xx:xx:xx:xx:xx:xx:xx:xx’”
column: “last_seen”

2 Likes