smart-home:home-assistant

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
smart-home:home-assistant [2025/05/03 04:09] – created adminsmart-home:home-assistant [2025/05/03 05:04] (current) – [HA mysql] admin
Line 22: Line 22:
 10A2ABxxxxxx - without double point 10A2ABxxxxxx - without double point
 </code> </code>
 +
 +==== HA CCTV frigate / scrypted  ====
 +https://docs.scrypted.app/installation.html -- scrypted install  docker  \\
 +https://www.homeautomationguy.io/blog/running-frigate-on-proxmox -- frigate install docker on proxmox\\
 +https://coral.googlesource.com/webcoral/ - TPU accelerator coral start \\
 +
 +===== HA. configue =====
 + example http://kvazis.link/hassos \\
 +icons https://pictogrammers.com/library/mdi/ https://mdi.bessarabov.com/icon/memory\\
 +==== HA. Floor plan beauty  ====
 +https://www.youtube.com/watch?v=MCNxgb0mrSA \\
 +==== HA articles ====
 +https://sprut.ai/user/AlexAz/contents/articles \\
 +
 +===== HA mysql =====
 +++++ Get stats of mysql - where data located|
 +<code SQL>
 +-- get count of state entity and count of history data
 +SELECT 
 +    states_meta.entity_id,
 +    COUNT(*),
 +   -- states.state,
 +    from_unixtime(MIN(IF(states.last_changed_ts IS NULL,
 +        states.last_updated_ts,
 +        states.last_changed_ts))) AS first_changed_ts,
 +    from_unixtime(Max(IF(states.last_changed_ts IS NULL,
 +        states.last_updated_ts,
 +        states.last_changed_ts))) AS last_changed_ts
 +FROM
 +    states
 +        LEFT JOIN
 +    states_meta ON (states.metadata_id = states_meta.metadata_id)
 +GROUP BY states_meta.entity_id
 +ORDER BY 2 DESC
 +LIMIT 10;
 +    
 +-- size of all tables
 +SELECT table_schema as `DB`, table_name AS `Table`,TABLE_ROWS, 
 +  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` 
 +  FROM information_schema.TABLES 
 +  ORDER BY (data_length + index_length) DESC
 +  limit 10;
 +
 +-- optimize
 +OPTIMIZE TABLE homeassistant.states;
 +</code>
 +++++
 +
 +===== HA - template =====
 +<code BASH>
 +# home assistant - executing in day 24h
 +01h 24
 +10m 144
 +05m 288
 +01m 1440
 +05s 17280
 +01s 86400
 +
 +{% set result = namespace(sensors=[]) %}
 +{% set exclude = [] %}
 +{% set hours = 1 %}
 +{% for state in states.sensor | rejectattr('attributes.device_class', 'undefined') | selectattr('attributes.device_class', '==', 'timestamp') %}
 +    {{state}}
 +    {% if 'last_seen' in state.entity_id and not state.entity_id in exclude.entity_id and (states(state.entity_id) == 'unavailable' or ((as_timestamp(now()) - as_timestamp(states(state.entity_id))) > ((hours | int) * 60 * 60))) %}
 +     {% set result.sensors = result.sensors + [state.name | regex_replace(find=' last seen', replace='') ~ ' (' ~ relative_time(strptime(states(state.entity_id), '%Y-%m-%dT%H:%M:%S%z', 'unavailable')) ~ ')'] %}
 +    {% endif %}
 + {% endfor %}
 + {{ result.sensors | join(', ') }}
 +
 +
 +</code>
 +
  • smart-home/home-assistant.1746245343.txt.gz
  • Last modified: 2025/05/03 04:09
  • by admin