How to make Markdown Card with Log History of Sensor's actions

Configuration Frontend
1

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.

2

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
3

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

But I’m absolutely sure that there is some more lovely way to do the same. Please advice :slight_smile:

3 Likes