InfluxDB+Grafana persistence and graphing

Foxejoe · August 21, 2017, 12:18pm

Hi,
very nice guide.
I am using OpenHab & InlfuxDB & Grafana to track some data i.e. Temperature and Energy consumption.

In Grafana there is the possibility to show on the graph legend the average, min and max value inside a determined period of time (i.e Last 24h)

I would like to send this data to an OpenHab Item (i.e. Temp_Avg_Last24h). Is it posssible and how?

Regards
Lorenzo

ThomDietrich · August 21, 2017, 2:28pm

Hello @Foxejoe, that’s not possible directly as these are specific queries generated by grafana to display your desired value.
However, you are free to query whatever you want to from InfluxDB yourself. Check out the InfluxDB documentaiton on how to retrieve such measures, then build a corresponding rule.

Check out: How to store / query Influxdb from OH?

Please post your solution and I’d be happy to add a section to this tutorial!! Thanks

Dim · August 21, 2017, 2:50pm

In theory, by using a dummy item and a rule with averageSince you could get such data into your sitemap.
Search the forum for averageSince examples.

Foxejoe · August 21, 2017, 3:10pm

Thanks,
I see there is also example on OH1 wiki for persistance in Rules.
if I well understand the rules for avg temperature on the last 24h should be something like::

rule "Temperature Average"
when
Item Temperature received update
then
Temperature_Avg.postUpdate(Temperature.averageSince(now.minusHours(24)))
end

Correct?

Is there also a method to get the avg temperature from a defined period (i.I.e. from 1/1/2017 to 1/2/2017)?

Regards
Lorenzo

LastSamurai · August 24, 2017, 9:37pm

Hi, I tried to use this tutorial to get the grafana+influxDB combo running on my raspberry pi together with openhab 2. I am struggling with grafana though.
I have set up influxDB and added a grafana and an openhab user. It is already connected to openhab and data is written to the openhab_db database. I also installed grafana and changed the settings .ini file according to this tutorial. Then I logged in with the grafana admin account to the server:3000 grafana website.
Now my problem is creating the influxdb datasource. I selected influxdb and added the url. I also checked the basic auth option and that is where the problems start: which user do I have to enter here? The influxDB grafana user? The grafana admin user? I tried both and get the same problem.
In the influxDB Details part I added the database and grafana user for the influxDB. Clicking save & test shortly displays a progressbar but then keeps spamming me with a “authentification needed” popup.

Why is that? I am already logged in.
Can anyone help solving this or had similar problems? Thank you!

LastSamurai · August 25, 2017, 3:21pm

Ok I fixed the problem… turns out I somehow made an error while creating the grafana user in the influxDB. I set the password a second time and now everything is working just fine. If someone has similar problems I recommend testing the users ability to request data via curl commands (there are explanations on how to do this in the influx manual).

BrutalBirdie · August 29, 2017, 11:57am

Okay I got a strange Issue. My Influx does not store any data.
If I look at the Karaf logs (I set org.openhab.influxdb to TRACE) I only get the log from startup

13:48:58.430 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model ‘influx.persist’

Nothing more.
At this point I dont know what the problem could be.
I use
Item101 : strategy = everyChange, everyDay, restoreOnStartup

Dim · August 29, 2017, 1:31pm

use the full path:

log:set TRACE org.openhab.persistence.influxdb

If you are on OH 2.2.0 S1025 you may have some issues (it seems that build 1025 has some problems)

BrutalBirdie · August 29, 2017, 1:42pm

@Dim Thanks for that

15:40:44.016 [DEBUG] [org.openhab.persistence.influxdb ] - BundleEvent STARTING - org.openhab.persistence.influxdb
15:40:44.017 [DEBUG] [.InfluxDBPersistenceServiceActivator] - InfluxDB persistence bundle has been started.
15:40:44.018 [DEBUG] [org.openhab.persistence.influxdb ] - BundleEvent STARTED - org.openhab.persistence.influxdb
15:40:44.030 [DEBUG] [org.openhab.persistence.influxdb ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.pid=org.openhab.influxdb, db
=openhab_db, user=openhab, url=http://127.0.0.1:8086, password=youSneekyLittle, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=207, service.id=331, service.bundleid=211, service.scope
=bundle} - org.openhab.persistence.influxdb
15:40:44.044 [DEBUG] [.internal.InfluxDBPersistenceService] - influxdb persistence service activated

So but what is the problem…

5:44:05.124 [DEBUG] [.internal.InfluxDBPersistenceService] - database status is OK, version is 1.3.3
15:44:05.127 [DEBUG] [org.openhab.persistence.influxdb ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.pid=org.openhab.influxdb, db
=openhab_db, user=openhab, url=http://127.0.0.1:8086, password=youSneekyLittle, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=207, service.id=331, service.bundleid=211, service.scope
=bundle} - org.openhab.persistence.influxdb

Dim · August 29, 2017, 1:46pm

logs look clean… no hints there

what OH2 release are you running currently?

did you use until now InfluxDB as a persistence service or this is the first time that you configure it?

note: the state of Item101 will be stored only in the DB
If that is a group, use: Item101*

Ps: Maybe it would be better to open up a new thread for the troubleshooting steps.

BrutalBirdie · August 29, 2017, 1:47pm

openHAB 2.1 Stable
InfluxDB shell version: 1.3.4
Influx Bundle Version 1.10.0

On my other PI its ( No Problems here works just fine )
openHAB 2.1 Stable
InfluxDB shell version: 1.2.4
Influx Bundle Version 1.10.0

Dim · August 29, 2017, 1:56pm

Debug log reports another version number versus what the linux shell reports? (strange)

I am using OH2.2.0 S1026 with InfluxDB 1.3.4 on Debian Jessie on a regular x64 PC and all is fine…

openhab> list -s |grep -i influx
207 │ Active   │  80 │ 1.11.0.201708221612    │ org.openhab.persistence.influxdb

Here is my config setup:

root@homer:~# more /etc/openhab2/services/influxdb.cfg 
url=http://127.0.0.1:8086
user=openhab
password=LittleyouSneeky
db=openhab_db
retentionPolicy=autogen

root@homer:~# more /etc/openhab2/persistence/influxdb.persist 
Strategies {
    everyHour : "0 0 * * * ?"
    everyDay  : "0 0 0 * * ?"

    default = everyChange
}

Items {
    // persist all members of gInflux Group on every change
    gInfluxDB* : strategy = everyChange, everyHour, everyDay
}

BrutalBirdie · August 29, 2017, 2:01pm

211 | Active   |  80 | 1.10.0                 | openHAB InfluxDB Persistence bundle                    | org.openhab.persistence.influxdb

# 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://127.0.0.1:8086

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

# The password of the database user.
password=YouSneekyLittle

# 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

// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
	everyHour : "0 0 * * * ?"
	everyDay  : "0 0 0 * * ?"
	everyMinute:"0 * * * * ?"

	// if no strategy is specified for an item entry below, the default list will be used
	default = everyChange
}

/* 
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */
Items {
	// persist all items once a day and on every change and restore them from the db at startup
	g_INFLUX* : strategy = everyChange, everyDay, restoreOnStartup
    Item101 : strategy = everyChange, everyDay, restoreOnStartup
}

What is wrong?

Dim · August 29, 2017, 2:04pm

no clue

re-install the addon… maybe it needs a small kick in the @ss to get moving

Ps: take out the everyMinute:"0 * * * * ?" line. it’s not needed and it doesn’t have spaces between :

BrutalBirdie · August 29, 2017, 2:09pm

I keep getting this message

16:07:58.790 [WARN ] [el.core.internal.ModelRepositoryImpl] - Configuration model ‘influx.persist’ has errors, therefore ignoring it: [1,1]: mismatched input ‘’ expecting ‘Strategies’

But sometimes the file has erros sometimes not
Now its OK again

16:10:02.374 [INFO ] [el.core.internal.ModelRepositoryImpl] - Refreshing model ‘influx.persist’

Now its not

16:10:24.918 [WARN ] [el.core.internal.ModelRepositoryImpl] - Configuration model ‘influx.persist’ has errors, therefore ignoring it: [1,1]: mismatched input ‘’ expecting ‘Strategies’
16:10:25.022 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model ‘influx.persist’

Are there limitations for InfluxDB ?..
Because I have 389 Items and 30% of them are updating every second

Dim · August 29, 2017, 2:12pm

take out all comments (stuff starting with // and/or /* + */) from /etc/openhab2/persistence/influxdb.persist
By the way use: influxdb.persist (not influx.persist)
edit the file with a linux editor (like vim) and make sure that there is no crap lying around in the contents of the file (strange characters or line feeds, etc)

BrutalBirdie · August 29, 2017, 2:21pm

Done but still no other series in my Influxdb. Only the test sine_wave

16:22:08.553 [DEBUG] [org.openhab.persistence.influxdb    ] - ServiceEvent UNREGISTERING - {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.pid=org.openhab.influxdb, db=openhab_db, user=openhab, url=http://127.0.0.1:8086, password=YouSneekyLittle, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=207, service.id=330, service.bundleid=211, service.scope=bundle} - org.openhab.persistence.influxdb
16:22:08.554 [DEBUG] [.internal.InfluxDBPersistenceService] - influxdb persistence service deactivated
16:22:08.559 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STOPPING - org.openhab.persistence.influxdb
16:22:08.560 [DEBUG] [.InfluxDBPersistenceServiceActivator] - InfluxDB persistence bundle has been stopped.
16:22:08.562 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STOPPED - org.openhab.persistence.influxdb
16:22:08.588 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent UNRESOLVED - org.openhab.persistence.influxdb
16:22:08.677 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent UNINSTALLED - org.openhab.persistence.influxdb
16:22:08.825 [INFO ] [core.karaf.internal.FeatureInstaller] - Uninstalled 'openhab-persistence-influxdb'

16:22:38.538 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent INSTALLED - org.openhab.persistence.influxdb
16:22:38.609 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent RESOLVED - org.openhab.persistence.influxdb
16:22:38.628 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTING - org.openhab.persistence.influxdb
16:22:38.737 [DEBUG] [.InfluxDBPersistenceServiceActivator] - InfluxDB persistence bundle has been started.
16:22:38.749 [DEBUG] [.internal.InfluxDBPersistenceService] - influxdb persistence service activated
16:22:39.051 [DEBUG] [.internal.InfluxDBPersistenceService] - database status is OK, version is 1.3.3
16:22:39.055 [DEBUG] [org.openhab.persistence.influxdb    ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.pid=org.openhab.influxdb, db=openhab_db, user=openhab, url=http://127.0.0.1:8086, password=YouSneekyLittle, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=210, service.id=342, service.bundleid=212, service.scope=bundle} - org.openhab.persistence.influxdb
16:22:39.058 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTED - org.openhab.persistence.influxdb

Dim · August 29, 2017, 2:27pm

how are you concluding to this statement?
are you using the influx console to check the stuff stored in the DB?

root@homer:~# influx -username admin -password YouSneekyLittle  -host localhost
Connected to http://localhost:8086 version 1.3.2
InfluxDB shell version: 1.3.4
> use openhab_db
Using database openhab_db
> show measurements
name: measurements
name
----
E03_J7
E03_Large_LED
E03_Small_LED
...

BrutalBirdie · August 29, 2017, 2:33pm

Of course. I checked it with show measurements and show series

Connected to http://localhost:8086 version 1.3.3
InfluxDB shell version: 1.3.4
> use openhab_db
Using database openhab_db
> show series
key
---
sine_wave
> show measurements
name: measurements
name
----
sine_wave

Dim · August 29, 2017, 2:44pm

did this installation work before and it broke recently or it’s a clean start?
I don’t see anything wrong with your setup…

that should be extremely light load for influxdb… I don’t expect problems from that.
On the other hand… I wouldn’t run influx on a Pi because of small RAM.