cristian.livadaru.net Open in urlscan Pro
2a05:d014:275:cb01::c8  Public Scan

Form analysis
0 forms found in the DOM

Text Content

Toggle navigation Cristian Livadaru's Blog
 * All Posts
 * cooking
 * kids
 * life
 * rant
 * security
 * tech
 * travel
 * ARCHIVE
 * ABOUT

home assistant smarthome grafana


HOME ASSISTANT WITH TIMESCALEDB AND GRAFANA




Posted by Cristian Livadaru on Saturday, July 30, 2022


NICE GRAPHS FOR ALL SENSORS

TL;DR, skip the whole bla bla rant and jump directly to the TimescaleDB part

I wanted to have some nice graphs for Home Assistant like I already did once
where I got the Temperature values via SDR from a few sensors broadcasting the
temperature via radio which was then pushed via MQTT to a NodeRed running on a
Pi. What I did not want to do however is to create something in NodeRed for
every sensor I have in the house. I just want it to work for all existing and
future sensors. I stumbled over Oliver Hilsky’s Blog Post showing exactly what I
wanted to accomplish, you install InfluxDB, add a few of lines of YAML to Home
Assistant and you’re done.


ME AND INFLUXDB DO NOT GET ALONG

My only issue was that I’m not a fan of InfluxDB, not that something is wrong
with it or it doesn’t have a use case, but for the things I need I always had
issues with not wanting to learn how to query data or think about retentions and
things like that because they are not relevant for me in this case, and this
image from https://howfuckedismydatabase.com/ says it all.

Nosql database - source: https://howfuckedismydatabase.com/

But, I wanted to give it a shot and just wanted to get it running.


INFLUXDB ON HOME ASSISTANT NOT WORKING

The InfluxDB addon on Home Assistant seems to not be working, it installs but
does not start up, in addition to that it shows that it’s not been maintained
anymore since 2021. It’s much simpler to just update an external docker
container than having to mess around with a Home Assistant add-on. So I just
went with the external installation. This is easy to accomplish with
docker-compose but I faced the issue that when creating a new bucket I was
placed exactly in that position of choosing a retention period. I don’t know, I
don’t care, I just want graphs, and let me deal with some cleanup later. At this
point, I decided to just abandon the idea of InfluDB.


TIMESCALEDB TO THE RESCUE

I like PostgreSQL, I know SQL, so why not just use TimescaleDB for this task? As
it seems I’m not the only one with this idea, there is an addon for Home
Assistant to install TimescaleDB but I decided to go with an external
installation of TimescaleDB instead and create a docker-compose containing both
TimescaleDB and Grafana.


THE DOCKER-COMPOSE FILE

version: "3.4"
services:
  grafana:
    image: grafana/grafana
    container_name: grafana
    restart: always
    ports:
      - 13000:3000
    networks:
      - monitoring
    volumes:
      - /opt/docker/home-assistant/grafana:/var/lib/grafana
  timescaledb:
    image: timescale/timescaledb-ha:pg14-latest
    restart: always
    ports:
      - 15432:5432
    environment:
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - /opt/docker/home-assistant/timescaledb:/home/postgres/pgdata
    networks:
      - monitoring

networks:
  monitoring:


Some important things about the compose file.

 * Update the ports to match your needs, here grafana is reachable via 13000 and
   TimescaleDB via 15432
 * For some reason TimescaleDB, or at least timescaledb-ha, places the database
   files under /home/postgres/pgdata/data instead of the usual Postgresql
   /var/lib/postgresql/data, this is new to me, I tried Timescale earlier and
   this was not the case. It did cause me to use my first minutes of data due to
   this.

Start up everything and then it’s time to create the database.


CREATE THE DATABASE

Execute a shell in the container and start psql

docker-compose run timescaledb /bin/bash
$ psql


postgres=# CREATE database homeassistant;
CREATE DATABASE
postgres=# \c homeassistant
You are now connected to database "homeassistant" as user "postgres".
homeassistant=# CREATE EXTENSION IF NOT EXISTS timescaledb;

NOTICE:  extension "timescaledb" already exists, skipping
CREATE EXTENSION
homeassistant=#


This was the database creation and the timescaledb extension.


SEND DATA TO TIMESCALE WITH LTSS

To get all the data into TimescaleDB you need to install LTSS There is a more
detailed instruction in the forum post by freol, I will just add the bare
minimum to get it running.


INSTALL LTSS

I am assuming here that you already have HACS installed, if not, you need that
first. This is outside the scope of this post.

Go to HACS Integrations

HACS Integrations



Click on Explore & Download repositories

Download repository



Search and add the ltss repository

Search and add ltss repository



Once it’s installed, restart your Home Assistant.


CONFIGURE CONNECTION TO TIMESCALEDB

Whip up your favorite editor (which of course must be vi) and edit
config/configuration.yaml adding these lines of YAML and fixing the password,
DBSERVER, and port to your environments.

ltss:
  db_url: postgresql://postgres:****@DBSERVER:port/homeassistant
  include:
    domains:
    - sensor


It’s very important to use a privileged user (like postgres) for the first start
since ltss needs to install extensions, after the initial startup you can change
to a non-privileged user. Validate your configuration and restart Home Assistant
once more. While it’s starting up you can watch the database being populated.

homeassistant=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | geography_columns | view     | postgres
 public | geometry_columns  | view     | postgres
 public | ltss              | table    | postgres
 public | ltss_id_seq       | sequence | postgres
 public | spatial_ref_sys   | table    | postgres
(5 rows)


That’s it! Your Home Assistant is now sending all sensor data to the
TimescaleDB. Time to get started with Grafana.


GRAFANA


CONFIGURE GRAFANA

The installation was already done via docker-compose, visit the Grafana
installation with your browser of choice and start adding a new data source.

Select PostgreSQL as the source for Grafana and configure it like this:

Grafana connection to Timescale DB

Please note that the host is timescaledb:5432, since it’s running on the same
network in docker, it can reach it with the hostname timescaledb and on the port
5432 and not the exposed 15432 port. I also disabled TLS in this case.

Next, scroll down and make sure to select the correct version and enable the
TimescaleDB setting.

Enable TimescaleDB in Grafana

That’s it from the Grafana connection part, now let’s build some dashboards.


SETUP DASHBOARD

To get an idea, this is my first dashboard.

Grafana Home Assistant Dashboard



And if you are asking yourself what “shithole” is supposed to be, that’s the
junk room full of crap I forgot about, yes I even have a motion sensor there,
stop judging me! And yes, my kids have been watching too much Encanto, hence the
smart-home had to be named “La Casita”, I was not allowed to call it “E-Corp”.

I will let you build your own dashboard, just want to give a few tips along the
way.

LTSS STATE IS A STRING, BUT GRAFANA WANTS NUMBERS

The first issue I encountered was that grafana is expecting numbers but ltss
saves the state as a string. This makes sense if you look at the data where you
have addresses of Geolocations as state or the SSID your phone was connected to
and all other kinds of non-numerical values that Home Assistant uses. But all
you have to do is let TimescaleDB typecast to decimal with

state::DECIMAL AS "value"


But you might come across a situation where you have a value of “None” as a
battery state in the database. This is a problem as the SQL statement will give
you an error. The simplest way for me was to just select the entries that have
numbers in the state using regex.

WHERE state ~ '[0-9]{1,3}(?:.?[0-9]{0,3})'


This selects Integers or Decimal numbers with up to three digits after the
comma.

Another issue I had was the long names like sensor.sensor_bedroom_battery when
all I wanted was to just have bedroom as the metric name in Grafana. Again you
can use PostgreSQL string replacements, I went with regex again.

SELECT
  REGEXP_REPLACE(REGEXP_REPLACE(entity_id, 'sensor.','','g'),'_battery','') AS "metric",


This replaces sensor. where . matches in regex also the _ which removes both
instances of sensor.sensor_

Now here’s the full SQL to select all Battery data from Grafana

SELECT
  $__timeGroupAlias("time",$__interval),
  REGEXP_REPLACE(REGEXP_REPLACE(entity_id, 'sensor.','','g'),'_battery','') AS "metric",
  state::DECIMAL AS "value"
FROM ltss
WHERE
  $__timeFilter(time) AND
  entity_id like '%battery' AND
  state ~ '[0-9]{1,3}(?:.?[0-9]{0,3})'
GROUP BY "time",2,3
ORDER BY 1,2


In my select statments, I used WHERE entity_id like '%battery' to get only
battery data and to get temperature data I used entity_id like
'sensor.%temperature', you get the idea.


CREATING ALERTS

One more before I let you go. Having all this data in Grafana now could be used
to set up some alerts. I will just show the basics since I have used the new
Grafana alerts for the first time in this project. Before starting with the
alerts, create a new folder in grafana as this will be a required selection in
the alerting system without an option to create one during the alert creation.
You also need to create a Time Series panel, the bar gauge, or any other type of
panels won’t have the Alert tab in Grafana.

Clik on the alert tab.

Grafana alerts



Set your condition, I used a value < 30 to trigger the alert.

Grafana alert condition



Define labels if you want, here I use the label channel with the value telegram
which allows me to send this alert via telegram.

Grafana alert labels



Now go to Alerting -> contact points and add a new Telegram (or whatever you
want) as a contact point. If using Telegram you need BOT API Token and the chat
ID, how to get those are not the scope of this already way too long post.

Grafana Telegram

Now Notification Policies in Grafana and create a new policy to use this contact
point in case the channel label is equal to telegram.

Grafana notification policy

One more thing that was not covered here is how to configure Grafana’s URL. All
alerts will have localhost in the URL for silencing for example. But this should
be just an ENV var in the compose file. For me, this is already enough to know
which battery needs changing.


WARP UP

And that’s a warp! I hope I didn’t forget anything, tried to take notes while
setting everything up. A huge thank you goes to freol for creating the ltss
integration! You can find freol on github

--------------------------------------------------------------------------------

 * ← Previous Post
 * Next Post →

--------------------------------------------------------------------------------

CATALOG

 * Nice graphs for all sensors
 * Me and InfluxDB do not get along
 * InfluxDB on Home Assistant not working
 * TimescaleDB to the rescue
 * The docker-compose file
 * Create the database
 * Send data to timescale with ltss
 * Install ltss
 * Configure connection to TimescaleDB
 * Grafana
 * Configure Grafana
 * Setup Dashboard
 * ltss state is a string, but grafana wants numbers
 * Creating alerts
 * Warp Up

--------------------------------------------------------------------------------

FEATURED TAGS

children-quotes docker fusionpbx kids life linux phishing raid rant raspberry
security sysadmin voip webdev xen
 * 
 * 
 * 
 * 

Copyright © Cristian Livadaru's Blog 2023
CleanWhite Hugo Theme by Huabing |