InfluxDB+Grafana persistence and graphing

Store your openHAB item states in an InfluxDB time-series datastore and create highly customizable diagrams with Grafana. These diagrams can in return be included in your sitemap as images.

Following this guide, you will install InfluxDB and Grafana, make openHAB store data in an InfluxDB database, make Grafana fetch data from the InfluxDB database and draw diagrams. Finally you can include these diagrams in your openHAB sitemap.

Table of Content


Starting Point

This guide is based on and was last tested with the following components:

  • openHAB 2.0
  • On the same system or on a system in the same network
    • x64 Debian/Ubuntu derivative (called monitoring-host)
    • InfluxDB (v1.3.2)
    • Grafana (v4.4.3)

Installation on a Raspberry Pi is also possible, installation instructions are given here.

Installation inside a docker image is described here.

Attention rrd4j users: A script to transfer your existing data from rrd4j to InfluxDB (by @Dangar) can be found here: https://github.com/jhron/rrd2influxdb

InfluxDB Installation and Setup

Following the installation instructions, start by adding the InfluxData repository and installing InfluxDB on your monitoring-host.

After successful installation, letā€™s connect to the InfluxDB command line interface and create users and databases for our needs. You may alternatively also use the web frontend to do so, the steps are nearly the same. We will create one database for your openHAB installation and an admin, an openhab and a grafana user. The user openhab will only receive privileges for his database, the grafana user will only be allowed to read. Be sure to store your selected passwords.

You may skip the extra security steps for Authentication and Authorization, depending on the reachability of your monitoring-host. In this case, you only need to create the database.

$ influx
Connected to http://localhost:8086 version 0.13
InfluxDB shell version: 0.13
> CREATE DATABASE openhab_db
> CREATE USER admin WITH PASSWORD 'SuperSecretPassword123+' WITH ALL PRIVILEGES
> CREATE USER openhab WITH PASSWORD 'AnotherSuperbPassword456-'
> CREATE USER grafana WITH PASSWORD 'PleaseLetMeRead789?'
> GRANT ALL ON openhab_db TO openhab
> GRANT READ ON openhab_db TO grafana
> exit

Additional CLI commands can be found in the InfluxDB documentation.

Enable authentication in the [http] section of the configuration file /etc/influxdb/influxdb.conf:

[http]  
enabled = true  
bind-address = ":8086"      # change to a specific interface if needed 
auth-enabled = true         # will enforce authentication
...

Finally, restart the InfluxDB service: sudo systemctl restart influxdb.service.

The CLI interface will now also ask for authentication:

influx -username admin -password SuperSecretPassword123+ -host localhost

If you ran into any problems, please refer to the InfluxDB documentation and use google to find more in-detail tutorials.

This is a good time to start injecting data into your InfluxDB for later testing with Grafana. Iā€™ve provided a small Python script here. If you are not familiar with Python, please have a look at a short tutorial, e.g. this one. Configure and execute the script on your openHAB host to get a sine wave data series stored in the database.

Grafana Installation and Setup

Follow the installation instructions, to install Grafana on your monitoring-host.

After successful installation you should be able to reach the Grafana dashboard at http://monitoring-host:3000 with the default login admin:admin - be sure to change the password now!

Disable user signup and enable anonymous access (for later image export) in the configuration file /etc/grafana/grafana.ini:

[users]
# disable user signup / registration
allow_sign_up = false
...
[auth.anonymous]
# enable anonymous access
enabled = true

If you ran into any problems, please refer to the Grafana documentation and use google to find more in-detail tutorials.

Connecting Grafana to InfluxDB

Logged in to the Grafana dashboard, go to ā€œData Sourcesā€ and create a new source pointing to your InfluxDB database, providing the credentials you chose earlier. Example:

Finally, create a new dashboard, create a graph panel, select the freshly created data source and build your first query, presenting the previously injected sine wave time series.

Connecting openHAB to InfluxDB

If everything is set up right and you were able to inject the sine wave into your database, itā€™s time to do the same from within openHAB. You need to first install the InfluxDB persistence service (via apt, PaperUI or addons.cfg - depending on your system and preferences).

Next, add your InfluxDB connection details to the persistence service config file /etc/openhab2/services/influxdb.cfg:

# The database URL, e.g. http://127.0.0.1:8086 or https://127.0.0.1:8084 .
# Defaults to: http://127.0.0.1:8086
url=http://192.168.0.2:8086

# The name of the database user, e.g. openhab.
# Defaults to: openhab
user=openhab

# The password of the database user.
password=AnotherSuperbPassword456-

# The name of the database, e.g. openhab.
# Defaults to: openhab
db=openhab_db

# The retention policy to be used, needs to configured in InfluxDB
# Till v0.13: 'default', since v1.0: 'autogen'
retentionPolicy=autogen

Finally add a persistence job so openHAB sends your item states to InfluxDB. Add all items or item groups you want to persist including fitting strategies to /etc/openhab2/persitence/influxdb.persist:

Strategies {
    everyMinute : "0 * * * * ?"
    everyHour   : "0 0 * * * ?"
    everyDay    : "0 0 0 * * ?"
}

Items {
    gHeatAct*, gHeatSet*, gHeatValve*   : strategy = everyChange, everyHour
    Presence_Phone                      : strategy = everyChange
}

Regarding strategies: a meaningful usage of these is dependent on the nature of your items and how often they are changed. everyChange is a good strategy with most items (like the power consumption of a washing machine), you might however additionally add a every... for items, that might not be changed for longer times (for example the preset temperature of a room). This is especially important, as Grafana will (by default) only draw graphs for times it has data for. If the preset temperature didnā€™t change for three hours, there will be no graph for the last three hours :wink:

If everything went well, you should be able, to start selecting your items for a query inside Grafana (after a few minutes to send first values).

Building openHAB Diagrams in Grafana

Grafana offers different panel types and countless displaying options. Your first reference for these should be:

That said, I want to point out a few options probably interesting for you.

Selection/Aggregation Functions

InfluxDB provides a set of functions that influence what is returned and therefore presented by Grafana. To understand these, you first need to understand the concept behind ā€œGROUP BYā€.

Simplified, this means Grafana is asking for a summarized portion of your data over time. Why? Imagine you have an item with a new data point every 5 minutes and a Grafana graph of the with of 300px. What will be be presented in a graph covering the time windows of one full year? With 300 pixels you do not even have one pixel per day, not to mention a few minutes. InfluxDB and Grafana solve this task by breaking down your data into time intervals, represented by $interval in Grafana.

By the help of functions, you can decide which or how data inside one time interval is supposed to be handled. Iā€™ll give one example representing power consumed by a fridge:

Looking at the graph, you can see the red line, using the default mean()ā± aggregation function suffers of irritating peaks. While not being wrong, itā€™s easy to filter them out and by that flattening the visually visible graph. The two possibilities percentile(80) ā± and min() ā± are shown with good success in this example, other data series might behave differently under them.

Another query shows the usage of moving_average(500) ā±. I found this useful in the example as the steps in the consumption, created by the on-off-on-off behavior of the fridge, are quite irritation when mixed with other consumptions by other devices.

Usage of all these options is of course up to you.

Display Options

Under Display there are a few options to modify how your metrics will be presented.

A short selection discussed:

  • Staircase: By default, data points are connected by a point-to-point line. This option will draw constant horizontal lines with a step at the next data point, making this option important for all kinds of discrete dataseries (e.g. on-off states or preset temperatures in 1 degree steps)

  • Stack: Data sets will be stacked instead of drawn in front of each other. This is useful for data like power consumption.

Series specific overrides

Under ā€œDisplayā€ the series specific overrides option can be found as well. This option allows one to vary display options among series of one graph. Do not underestimate the possibilities.

Left and Right Y-Axis

I want to mention the possibility to use two y-axis to present data series with two independent value ranges and units. The y-axis can be set via series override or (conveniently) by clicking in the colored legend bar of the series:

In the following example you can see this in action. While Downlink and Uplink (in megabit per second) are linked to the left y-axis, ping (in milliseconds) uses the right y-axis.

Two more things can be seen:

  • a series specific override for ping introduces points for this one series, drawing attention to it
  • the y-min and y-max of the right axis have been fixed to 0-100ms, in order to shrink the ping distribution to the mid area of the graph

Showing diagrams in your openHAB sitemap

Item data is stored in an InfluxDB datastore and graphs are drawn by Grafana. To make this guide complete, letā€™s have a look at how you can export your graphs back into your openHAB system as a sitemap element.

ā€¦as ā€˜Imageā€™ Element

We will be exporting Grafana graphs as .png files and display them by the help of the image element on your sitemap.

To generate the URL to your graphs png file, open the graphs Share dialog:

Followed by clicking the ā€œDirect link rendered imageā€ button.

You will be presented with a static graph only view. The URL in the address bar is the information we are looking for. Pay attention to the fact, that there are a few parameters you can (and should) tune directly in the URL:

http://monitoring-host:3000/render/dashboard-solo/db/mydashboard?panelId=5&width=700&height=350&from=now-48h&to=now
                                                     ā»ā»ā»ā»ā»ā»ā»ā»ā»ā»ā»ā»        ā»       ā»ā»ā»        ā»ā»ā»      ā»ā»ā»ā»ā»ā»ā»    ā»ā»ā»
  • dashboardname + panelID: the dashboard and panel to export
  • width + height: resolution of the exported image
  • from + to: the timespan to display.

Hint: Grafana by default generated share URLs with absolute timestamps but relative dates like ā€œnow-2dā€ can be used as well, just like in the frontend time selector!

Our last step is to include the image in our sitemap. Itā€™s as easy as:

Image refresh=60000 url="http://monitoring-host:3000/render/dashboard-solo/db/mydashboard?panelId=5&width=700&height=350&from=now-48h&to=now"

One last remark: If the image is not loaded by openHAB, openHAB probably doesnā€™t have the permission to do so without login. The easiest solution is to allow anonymous read access, as described above.
Anonymous access is of course only acceptable if you are okay with the public being potentially able to look at your data or if your monitoring-host is in your personal trusted subnet behind a firewall.

If you found a clean solution to allow openHAB to access graphs by the help of login credentials, please let us know!

ā€¦as ā€˜Webviewā€™ Element

Thanks to @pahansen for suggesting this great alternative solution, utilizing a custom html view instead of a pseudo-static image. This solution has a few benefits compared to the ā€˜Imageā€™ solution above.

Attention: The above solution is known to not work with the openHAB Android app. In order to show the dynamic graph all interfaces you need to set an absolute path as URL. e.g.:

Webview url="http://YOURIP:YOURPORT/static/chartsHour.html"

ā€¦as dynamic ā€˜Webviewā€™ Element with JavaScript library

If you want to display differently parametrized views of graphs, normally a lot of Webview elements are required, which will only be presented based on certain conditions though the visibility[..] expressions. Your browser or client will always load these hidden Webviews, even though they are not visible. As a result, page load times will suffer, especially in combination with weak hardware like the RPi3 or with mobile devices.

@wborn has created a JavaScript library that uses the openHAB 2 REST API and server side events (SSE) to resolve items values to Grafana panel parameters. By that, itā€™s possible to reduce the number of Webview elements and to make graph generation more customizable directly from inside openHAB.

The code including documentation and an example demo is available at:

ā€¦inside HABPanel

Grafana diagrams can of course also be embedded in HABPanel. In fact, you can simply embed a dynamic view of your graphs. Create a new frame widget and use the Embed Sharing option of Grafana to generate the URL to include.

Persistance Data Migration

When you start using InfluxDB to store your data you might want to migrate old data from another persistance service like rrd4j to the new database.
Otherwise the new charts will only start from the time you started using InflluxDB.

@christoph_wempe developed a little bash script, which reads the available data via REST and imports the data to InfluxDB. Find all further details at:

Examples

Grafana graphs and configuration tips can be found all over the the internet. Also be sure to scroll through the comments here to find openHAB graphs by other users.

Feel free to post your screenshots! :slight_smile:


InfluxDB database and measurement hacks

Renaming measurements

If you are changing the name of an item that has been persistent by InfluxDB+Grafana or if you replace one with another for the same purpose, you might want to continue using the one measurement time series. Renaming a measurement is not possible but you can do the following:

In your InfluxDB console:

USE openhab_db
SELECT * INTO new_measurement FROM old_measurement
DROP MEASUREMENT old_measurement

/* Check the result */
SELECT * FROM old_measurement
SELECT * FROM new_measurement

Deleting invalid measurements

Deleting parts of a measurements might be useful, for example when the value send by openHAB resulted out of an error and is not a correct measurement.

In your InfluxDB console:

USE openhab_db

/* First search for the measurements you are looking for */
SELECT * FROM my_measurement WHERE value = 0
SELECT * FROM my_measurement WHERE time >= '2016-07-25T00:00:00Z' AND time <= '2016-07-27T00:00:00Z'

/* Delete what you do not like */
/* either by using the search from above (careful) or by using the unique timestamp */
DELETE FROM my_measurement WHERE value = 0
DELETE FROM my_measurement WHERE time = 1469484042447000000
177 Likes

Great post!
Thanks bro!

3 Likes

Clean so far. now to create my first graph

1 Like

I stepped through it and influxDB still recommend installation via their official downloads https://influxdata.com/downloads/#influxdb (this was for a Pi3)[quote=ā€œThomDietrich, post:1, topic:13761ā€]
Finally, restart the InfluxDB service: sudo systemctl restart influxdb.service.
[/quote]
This might be sudo service influxdb restart on raspbian (debian). Either option doesnā€™t ask for any user/password privileges though so I need to take a few steps back now and check whatā€™s going on.

1 Like

I honestly just referenced one article on that topic. Iā€™m sure there are better articles out there. Iā€™ll post some instructions as soon as I come around to go through the process of installing on RPi myself.

There are more options to choose from. Raspbian Jessie is actually also provided with systemctl (systemd).
I donā€™t get why you would expect it to prompt for username/password thoughā€¦?

1 Like

I got the impression from this line, I would be prompted for a username and authentication. shrug

1 Like

Next time you try to connect to the influx cli, yesā€¦

1 Like

I got excited and couldnā€™t wait to try something out. I Installed both InfluxDB and Grafana via DockerHub. Getting them up an running was a cinch and performing the admin commands to create the DBs and users through the web interface was a little easier than dealing with Docker containers on the command line to get to the CLI.

One thing Iā€™m hesitant to give up though is the data decimation and aggregation that rrd4j provides to manage the size of the DB over time. While Influx isnā€™t quite as strict about guaranteeing that the DB wonā€™t grow beyond a certain size, it does provide the ability to automatically delete data as it gets beyond a certain age.

I really just followed the following Document on the openHhab_db.

I created a RP that deletes everything after 78 weeks (1.5 years) and set it as the default:

CREATE RETENTION POLICY one_and_one_half_year ON openhab_db DURATION 78w REPLICATION 1 DEFAULT

I might play around with a continuous query to decimate the data as it ages as well but this is enough for me for now. I only have a handful of Items that I care to chart and even even those I rarely care about for more than a week.

Great post. I eagerly await the parts on how to put these on the sitemap.

2 Likes

Well this is frustrating. Either my RP has messed something up or I did something else wrong. I followed all the config steps and was able to successfully see the sine wave chart. I configured openHAB I think correctly and there must be some sort of connectivity because Grafana sees all my persisted Items from the influxdb.persist file. A query for SELECT * from ā€œMy_Itemā€ returns no results so I know that

However, data is not being published.

I dropped the database and recreated it without the RP and everything seems to work.

Iā€™m too tired to fight this dragon tonight. If anyone else has seen this and knows a fix I would appreciate it. Otherwise Iā€™ll be scrapping my DB and trying it without the RP to see if that makes a difference.

1 Like

Hey @rlkoshak, wow nice to see that I created a bit of an interest :slight_smile:
Iā€™ve played with retention back when I started to use InfluxDB but in the end dropped the idea, simply because I decided to not need it. After a few month of usage, my database is still pretty small and honestly, I like the idea to be able to get ā€œmean values of temperature over the last 4 wintersā€ at some point :wink:

I would still be interested in a working setting, out of interest and to have it here in the guide.
@ben_jones12 do you have a downsampling and retention in place?

Thanks for reminding me, that the InfluxDB web frontend is a good alternative to the CLI. Iā€™ll add it to the guide.

Not sure whatā€™s the exact problem here ā€¦ What does SHOW DIAGNOSTICS tell you?
Be careful to have your data query and your time window matched up. A wrong combination will leave a graph drawn without content.

1 Like

Nope, much like you I havenā€™t seen my databases grow to any significant size so never looked into it.

1 Like

I wasnt aware of this thread, but configured Openhab/InfluxDB/Grafana yesterday on my system and it works flawlessly. I love it!
Getting some graphs in the Openhab frontend would be great. Saw youā€™re space allocation on first reply and was wondering if you already have some insights?
Found this docu (de, but lots of pictures) on how to extract the link that Openhab nmeeds.
best

1 Like

@waitz_sebastian the link looks promising. Tell us if you got to another result than me.

Iā€™ll hopefully soon get around to write that part of the guide. In short, thatā€™s the format Iā€™m using:

Image label="Room Temperatures" refresh=60000
   url="http://192.168.0.2:3000/render/dashboard-solo/db/mydashboard?panelId=4&width=700&height=350&from=now-48h&to=now"
1 Like

I actually just dropped the DB and the data is flowing into Influx and Grafana now. Clearly I either did something wrong in setting up the RP or RP is not compatible with Grafana. The former is the most likely. Iā€™ll pound on it some more later today and see if I can make it work. For now I can say that what I did above clearly does not work.

Even though we are not talking about a whole lot of data in size (I have it pretty well constrained to just a few Items anyway per my persistence Design Pattern), I like the idea of never needing to do maintenance of the DB even a decade from now. But I might just have to give up on that. After about 8 hours of storing data my DB is about 2.6k. If I project that Iā€™m looking at only around 2-5 Mb for the year. I can live with that.

Still, I donā€™t like leaving problems like this unanswered.

1 Like

Iā€™m persisting 56 items at the moment and there is a 7MB database for three months (new server, old db not restored) on my server. Soā€¦Meh.

But yes sure, i agree it would be nice to have that figured out.

2 Likes

Yep - I just did a quick tally up - 47 items being persisted (most of them ever 5s), for just over 5 months and I am using 53M.

So roughly 2MB per item per year, or 50 items for 10yrs = 1GB. Pretty managable I think in todays world of cheap storage.

1 Like

extremely well written write up! I was just working on setting this up following the instructions from each application separately but yours was so much nicer written and all in one place, that made it so much easier. Also, Iā€™m in love with grafana :slight_smile:

Hereā€™s what Iā€™ve got after a couple minutes of finally getting openhab talking to my PLC properly - http://i.imgur.com/nCnVAef.png

Looks so much better than what I was using before!

2 Likes

Maybe worth to insert in http://docs.openhab.org ā€¦ :slight_smile:

2 Likes

I got this to work and am rather happy with it. I thought I would post some cliffā€™s notes on what I did for those who may want to go down the Docker route.

  • I installed both InfluxDB and Grafana using the official Docker images from Docker Hub:

    docker pull influxdb
    docker pull grafana/grafana

  • I next configured them both to run as a service. I do not recycle containers (i.e. a new container gets built every time the service starts) so I mount the config file and a path for the InfluxDB and Grafana database files. Also, as a lesson learned I had to mount /etc/localtime to the containers to make the graphs generated by in the right timezone.

influxdb.service

[Unit]
Description=influxdb
Requires=docker.service
After=docker.service

[Service]
Restart=always
ExecStart=/usr/bin/docker run --name=%n -p 8083:8083 -p 8086:8086 -v /opt/influxdb:/var/lib/influxdb -v /opt/influxdb/influxdb.conf:/etc/influxdb/influxdb.conf:ro -v /etc/localtime:/etc/localtime:ro influxdb -config /etc/influxdb/influxdb.conf
ExecStop=/usr/bin/docker stop -t 2 %n ; /usr/bin/docker rm -f %n

[Install]
WantedBy=multi-user.target

grafana.service

[Unit]
Description=grafana
Requires=docker.service
After=docker.service

[Service]
Restart=always
ExecStart=/usr/bin/docker run --name=%n -p 3030:3000 -v /opt/influxdb/grafana.ini:/etc/grafana/grafana.ini -v /opt/influxdb:/var/lib/grafana -v /etc/localtime:/etc/localtime:ro grafana/grafana
ExecStop=/usr/bin/docker stop -t 2 %n ; /usr/bin/docker rm -f %n

[Install]
WantedBy=multi-user.target

NOTE: I had permission problems when I tried to mount the grafana.ini file ro which I havenā€™t resolved so I mount it rw as you see above.

  • Because Iā€™m running in Docker, it was easier to use the web admin interface for InfluxDB instead of the cli interface. The commands are identical to @ThomDietrichā€™s above. However, there is a way to run the CLI, see the notes for the Docker image on DockerHub.

  • Grafana is supposed to support passing in changes to the config via environment variables rather than needing to mount the ini file but I couldnā€™t get it to work.

  • Creating the carts themselves is pretty easy. To put two Items with different scales (e.g. a Switch and a Temperature) on the same graph, you can configure a right hand axis and then move those Items to the right hand access by applying a Series Override in the Display tab.

  • To discover the panelID and see the general format of the URL to the chart press the Label of the graph, choose ā€œShareā€ and select ā€œDirect Linkā€ at the bottom. This will open just an image of that chart in a separate tab.

NOTES:

  • I still havenā€™t figured out how to set up the RP
  • I also havenā€™t figured out how to make Grafana not interpolate certain series (e.g. see the Fan line above which is a Switch)
  • Iā€™ve noticed with the longer timer periods there can be a noticeable delay.
4 Likes

Great tutorial @rlkoshak Thanks. Iā€™ll remember to link it above.

You need to not use the mean aggregator: https://docs.influxdata.com/influxdb/v0.13/query_language/functions
Iā€™m using the last selector for switches

SELECT last("value") FROM "switchitem" WHERE $timeFilter GROUP BY time($interval) fill(null)
2 Likes