[[software:home_automation]]
Trace: • home_automation

Home automation

Node-RED:

MQTT:

Zigbee vs Z-wave vs WiFi:

WiFi vs Zigbee channels

Domoticz:

Zigbee

Hubs

Проект SprutHub

Homebridge emulates the iOS HomeKit API to expose e.g. Zigbee devices to HomeKit.

Xiaomi Mijia gateway – $26.33 (€23.30)

Xiaomi Aqara Hub – $37.44 (€32.60)

:YES:

  • Integrates with Apple HomeKit

Coordinator sticks:

CC2531 USB sniffer – $8.25 (€7.25) Zigbee V2 (СС2530 + CH340G) Zigbee V3 (CC2538 + CC2592) CC2538+CC2592 Zigbee module – $9 (€8.25) CC2538 Dongle, CC2538SF53, USB – $33.99 (€31.38) ZigBee Dongle CC2538 – $39.60 (€33.83) CC2538+CC2592 zigbeebox CC2538+CC2592 development board – $39.99 (€36.92) CC2538+CC2592 Egony mod with USB-UART adapter RF-Star RF-BM-2652P2 V4 Stick by Egony Ebyte E72-2G4M20S1E CC2652P V4 Stick by Egony RF-Star RF-BM-MG24B2i V6 Stick by Egony CC2652RB Stick by Slaesh – $26.52 CC2652P / V4LAN Gateway CC2652R Stick – €25.30 + €13.49 ZigStar LAN Gateway (Zigbee LAN Coordinator based on CC2652P) – €30 (only shield) / €42 (shield + case) CC2652P / Ebyte E72-2G4M20S1E Stick by Egony

:YES:

:NO:

Advantages / disadvantages of CC2531-based solutions:

  • Requires flashing of custom firmware on the stick (extra cables and debugger)
  • Requires running MQTT broker which is an extra component (however could be used by other IoT devices)

J-Link to CC2538 connection scheme

Installation:

  • Use firmware Z-Stack_3.0.x.
  • Run 
    # apt-get install nodejs npm
$ mkdir zigbee2mqtt
$ git clone https://github.com/Koenkk/zigbee2mqtt.git zigbee2mqtt/
$ cd zigbee2mqtt/
$ npm install
# cat <<'EOF' > /etc/systemd/system/zigbee2mqtt.service
[Unit]
Description=zigbee2mqtt
After=network.target

[Service]
User=homeassistant
UMask=0027
ExecStart=/usr/bin/npm start
WorkingDirectory=/var/lib/homeassistant/zigbee2mqtt
# Don't log output to syslog:
StandardOutput=null
# Below will restart the service StartLimitBurst times. For that set StartLimitInterval=RestartSec*StartLimitBurst+small_delta
Restart=always
RestartSec=30
StartLimitInterval=320
StartLimitBurst=10

[Install]
WantedBy=multi-user.target
EOF
# systemctl daemon-reload
# systemctl enable zigbee2mqtt

Other:

Flashing via UART:

file.php_id_15379_.jpg

Similar projects are:

Conbee – €39.95

  • The onboard placed radio module deRFmega256-23M12 contains an 8-bit AVR microcontroller with an integrated low-power 2.4 GHz transceiver for Zigbee / IEEE 802.15.4 applications.

:YES:

:NO:

  • From here: One needs to have deConz running which is a full blown GUI application. There is a beta version out there which can be run headless but you still need the X.org libs though.
  • not opensource

ZiGate – €49

:YES:

:NO:

Philips Hue Bridge 2.0 – €49.95

:YES:

Samsung SmartThings Hub – €95.43

:YES:

Zipabox – €184.95 480cba03e26439b7caa3639a354ac915.jpg

  • ARM9 208MHz CPU, 64MB RAM, 128MB Flash, Linux 2.6
  • Z-Wave, Zigbee (ext module), ONVIF / IPcam, SMS (ext. module)

Homey – €299

:YES:

Devices

Bilisty Zigbee smart devices Single, double, momentary and 3-way switch for Shelly dimmer with and without neutral wire SONOFF ZBMINI Zigbee 3.0 – €8.58 ($9.90)

Switches

Схема подключения

Livolo switches:

:WARN: Zigbee switches cannot work in 2-way mode with non-Zigbee (RF or WiFi) switches, see 10. Can I synchronize a 2 ways Zigbee switch and a none Zigbee 2 ways switch?. Это положение не подтверждается: функциональность Livolo 2-way никак не связана со способами удалённого управления: Zigbee 1-gang 2-way выключатель VL-C701SZ работает отлично в связке с обычным 1-gang 2-way выключатель VL-C701S.

:WARN: Zigbee version works on 26 channel and with specific manufacture-locked extended pan ID, see Switch 1 or 2 Gang by Livolo.

:WARN: Livolo switches without group wire do not act as Zigbee routers (amperage is not enough to support that feature).

:WARN: After power is back (after power shut down or changing the lamp/load), the coordinator should be in “permit join” mode because Livolo devices re-announce themselves (and get a new short network address). Otherwise they don't return back to network.

:WARN: Livolo devices are flooding the network with requests (see this comment) which probably only Livolo switches may answer.

:INFO: RF switches use 433.92MHz frequency which allows it to be controlled using Arduino, see Controlling a Livolo RF Light Switch Using a Raspberry Pi 3 B in Python and ontrol Livolo switches / Livolo switch library. There is now way to update/get the current status, so communication is one-way.

:INFO: Еще раз про Livolo + схема + ремонт + допилинг – решение проблемы, когда выключатель реагирует на касания переключением светодиода, но реле не щёлкает

:INFO: Home Assistant configuration to fix Zigbee2mqtt issue with Livolo switch

Price comparison for 1 gang 2 way switch:

Site Product Price
Livolo EU – Zigbee Schakelaars VL-C701SZ €44.95
Livolo NL VL-C701SZ + VL-C7-C1-12 €49.95
Alibaba VL-C701SZ-11 $35.97 (3-9 pcs)
Aliexpress C701Z-C1EUZ-11 €28.23 ($30.89)

ESP32 based:

DIY:

Power sockets

FAQ

Control devices (switches) and bulb status update

3rd party switches can usually be added, like the ones made by Lutron, Busch Jaeger or Dresden Elektronik, but they are not exposed to apps as such, and hence can’t be configured by apps. They usually only communicate on Zigbee level (the wireless protocol the Hue lights use) and transmit their commands directly to the bulbs instead of using the gateway.

Motion sensor: IKEA Trådfri or others?

The IKEA motion sensor talks to the bulb, Home Assistant can’t receive any signals from it directly. This means that you can’t create automations in HA based on the motion sensor alone. What you CAN do is make automations based on status of the bulb so that when the sensor triggers the bulb the bulb will in turn trigger something in HA. The efficiency of this is dependent on how fast HA gets the status of the bulb, so your mileage will vary, but depending on what kind of automation you’re looking for it could be worth a shot.

From zigbee2mqtt issue#247 and zigbee2mqtt issue#102:

You could control your Ikea bulb (or outlet) either with zigbee2mqtt only or with the remote only. Now you can use both in the same time, that's already a very good news.

Solution is to enable reporting in configuration:
advanced:
  report: true

Other issue reports:

What does interviewCompleted: false means?

The entry like below in data/database.db 
{"id":4,"ieeeAddr":"0xccccccfffed5138a","nwkAddr":38336,"epList":[],"endpoints":{},"interviewCompleted":false,"meta":{}}

means that there was a problem when pairing with the device. To cure that do the steps from issue #2148:

  • systemctl stop zigbee2mqtt.service
  • Edit data/database.db and set interviewCompleted: true and meta: {"configured": 0}
  • :OPT: Wake up the control device by clicking buttons on it
  • systemctl start zigbee2mqtt.service

“offline” problem on Zigbee map

CC2530+CC2591 router is “offline”

Sniffing Zigbee traffic

  • apt-get install wireshark-gtk wireshark && ln -s /usr/bin/wireshark-gtk /usr/local/bin/wireshark (fix for wireshark command via alternatives)
  • # apt-get install libstdc++-dev libusb-1.0-0-dev
# wget -nv -O - https://github.com/homewsn/whsniff/archive/v1.3.tar.gz | tar -xz
# cd whsniff-1.3
# make all install
  • :OPT: Assign a special group for CC2531 stick device so that it can be read by whsniff: 
    # cat <<'EOF' > /etc/udev/rules.d/95-CC2531.rules
ACTION=="add", SUBSYSTEMS=="usb", ATTR{idVendor}=="0451", ATTR{idProduct}=="16ae", MODE="660", GROUP="wireshark"
EOF
# udevadm control --reload

    :OPT: Otherwise it should be run by root: 

    # cat <<EOF > /etc/sudoers.d/whsniff
$USER  ALL = NOPASSWD: /usr/local/bin/whsniff *
EOF
  • :OPT: Grant access to USB stick to some user 
    usermod -a -G wireshark $USER

    :OPT: or open a new root session with 

    su - wireshark
  • Run 
    whsniff -c 11 | wireshark -k -i -

    where 11 is obligatory parameter that specifies ZigBee channel ID

More info:

Device tracking options

ZLL and Zigbee compliance

From Zigbee Light Link (ZLL) vs Zigbee 3.0:

Chipsets can be the same but the issue with ZLL is that it can still be different from one manufacturer to another, there is no definite standard. Zigbee HA1.2 is better and Zigbee 3.0 will be best and fully compliant across all Zigbee protocols.

In 2017 IKEA released firmwares which makes their devices fully ZLL compliant, so that they started to work with e.g. Philips Hue.

Home Assistant

Examples

Energy

Custom cards

External sensors and controllers

  • OpenMQTTGateway – bridge from 433/315 mHz, IR, Bluetooth, GSM to MQTT.
  • SmartIR – a custom Home Assistant component for controlling AC units, TV sets and fans via IR and RF controllers.
  • Broadlink – supports WiFi + IR

Extras

Managed installation 

# apt-get install python3-pip python3-setuptools
# mkdir -m 770 /var/lib/homeassistant
# useradd -d /var/lib/homeassistant -G dialout -r homeassistant
# chown homeassistant.homeassistant /var/lib/homeassistant
# su - homeassistant
$ pip3 install homeassistant --prefix=~/.homeassistant/deps
$ ln -s .homeassistant/deps/bin bin
$ ln -s .homeassistant/deps/lib/python3.7/site-packages packages
# cat <<'EOF' > /etc/systemd/system/homeassistant.service
[Unit]
Description=Home Assistant
After=network-online.target

[Service]
Type=simple
User=homeassistant
UMask=0027
Environment=PYTHONPATH=/var/lib/homeassistant/packages
Environment=TMP=/var/tmp
# [optional] scan network to populate ARP table:
#ExecStartPre=/usr/bin/nmap --open -p 23,80 -oG - 10.0.1.0/24
ExecStart=/var/lib/homeassistant/bin/hass
# Don't log output to syslog:
StandardOutput=null
# Enable if Home Assistant crashes:
# Below will restart the service StartLimitBurst times. For that set StartLimitInterval=RestartSec*StartLimitBurst+small_delta
#Restart=on-failure
#RestartSec=20
#StartLimitInterval=80
#StartLimitBurst=3

[Install]
WantedBy=multi-user.target
EOF
# systemctl daemon-reload
# systemctl enable homeassistant

Didn't work

Docker installation

  • Create/copy configuration to /etc/homeassistant. 
    # apt-get install -y docker-ce docker-ce-cli docker-compose-plugin containerd.io
# cat <<'EOF' > /var/lib/homeassistant/docker-compose.yaml
version: "3.6" # minimum version level that supports "tmpfs" volume type
services:
  homeassistant:
    container_name: homeassistant
    image: ghcr.io/home-assistant/home-assistant:2023.4.0
    restart: unless-stopped
    privileged: true
    volumes:
      - /etc/homeassistant:/config
      - /etc/localtime:/etc/localtime:ro
      - /run/mysqld:/run/mysqld:ro
    network_mode: host

  frigate:
    container_name: frigate
    image: blakeblackshear/frigate:0.12.0-beta2
    restart: unless-stopped
    privileged: true # this may not be necessary for all setups
    # check here for more information about how to calculate this value: https://docs.frigate.video/frigate/installation#calculating-required-shm-size
    shm_size: "64mb"
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /etc/frigate:/config
      - /home/frigate:/media/frigate
      - type: tmpfs # Optional: 1GB of memory, reduces SSD/SD Card wear
        target: /tmp/cache
        tmpfs:
          size: 1000000000
    network_mode: host
EOF
# cat <<'EOF' > /etc/systemd/system/homeassistant.service
[Unit]
Description=Home Assistant
After=network-online.target mosquitto.service mariadb.service

[Service]
Type=simple
# Discover the network to learn hostnames of various devices:
ExecStartPre=/usr/bin/nmap --open -p 23,80 -oG - 10.0.1.0/24
# Builds, (re)creates, starts, and attaches to containers. If the process encounters an error, the exit code for this command is 1. If the process is interrupted using SIGINT (ctrl + C) or SIGTERM, the containers are stopped, and the exit code is 0.
ExecStart=docker compose -f /var/lib/homeassistant/docker-compose.yaml up
TimeoutStopSec=30

[Install]
WantedBy=multi-user.target
  • :OPT: For hardware acceleration on Intel chipsets: 
    # apt-get install i965-va-driver

    If the following error is displayed: 

    # ffmpeg -hwaccel vaapi -i test.mp4 -c:v libx264 output.mp4
...
[AVHWDeviceContext @ 0x559f7e1aabc0] libva: /usr/lib/x86_64-linux-gnu/dri/iHD_drv_video.so init failed
Stream mapping:
  Stream #0:0 -> #0:0 (h264 (native) -> h264 (libx264))
  Stream #0:1 -> #0:1 (aac (native) -> aac (native))
...

    then check that vainfo (should be installed via apt-get install vainfo) outputs profiles using i965_drv_video.so driver: 

    # vainfo
libva info: VA-API version 1.10.0
libva info: Trying to open /usr/lib/x86_64-linux-gnu/dri/iHD_drv_video.so
libva info: Found init function __vaDriverInit_1_10
libva error: /usr/lib/x86_64-linux-gnu/dri/iHD_drv_video.so init failed
libva info: va_openDriver() returns 1
libva info: Trying to open /usr/lib/x86_64-linux-gnu/dri/i965_drv_video.so
libva info: Found init function __vaDriverInit_1_8
libva info: va_openDriver() returns 0
vainfo: VA-API version: 1.10 (libva 2.10.0)
vainfo: Driver version: Intel i965 driver for Intel(R) Haswell Desktop - 2.4.1
vainfo: Supported profile and entrypoints
      VAProfileH264ConstrainedBaseline: VAEntrypointVLD
      VAProfileH264ConstrainedBaseline: VAEntrypointEncSlice
      VAProfileH264Main               : VAEntrypointVLD
      VAProfileH264Main               : VAEntrypointEncSlice
      VAProfileH264High               : VAEntrypointVLD
      VAProfileH264High               : VAEntrypointEncSlice
      VAProfileH264MultiviewHigh      : VAEntrypointVLD
      VAProfileH264MultiviewHigh      : VAEntrypointEncSlice
...

    and then force ffmpeg to use that driver: 

    LIBVA_DRIVER_NAME=i965 ffmpeg -hwaccel vaapi -i test.mp4 -c:v libx264 output.mp4

    If OK, then add 

    ffmpeg:
  hwaccel_args: preset-vaapi

    to /etc/frigate/config.yml.
    More info:

Additionally install intel-gpu-tools and monitor the load on GPU: 

# apt-get install intel-gpu-tools; intel_gpu_top

intel-gpu-top: Intel Haswell (Gen7) @ /dev/dri/card0 -  238/ 238 MHz;   0% RC6;  0.36/ 8.82 W;       14 irqs/s

      IMC reads:     1219 MiB/s
     IMC writes:      345 MiB/s

         ENGINES     BUSY                                                                                                   MI_SEMA MI_WAIT
       Render/3D    1.50% |                                                                                               |      0%      0%
         Blitter    0.00% |                                                                                               |      0%      0%
           Video    2.49% |██▍                                                                                            |      0%      0%
    VideoEnhance    0.00% |                                                                                               |      0%      0%

See also:

FAQ

Error executing query: (MySQLdb.OperationalError) (1271, “Illegal mix of collations for operation 'UNION'”)

Check for tables that have collation utf8mb4_general_ci and change the collation for all of them one by one to utf8mb4_unicode_ci:
[homeassistant_db]> SELECT TABLE_NAME, table_collation FROM information_schema.tables WHERE table_schema = 'hass_db' AND table_collation = 'utf8mb4_general_ci';
+-----------------------+--------------------+
| TABLE_NAME            | table_collation    |
+-----------------------+--------------------+
| recorder_runs         | utf8mb4_general_ci |
| schema_changes        | utf8mb4_general_ci |
| statistics            | utf8mb4_general_ci |
| statistics_runs       | utf8mb4_general_ci |
| statistics_short_term | utf8mb4_general_ci |
+-----------------------+--------------------+
 
[homeassistant_db]> ALTER TABLE recorder_runs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, LOCK=exclusive;
...

The solution is provided in issue 73235 (see also issues 92852, issue 93410).

Visualization

  • Install packages: 
    # cat <<EOF > /etc/apt/sources.list.d/grafana.list
deb https://packages.grafana.com/oss/deb stable main
EOF
# wget -nv -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
2019-08-10 12:36:19 URL:https://packages.grafana.com/gpg.key [1694/1694] -> "-" [1]
OK
# apt-get update
# apt-get install grafana influxdb influxdb-client mosquitto
  • Setup Grafana:
    1. # chgrp grafana -R /etc/grafana
# chmod o-rwx -R /etc/grafana
# patch -p0 <<'EOF'
--- /etc/grafana/grafana.ini.orig    2020-02-11 22:13:49.346687600 +0100
+++ /etc/grafana/grafana.ini    2020-02-06 19:05:54.087086389 +0100
@@ -33,6 +33,7 @@

 # The ip address to bind to, empty will bind to all interfaces
 ;http_addr =
+http_addr = 127.0.0.1

 # The http port  to use
 ;http_port = 3000
@@ -48,6 +49,7 @@
 # If you use reverse proxy and sub path specify full url (with sub path)
 # See https://grafana.com/docs/grafana/latest/installation/behind_proxy/
 ;root_url = http://localhost:3000
+root_url = https://www.host.com/grafana/

 # Serve Grafana from subpath specified in `root_url` setting. By default it is set to `false` for compatibility reasons.
 ;serve_from_sub_path = false
@@ -172,12 +174,15 @@

 # set to true if you host Grafana behind HTTPS. default is false.
 ;cookie_secure = false
+cookie_secure = true

 # set cookie SameSite attribute. defaults to `lax`. can be set to "lax", "strict" and "none"
 ;cookie_samesite = lax

 # set to true if you want to allow browsers to render Grafana in a <frame>, <iframe>, <embed> or <object>. default is false.
+# See also https://grafana.com/docs/grafana/latest/reference/share_panel/
 ;allow_embedding = false
+allow_embedding = true

 # Set to true if you want to enable http strict transport security (HSTS) response header.
 # This is only sent when HTTPS is enabled in this configuration.
@@ -222,6 +227,7 @@
 [users]
 # disable user signup / registration
 ;allow_sign_up = true
+allow_sign_up = false

 # Allow non admin users to create organizations
 ;allow_org_create = true
@@ -231,6 +237,7 @@

 # Default role new users will be automatically assigned (if disabled above is set to true)
 ;auto_assign_org_role = Viewer
+auto_assign_org_role = Editor

 # Background text for the user field on the login page
 ;login_hint = email or username
@@ -389,8 +396,10 @@
 [auth.proxy]
 # Defaults to false, but set to true to enable this feature
 ;enabled = false
+enabled = true
 # HTTP Header name that will contain the username or email
 ;header_name = X-WEBAUTH-USER
+header_name = X-WEBAUTH-USER
 # HTTP Header property, defaults to `username` but can also be `email`
 ;header_property = username
 # Set to `true` to enable auto sign up of users who do not exist in Grafana DB. Defaults to `true`.
@@ -405,9 +414,12 @@
 # Limit where auth proxy requests come from by configuring a list of IP addresses.
 # This can be used to prevent users spoofing the X-WEBAUTH-USER header.
 ;whitelist = 192.168.1.1, 192.168.2.1
+whitelist = 127.0.0.1
 # Optionally define more headers to sync other user attributes
 # Example `headers = Name:X-WEBAUTH-NAME Email:X-WEBAUTH-EMAIL Groups:X-WEBAUTH-GROUPS`
 ;headers = Email:X-User-Email, Name:X-User-Name
+# Check out docs for more details on the below setting
+enable_login_token = false

 #################################### Basic Auth ##########################
 [auth.basic]
EOF
    2. :OPT: Setup datasources 
      # cat <<EOF > /etc/grafana/provisioning/datasources/mysql.yaml
apiVersion: 1

deleteDatasources:
  - name: MySQL

datasources:
  - name: MySQL
    type: mysql
    editable: false
    url: 10.0.0.2:3306
    database: source_db
    user: grafana
    secureJsonData:
      password: abc123
    3. :OPT: Install necessary plugins: 
      # grafana-cli plugins list-remote
# grafana-cli plugins install pr0ps-trackmap-panel
# grafana-cli --pluginUrl https://github.com/alexandrainst/alexandra-trackmap-panel/archive/refs/heads/master.zip plugins install alexandra-trackmap-panel
  • Setup Apache: 
    # cat <<'EOF' > /etc/apache2/conf-available/grafana.proxy.conf
<IfModule mod_proxy.c>

<Location "/grafana">
    # Grafana requires a slash at the end of the path, hence implementing proxying using mod_rewrite:
    RewriteEngine on
    # In this context the path is actually /var/www/grafana, hence don't start regexp with ^
    RewriteRule /grafana$        /grafana/ [R,L]
    RewriteRule /grafana/(.*)$    http://localhost:3000/$1 [P,L]

    SSLRequireSSL
    AuthType Basic
    AuthName "Grafana"

    Require valid-user

    # Prevent the client from setting these headers:
    RequestHeader unset Authorization
    RequestHeader unset X-WEBAUTH-USER
    RequestHeader set X-WEBAUTH-USER %{REMOTE_USER}s
</Location>
<Location "/grafana/api/live/ws">
    ProxyPass        ws://localhost:3000/api/live/ws
    ProxyPassReverse    ws://localhost:3000/api/live/ws

    SSLRequireSSL

    Require all granted
</Location>

</IfModule>
EOF
# a2enconf grafana.proxy
# systemctl restart apache2.service
  • Setup Mosquitto MQTT server1) (see also Authentication and authorization in InfluxDB): 
    # mosquitto_passwd -c /etc/mosquitto/passwd <user>
Password: ****
Reenter password: ****
# cd /etc/mosquitto
/etc/mosquitto# chgrp mosquitto -R .
/etc/mosquitto# chmod o-rwx -R .
  • :OPT: Setup InfluxDB server:
    1. # chgrp influxdb -R /etc/influxdb
# chmod o-rwx -R /etc/influxdb
# influx
> CREATE USER admin WITH PASSWORD 'secret1' WITH ALL PRIVILEGES
> CREATE DATABASE hass_db
> USE hass_db
> CREATE USER hass WITH PASSWORD 'secret2'
> GRANT ALL ON hass_db TO hass
> exit
# patch -p0 <<'EOF'
--- /etc/influxdb/influxdb.conf.orig    2018-10-19 06:47:59.000000000 +0200
+++ /etc/influxdb/influxdb.conf 2020-01-27 12:01:56.766787568 +0100
@@ -223,7 +223,7 @@
   # bind-address = ":8086"

   # Determines whether user authentication is enabled over HTTP/HTTPS.
-  # auth-enabled = false
+  auth-enabled = true

   # The default realm sent back when issuing a basic auth challenge.
   # realm = "InfluxDB"
EOF
# systemctl restart influxd
    2. Setup Home Assistant to dump the entity states also to InfluxDB:

      configuration.yaml

      influxdb:
  database: hass_db
  username: hass
  password: !secret hass_password
  default_measurement: state
  component_config_glob:
    sensor.*humidity:
      override_measurement: humidity
    sensor.*temperature:
      override_measurement: temperature
  • :OPT: Setup MySQL backend support to replace event state recorder
    1. Run 
      # apt-get install python3-mysqldb
# mysql -uroot -p <<'EOF'
CREATE DATABASE hass_db;
GRANT SELECT ON hass_db.* TO 'hass'@'localhost' IDENTIFIED BY 'zzz';
GRANT SELECT ON hass_db.* TO 'grafana'@'localhost' IDENTIFIED BY 'xxx';
EOF
    2. Change homeassistant.service to read: 
      [Unit]
After=network-online.target mariadb.service
    3. Change configuration.yaml to read: 
      recorder:
  db_url: mysql://hass:zzz@localhost/hass_db?unix_socket=/run/mysqld/mysqld.sock&charset=utf8

How to implement datetime filters and grouping for SQL source?

:OPT: First option would be to use the standard function $__timeGroup(dateColumn, '5m'): 
...
WHERE $__timeFilter(time_created)
GROUP BY $__timeGroup(time_created, $__interval)
ORDER BY $__timeGroup(time_created, $__interval) ASC

:OPT: Second option would be to introduce a special variable, which will provide more fine-grained control but will require the user to select manually the appropriate scaling where necessary:

  • Create $interval template variable as in this post: 
    SELECT 'minute' AS __text, '%Y%m%d%H%i' AS __value
UNION
SELECT 'hour' AS __text, '%Y%m%d%H' AS __value
UNION
SELECT 'day' AS __text, '%Y%m%d' AS __value
UNION
SELECT 'month' AS __text, '%Y%m' AS __value
  • Use the following SQL query template (:INFO: last_updated is indexed): 
    SELECT
  MAX(unix_timestamp((last_updated))) AS TIME,
  MAX(volume) AS VALUE,
  'Volume' AS metrics
FROM states
WHERE $__timeFilter(last_updated)
AND ...
GROUP BY date_format(last_updated, '$interval')
ORDER BY date_format(last_updated, '$interval') ASC

See also: Visualize Your Data With Grafana, Percona Live 2017 (Daniel Lee)

How do add series to second Y axis?

You can switch any series to the second y axis by clicking on the colored line to left of the series name in the legend below the graph. Alternately, use the “Display Styles” → “Series Specific overrides” to define an alias or regex + “Y-axis: 2” to move metrics to the right Axis.

How to increase label font size on OSM maps?

1) Should be setup in a specific order, see InfluxDB can create the first user only when auth is turned off
software/home_automation.txt · Last modified: 2019/11/05 17:50 by dmitry

"Девица не хочет лезть в Окно" – device not compatible with Windows.

7-zip barcode browser cpp dictionary dlna dns eclipse encoding event gps greasemonkey hibernate hudson image ind iso jboss jersey jetty kaspersky kerberos ldap machine_learning mail maven mortgage ntlm ocr outdoor poodle postfix property rest ripping security skiing spellcheck spnego spring squirrelmail ssh ssl svn taxes utf video vnc wifi xml
 
 
Recent changes RSS feed Driven by DokuWiki