# InfluxDB+Grafana persistence and graphing

**URL:** <https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761>\
**Category:** Tutorials & Examples\
**Tags:** influxdb, graphs, grafana\
**Created:** [September 1, 2016, 5:56pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761 "2016-09-01T17:56:35Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 1, 2016, 5:56pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/1 "2016-09-01T17:56:35Z")

</div>

Store your openHAB item states in an [InfluxDB](https://influxdata.com/time-series-platform/influxdb) time-series datastore and create highly customizable diagrams with [Grafana](http://grafana.org). These diagrams can in return be included in your sitemap as images.

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/3/35b831f9a4b6fc345eda75c3958ed92d8ce52299.png)

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](#starting-point)
- [InfluxDB Installation and Setup](#influxdb-setup)
- [Grafana Installation and Setup](#grafana-setup)
- [Connecting Grafana to InfluxDB](#connecting-grafana-to-influxdb)
- [Connecting openHAB to InfluxDB](#connecting-openhab-to-influxdb)
- [Building openHAB Diagrams in Grafana](#grafana-diagrams)
  - [Selection/Aggregation Functions](#grafana-diagrams-functions)
  - [Display Options](#grafana-diagrams-displayoptions)
  - [Series specific overrides](#grafana-diagrams-overrides)
  - [Left and Right Y-Axis](#grafana-diagrams-yaxes)

- [Showing diagrams in your openHAB sitemap](#graph-as-openhab-element)
  - […as ‘Image’ Element](#graph-as-openhab-image)
  - […as ‘Webview’ Element](#graph-as-openhab-webview)
  - […as dynamic ‘Webview’ Element](#graph-as-openhab-webview-js)
  - […as dynamic ‘Webview’ Element with JavaScript library](#graph-as-openhab-webview-js)
  - […inside HABPanel](#graph-in-habpanel)

- [Persistance Data Migration](#migration)
- [Examples](#examples)

* * *

## Starting Point

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

- openHAB 2.0
  - [InfluxDB persistence service](https://github.com/openhab/openhab/wiki/InfluxDB-Persistence) (persistence-influxdb-1.9.0.SNAPSHOT)

- On the same system or on a system in the same network
  - x64 Debian/Ubuntu derivative (called `monitoring-host`)
  - [InfluxDB](https://influxdata.com/time-series-platform/influxdb) (v1.3.2)
  - [Grafana](http://grafana.org) (v4.4.3)

Installation on a Raspberry Pi is also possible, installation instructions are [given here](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/86).

Installation inside a docker image is described [here](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/21).

**Attention rrd4j users:** A script to transfer your existing data from rrd4j to InfluxDB (by @Dangar) can be found here: [GitHub - jhron/rrd2influxdb: Tool for converting Openhab rrd4j data file to InfluxDB line protocol file. · GitHub](https://github.com/jhron/rrd2influxdb)

## InfluxDB Installation and Setup

Following the [installation instructions](https://docs.influxdata.com/influxdb/v0.13/introduction/installation), 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](https://docs.influxdata.com/influxdb/v0.13/introduction/getting_started) 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](https://docs.influxdata.com/influxdb/v0.13/administration/authentication_and_authorization), depending on the reachability of your `monitoring-host`. In this case, you only need to create the database.

```sql
$ 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](https://docs.influxdata.com/influxdb/v0.13/query_language).

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

```auto
[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:

```auto
influx -username admin -password SuperSecretPassword123+ -host localhost

```

If you ran into any problems, please refer to the [InfluxDB documentation](https://docs.influxdata.com/influxdb/v0.13) 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](https://gist.github.com/ThomDietrich/ff836dbe0f0eaa2c5270a846a963893b). If you are not familiar with Python, please have a look at a short tutorial, e.g. [this one](http://pythoncentral.io/execute-python-script-file-shell/). 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](http://docs.grafana.org/installation), to install Grafana on your `monitoring-host`.

After successful installation you should be able to reach the Grafana dashboard at [http://monitoring-host:3000](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](http://docs.grafana.org/installation/configuration) file `/etc/grafana/grafana.ini`:

```auto
[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](http://docs.grafana.org/) 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:

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/5/5785ae20dd525871cf4e612f1803d2fe212f1d66.png)

Finally, create a new dashboard, create a [graph panel](http://docs.grafana.org/reference/graph), select the freshly created data source and build your first query, presenting the previously injected sine wave time series.

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/a/a490d37bd076e36e7a25752141cb857dfff1b44d.png)

## 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](https://github.com/openhab/openhab/wiki/InfluxDB-Persistence) (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`:

```ini
# 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](https://github.com/openhab/openhab/wiki/Persistence) including fitting strategies to `/etc/openhab2/persitence/influxdb.persist`:

```java
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 😉

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:

- [The official Grafana documentation](http://docs.grafana.org)
- [Grafana Graph Panel documentation](http://docs.grafana.org/reference/graph)
- [Grafana InfluxDB specific documentation](http://docs.grafana.org/datasources/influxdb)

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

### Selection/Aggregation Functions

InfluxDB provides a [set of functions](https://docs.influxdata.com/influxdb/v1.0/query_language/functions) that influence what is returned and therefore presented by Grafana. To understand these, you first need to understand the concept behind [“GROUP BY”](https://docs.influxdata.com/influxdb/v1.0/troubleshooting/frequently_encountered_issues/#understanding-the-time-intervals-returned-from-group-by-time-queries).

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/3/3f450cd92d0bb52492231dca3e181219387a02ca.png)

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:

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/d/d95d2c47464f88f44bb84ad1d6dee529792f593f.png)

Looking at the graph, you can see the red line, using the default `mean()`[ⁱ](https://docs.influxdata.com/influxdb/v1.0/query_language/functions/#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)` [ⁱ](https://docs.influxdata.com/influxdb/v1.0/query_language/functions/#percentile) and `min()` [ⁱ](https://docs.influxdata.com/influxdb/v1.0/query_language/functions/#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)` [ⁱ](https://docs.influxdata.com/influxdb/v1.0/query_language/functions/#moving-average). 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.

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/9/95d2bf0911fe571385d6d581d3f092a91cfd4ce6.png)

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.

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/9/9a754a1f7762a6b4a30c77147a754d8b107b0d41.png)

### 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:

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/a/aa31ae9380c4108f3892ff46c878c53ceb149c25.png)

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

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/0/03fe0c709cc0f4f9a4b0bd7979320710388f07cf.png)

## 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:

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/5/58943b3ec5553918fc6d9dbcc95f54e06916e885.png)

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:

```plaintext
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:

```java
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.

> [@InfluxDB+Grafana persistence and graphing](https://community.openhab.org/t/13761/69):
>
> I’ve just got this up and running and have encountered the same issue as you @vespaman I don’t have a reverse proxy set up, but am running openHABian fwiw. I have triple checked my login settings match those set out in the guide, but still no image actually loads. As a workaround (a better option @ThomDietrich?) i’ve set up a few html pages with iframes to the graphs. And not only does it work, it loads pretty quickly on my Pi3 and has some of the interactive elemnts of the original grafana gr…

[![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/6/6133cad633b0a966fab9cc1e5805a2a371f225de.png) ](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/6/6133cad633b0a966fab9cc1e5805a2a371f225de.png)

_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.:

```auto
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:

> **[GitHub - wborn/openhab-grafana: JavaScript utilities and examples for using...](https://github.com/wborn/openhab-grafana)**
>
> JavaScript utilities and examples for using Grafana with openHAB

### …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.

![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/4/4f28d849a42c435c495acce0cd43ea97f4a075a8.png)

## 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:

> **[GitHub - CWempe/rest2influxdb](https://github.com/CWempe/rest2influxdb)**
>
> Contribute to CWempe/rest2influxdb development by creating an account on GitHub.

## 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! 🙂

* * *

## 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:

```sql
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:

```sql
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

```

---

<div class="post-metadata">

**Author:** ![walter](https://community.openhab.org/user_avatar/community.openhab.org/walter/32/515_2.png) [@walter](https://community.openhab.org/u/walter)\
**Post date:** [September 1, 2016, 8:01pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/4 "2016-09-01T20:01:26Z")

</div>

Great post!  
Thanks bro!

---

<div class="post-metadata">

**Author:** ![p0lar](https://community.openhab.org/letter_avatar_proxy/v4/letter/p/7993a0/32.png) [@p0lar](https://community.openhab.org/u/p0lar)\
**Post date:** [September 1, 2016, 10:18pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/5 "2016-09-01T22:18:07Z")

</div>

Clean so far. now to create my first graph

---

<div class="post-metadata">

**Author:** ![pahansen](https://community.openhab.org/user_avatar/community.openhab.org/pahansen/32/4938_2.png) [@pahansen](https://community.openhab.org/u/pahansen)\
**Post date:** [September 1, 2016, 10:48pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/6 "2016-09-01T22:48:43Z")

</div>

> [@ThomDietrich](#):
>
> Installation on a Raspberry Pi is also possible, please follow this article.

I stepped through it and influxDB still recommend installation via their official downloads [InfluxData 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.

---

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 1, 2016, 11:55pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/7 "2016-09-01T23:55:47Z")

</div>

> [@pahansen](#):
>
> I stepped through it and influxDB still recommend installation via their official downloads [InfluxData Downloads](https://influxdata.com/downloads/#influxdb) (this was for a Pi3)

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.

> [@pahansen](#):
>
> This might be sudo service influxdb restart on raspbian (debian).

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…?

---

<div class="post-metadata">

**Author:** ![pahansen](https://community.openhab.org/user_avatar/community.openhab.org/pahansen/32/4938_2.png) [@pahansen](https://community.openhab.org/u/pahansen)\
**Post date:** [September 2, 2016, 12:02am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/8 "2016-09-02T00:02:52Z")

</div>

> [@ThomDietrich](#):
>
> 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

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

---

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 2, 2016, 12:06am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/9 "2016-09-02T00:06:51Z")

</div>

Next time you try to connect to the influx cli, yes…

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [September 2, 2016, 4:14am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/10 "2016-09-02T04:14:17Z")

</div>

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.

[https://docs.influxdata.com/influxdb/v0.13/guides/downsampling\_and\_retention/](https://docs.influxdata.com/influxdb/v0.13/guides/downsampling_and_retention/)

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.

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [September 2, 2016, 4:43am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/11 "2016-09-02T04:43:10Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 2, 2016, 9:49am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/12 "2016-09-02T09:49:56Z")

</div>

Hey @rlkoshak, wow nice to see that I created a bit of an interest 🙂  
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 😉

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.

> [@rlkoshak](#):
>
> A query for SELECT \* from “My\_Item” returns no results so I know that  
> However, data is not being published.

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.

---

<div class="post-metadata">

**Author:** ![ben\_jones12](https://community.openhab.org/user_avatar/community.openhab.org/ben_jones12/32/52_2.png) [@ben\_jones12](https://community.openhab.org/u/ben_jones12)\
**Post date:** [September 2, 2016, 9:59am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/13 "2016-09-02T09:59:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![waitz\_sebastian](https://community.openhab.org/user_avatar/community.openhab.org/waitz_sebastian/32/21932_2.png) [@waitz\_sebastian](https://community.openhab.org/u/waitz_sebastian)\
**Post date:** [September 2, 2016, 11:24am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/14 "2016-09-02T11:24:33Z")

</div>

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](https://blog.veloc1ty.de/2016/01/28/grafana-png-export-von-graphen-via-http-api/) (de, but lots of pictures) on how to extract the link that Openhab nmeeds.  
best

---

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 2, 2016, 12:04pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/15 "2016-09-02T12:04:16Z")

</div>

@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:

```java
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"

```

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [September 2, 2016, 2:28pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/16 "2016-09-02T14:28:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 2, 2016, 2:33pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/17 "2016-09-02T14:33:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ben\_jones12](https://community.openhab.org/user_avatar/community.openhab.org/ben_jones12/32/52_2.png) [@ben\_jones12](https://community.openhab.org/u/ben_jones12)\
**Post date:** [September 2, 2016, 9:29pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/18 "2016-09-02T21:29:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![fohdeesha](https://community.openhab.org/user_avatar/community.openhab.org/fohdeesha/32/13449_2.png) [@fohdeesha](https://community.openhab.org/u/fohdeesha)\
**Post date:** [September 3, 2016, 3:06am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/19 "2016-09-03T03:06:23Z")

</div>

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 🙂

Here’s what I’ve got after a couple minutes of finally getting openhab talking to my PLC properly - [Imgur: The magic of the Internet](http://i.imgur.com/nCnVAef.png)

Looks so much better than what I was using before!

---

<div class="post-metadata">

**Author:** ![Udo\_Hartmann](https://community.openhab.org/user_avatar/community.openhab.org/udo_hartmann/32/70871_2.png) [@Udo\_Hartmann](https://community.openhab.org/u/Udo_Hartmann)\
**Post date:** [September 3, 2016, 4:14am UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/20 "2016-09-03T04:14:27Z")

</div>

Maybe worth to insert in [http://docs.openhab.org](http://docs.openhab.org) … 🙂

---

<div class="post-metadata">

**Author:** ![rlkoshak](https://community.openhab.org/user_avatar/community.openhab.org/rlkoshak/32/75251_2.png) [@rlkoshak](https://community.openhab.org/u/rlkoshak)\
**Post date:** [September 4, 2016, 4:24pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/21 "2016-09-04T16:24:49Z")

</div>

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:

- 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**

```auto
[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**

```auto
[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.

 ![](https://community-openhab-org.s3-eu-central-1.amazonaws.com/original/2X/8/80eab39f854a77de8e7f7b6ee6629cc8d4024601.jpg)

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.

---

<div class="post-metadata">

**Author:** ![ThomDietrich](https://community.openhab.org/user_avatar/community.openhab.org/thomdietrich/32/71239_2.png) [@ThomDietrich](https://community.openhab.org/u/ThomDietrich)\
**Post date:** [September 4, 2016, 4:46pm UTC](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761/22 "2016-09-04T16:46:02Z")

</div>

Great tutorial @rlkoshak Thanks. I’ll remember to link it above.

> [@rlkoshak](#):
>
> 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)

You need to not use the mean aggregator: [https://docs.influxdata.com/influxdb/v0.13/query\_language/functions](https://docs.influxdata.com/influxdb/v0.13/query_language/functions)  
I’m using the last selector for switches

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

```

[Next page](https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761.md?page=2)
