Hello!
I’ve got a door lock connected with Mqtt. It sends Actions (Door was opened by 01-code, Door was opened by 02-fingerprint).
I’d like to make Lovelace card which shows the history of actions with timestamp for last 24 hours.
How I can do it? Please direct me with some examples.
SQLite query returns the History records in format I need.
This output is larger than 255 chars and SQL integration won’t help me - sensor state is limited by 255.
I’d like to show this output in my HA dashboard.
How I can do this? Custom lovelace card?
select group_concat(door,char(10)) from (SELECT distinct strftime('%d.%m %H:%M',created, 'localtime') || ' ' || state as door FROM states WHERE entity_id = 'sensor.mydoor' and state != '' and state != 'unknown' order by state_id desc limit 20);
14.03 20:39 lock_opened_outside
14.03 20:39 finger_open_user_id3
14.03 20:39 finger_not_match
14.03 19:07 locked
14.03 19:03 finger_open_user_id5
14.03 19:03 finger_not_match
14.03 17:07 finger_open_user_id3
14.03 16:18 finger_open_user_id0
14.03 14:57 lock_opened_outside
14.03 14:57 finger_open_user_id5
14.03 14:57 finger_not_match
14.03 12:31 locked
14.03 12:30 lock_opened_outside
14.03 10:40 locked
14.03 10:39 lock_opened_outside
14.03 10:15 locked
14.03 10:08 ring_bell
14.03 08:33 finger_open_user_id0
14.03 08:22 lock_opened_outside
14.03 08:03 locked
14.03 07:00 locked
14.03 06:54 finger_open_admin_id1
14.03 06:54 finger_not_match
It seems I did what I needed
in configuration.yaml:
var:
door_stats:
friendly_name: "Door Open Statistics"
icon: mdi:door
query: "select group_concat(door,char(10)) as door_log from (SELECT distinct strftime('%d.%m %H:%M',created, 'localtime') || ' ' || state as door FROM states WHERE entity_id = 'sensor.0x00158d0002728a26_action' and state != '' and state != 'unknown' and state != 'locked' order by state_id desc limit 7);"
column: door_log
in automation.yaml:
- alias: Light Up When Door Open
trigger:
platform: state
entity_id: sensor.0x00158d0002728a26_action
condition:
condition: template
value_template: "{{ '_open' in trigger.to_state.state }}"
action:
- service: switch.turn_on
entity_id: switch.0x00158d00012414a2_switch
- service: var.update
data:
entity_id: var.door_stats
In ui-lovelace.yaml:
- type: markdown
title: Door Open Stats
content: >
{% for l in states('var.door_stats').splitlines() %}
{%- set entry = l.split() -%}
{% set who = entry[2].replace("finger_open_admin_id1","Serge (Admin)").replace("finger_open_user_id5","Nastya") %}
- {{ entry[0] }} {{ entry[1] }}: {{ who }}
{%- endfor -%}
As result I have simple card with History of opening the door:
![image](//community-assets.home-assistant.io/original/3X/1/d/1dc788ee07b973a0be98b7ad65bc20658635e763.png)
But I’m absolutely sure that there is some more lovely way to do the same. Please advice ![:slight_smile: :slight_smile:](https://community.home-assistant.io/images/emoji/twitter/slight_smile.png?v=9)
3 Likes