InfluxDB+Grafana persistence and graphing

Hey Daniel,
I was not aware of this, thanks. In this case it would probably be even better to replace this library for these relatively simple actions…
Do you have any programming experience? Would be great if you could try to get this working and tested.

As for the actual problem at hand. I’ve a tutorial inside the script. Is that okay now?

Hello,
I’ve added comments to the script, see https://gist.github.com/ThomDietrich/ff836dbe0f0eaa2c5270a846a963893b

IP is the IP of your influxdb machine, if it’s on the same RPi: 127.0.0.1
DB, USER and PASSWORD are the ones you chose to set up in influxdb before, following the tutorial.

It’s a python script. You’ll need to install python and execute it. That’s beyond the scope of this tutorial. I’ve added a link to the article.

In this case please add it. If you are not on openHAB 2.0 please check the previous comments for openHAB 1.8 related things.

No need :wink: You’ve pointed out missing pieces, which is great!

Yes, that actually helps.

Unfortunately all my programming experience was long ago, and only very very little in Java/Python/anything else modern, it was mainly in assembly and C.

You just have to replace the commands by the ones given behind the link. The important thing would be testing, which I can not do currently.

I have strange problem with Grafana . I have RPi 2, so I based on this manual:

Everything went well, I have created DB according manual in first post, then generate data by sinus wave.I have checked and data was in DB.
I have also configured Grafana and add my DB, I got
Success
Data source is working

Problem is that I am unable to generate a chart or can’t see data as table. I have tried with just:
SELECT * FROM “sine_wave”

and default config + sine_wave also give nothing (like in first post).

Is it anything else that should be configured ?
Does somebody meet such problem ?

A small recommendation for people who are running InfluxDB on systems which are “sensitive” to storage I/Os (like Raspberry Pi with SD Cards):

Modify the following logging options in the InfluxDB configuration file (/etc/influxdb/influxdb.conf):

  1. Disable the Query logging. In section [data], set query-log-enabled = false
  2. Disable the HTTP request logging. In section [http], set log-enabled = false

Then, restart InfluxDB (systemctl restart influxdb.service)

From the Influxdb docs entry [Link]

Starting with version 1.0, InfluxDB on systemd systems will no longer write files
to /var/log/influxdb by default, and will now use the system configured
default for logging (usually journald). On most systems, the logs will be directed
to the systemd journal and can be accessed with the command:
`sudo journalctl -u influxdb.service`
Please consult the systemd journald documentation for configuring journald.

In my system, the logs are growing large and I don’t really need to log the queries and/or the http requests:

root@host:~# journalctl --disk-usage 
Journals take up 154.1M on disk.
root@host:~# journalctl -u influxdb.service 
-- Logs begin at Mon 2017-01-23 22:35:24 EET, end at Sun 2017-01-29 10:14:34 EET. --
Jan 23 22:35:24 homer influxd[1978]: [httpd] ::1 - grafana [23/Jan/2017:22:35:24 +0200] "GET /query?db=openhab_db&epoch=ms&q=SELECT+last%28%22valu
Jan 23 22:35:27 homer influxd[1978]: [httpd] 127.0.0.1 - openhab [23/Jan/2017:22:35:27 +0200] "POST /write?consistency=one&db=openhab_db&p=%5BREDA
Jan 23 22:35:37 homer influxd[1978]: [httpd] 127.0.0.1 - openhab [23/Jan/2017:22:35:37 +0200] "POST /write?consistency=one&db=openhab_db&p=%5BREDA
Jan 23 22:35:48 homer influxd[1978]: [httpd] 127.0.0.1 - openhab [23/Jan/2017:22:35:48 +0200] "POST /write?consistency=one&db=openhab_db&p=%5BREDA
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP6_01_01_V WHERE time > now() - 1d GR
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP6_01_01_P WHERE time > now() - 1d GR
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP6_01_02_V WHERE time > now() - 1d GR
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP3_03_01_P WHERE time > now() - 1d GR
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP3_04_02_P WHERE time > now() - 1d GR
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP3_04_03_P WHERE time > now() - 1d GR
Jan 23 22:35:55 homer influxd[1978]: [query] 2017/01/23 22:35:55 SELECT last(value) FROM openhab_db.autogen.mP6_01_03_V WHERE time > now() - 1d GR
[...]
6 Likes

I’ve been trying to get this working all day, and feel like i’m hammering myself against a brick wall!

I’ve set up influxdb and grafana on an internet-facing server, behind an nginx proxy, and am able to access and query them both. I’ve manually inserted test data into the influxdb and graphed it in grafana so i’m fairly happy with that side. The problem i’m having is that openhab refuses to put any data into the influxdb.

My setup:

  • Openhab 2.0 release
  • Influxdb binding 1.9.0
  • Influxdb 1.2.0
  • I’ve set my default persistence to influxdb.

My configs are as follows:

###services/influxdb.cfg

url=https://iot.thinkl33t.co.uk/influx/
user=backend
password=<password here>
db=iot
retentionPolicy=autogen

###items/sensors.items

Number lounge_temp "Temperature [%.2f C]" <temperature> (gSensors,gLounge) {mqtt="<[mosquitto:homie/sc-lounge/sc/temperature:state:default]"}
Number lounge_humi "Humidity: [%.2f]%" <humidity> (gSensors,gLounge) {mqtt="<[mosquitto:homie/sc-lounge/sc/humidity:state:default]"}
Number lounge_light "Light [%d]" <light> (gSensors,gLounge) {mqtt="<[mosquitto:homie/sc-lounge/sc/light:state:default]"}
Number lounge_noise "Noise: [%d]" <noise> (gSensors,gLounge) {mqtt="<[mosquitto:homie/sc-lounge/sc/noise:state:default]"}
Number lounge_dusty "Air Quality: [%d]" <smoke> (gSensors,gLounge) {mqtt="<[mosquitto:homie/sc-lounge/sc/dusty:state:default]"}

###persistence/influxdb.persist

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

Items {
    gSensors*   : strategy = everyChange, everyMinute
}

I’ve tried replacing gSensors* with ‘*’ and lounge_temp but that diddnt help.

When i change the persistence file, i get the following in my openhab.log:

17:15:03.823 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTED - org.openhab.persistence.influxdb
17:15:03.856 [DEBUG] [.internal.InfluxDBPersistenceService] - influxdb persistence service activated
17:15:04.894 [DEBUG] [.internal.InfluxDBPersistenceService] - database status is OK, version is 1.2.0
17:15:05.019 [DEBUG] [org.openhab.persistence.influxdb    ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.pid=org.openhab.influxdb, db=iot, user=backend, url=https://iot.thinkl33t.co.uk/influx, password=<passwordhere>, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=207, service.id=352, service.bundleid=209, service.scope=bundle} - org.openhab.persistence.influxdb

You can see from the log that the influx version is correct, and in my influxdb log i get:

Jan 29 17:15:04 iot influxd[1276]: [httpd] 82.69.23.89,192.168.122.6 - - [29/Jan/2017:17:15:04 +0000] "GET /ping HTTP/1.1" 204 0 "-" "okhttp/2.4.0" 79df22be-e646-11e6-8057-0000

When this happens. The item values are constantly updating and are viewable in my openhab dashboard, but the persistance job never seems to be firing off.

If anyone has any ideas how i can debug this or get it working, i’d be very grateful!

I don’t use https to connect from OH2 to InfluxDB. This shouldn’t be the problem: but have you tried http?

Also: how does your nginx config look like?

The “standard” config for the url setting is:

http://<host>:8086 or https://<host>:8084

I assume that you are reverse proxy-ing 443 to 8084 using nginx: correct?

On the other hand… your connection seems to be ok… something else is going wrong… :frowning:

Edit: I think that default = everyMinute is correct (not default: everyMinute)

@Dim the db version is shown in log.

I don’t think connection is the issue. For debugging please increase the loglevel of org.openhab.persistence.influxdb. Next you should find out if the service tries to send data. Your persist file looks pretty similar to mine, I don’t think there is a problem there.

Could it be that the influxdb.persist file gets stuck at this error and it can not be parsed further down?

1 Like

@Dim nginx config chunk thats proxying influxdb:

    location /influx/ {
        proxy_pass http://192.168.122.4:8086/;
        proxy_http_version 1.1;
        proxy_set_header Host $http_host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_redirect off;
    }

As @ThomDietrich said, the connection seems to be working.

To increase the log level, i’ve been adding

log4j.appender.org.openhab.persistence.influxdb = TRACE

to /var/lib/openhab2/etc/org.ops4j.pax.logging.cfg and not gotten anything extra or new in openhab.log as far as i can tell - am i doing that wrong?

Oooh, that seems plausible! Testing now.

edit: Its working! Thanks @Dim!

Now… Why does that bit of the config file use two different assignment operators?! :confounded:

1 Like

My main (invaluable) troubleshooting tool: http://docs.openhab.org/installation/designer.html :slight_smile: (get version 0.80)

Thanx to @rlkoshak who has been promoting it and I listened to him, I am using it daily and it saves me every time :sunglasses:

1 Like

Thanks for this tutorial. With this explanation it was absolutely easy to implement

An important hint for all Android users: the current version of Chromium (version 55) has a bug (https://bugs.chromium.org/p/chromium/issues/detail?id=662040). This means the Grafana diagrams are neither visible in the HABdroid app nor in BasicUI in the Chrome browser.
The problem will be solved in Chrome Version 56 that is available a Beta in the app store. I have tested it and the diagrams are visible in BasicUI with this Chrome version.

Hello everyone, hello @wborn,

It was easy and fun to setup openhab2, influxdb and grafana on the raspberry pi2 following this absolutely awesome tutorial. Coming from openhab1 and rrd4j I was able to create so much more appealing graphics.

But now I got stuck with my sitemaps. I want to get rid of the many almost redundant visibility switches that I used before. Thus @wborn 's java script library seems to be the perfect solution.
But I need help here installing this library. I have seen that in order to build it, I am advised to
"Use Grunt to check the library code with eslint and minify it."
For me (having no prior experience with java script) this is not enough information to get going.
Please could someone sketch out a typical install process and point out what to customise/look out for?
Where to git-clone to?
What are the software prerequisites? Do I need to install node.js?
Do I have to customise some files before or after using “grunt”? Do I need grunt at all?
Or can I just copy all files somewhere into /etc/openhab2?

Please shed some light into my darkened mind:-)

Best regards
SfromF

1 Like

If you just want to use the library you don’t need to do any building. So you can skip the Build step and also don’t need to install Grunt or Node.js. The build step is intended for people who want to modify the library.

If you want to use the library you can continue by reading the Demo section. After reading it, you can then run the Demo on you own openHAB instance. You can do so by adding the content of the example directory to the /conf/ (or /etc/openhab2) directory of your own openHAB instance.

To get a copy of my repository (with the “example” directory) either download my repository as master.zip or clone it with git clone https://github.com/wborn/smarthome-grafana.git

When the Demo example works on your own openHAB instance, you can then start modifying it to use your own real Grafana panels. E.g. edit smarthome-grafana-user-defaults.js and disable “debug”, update the “urlPrefix” so it points to your own Grafana server and update the sitemap (usually “default”) to the one where you are going to adding your own Webview. Next you’ll want to add a Webview to your own sitemap using the library based on one of the Demo examples.

3 Likes

Perfect! Thank you! Works like a charm!

EDIT: The dynamically presented charts do not get displayed by the openhab iOS App. Is this something that can be battled? They show up in mobile safari just fine.

1 Like

I don’t use iOS myself, but also had troubles to get it to work with the Android App. Eventually I did find a way to get rendered Grafana panels working from within the App. Most likely the same approach also works with the iOS app.

Recently PR #266 to turn on DOM storage in the Android App was merged. So in the next version of the Android App (1.8.0.5) the non-rendered (Javascript) Grafana panels should also work. Perhaps something similar needs to be done to get Javascript panels working on iOS.

Hey gents,
awsome tutorial. Was a snap to get this up and running. I read some comments about broken persistance when using minimumsince() function. I started using Grafana after OH2 prod release. Not sure if the mentioned problems are still valid for the version I’m using.

Here’s the deal. I’m persisting gas prices from Tankerkönig and would like to get the cheapest price for the last 3 days. Thus I’ve setup a small rule containing:

var cheapest_sprit = diesel_jet_berliner.minimumSince(now.minusDays(3), "influxdb")
logInfo("Sprit Preise", "Der günstigste Sprit Preis seit 3 Tagen ist: " + cheapest_sprit )

This does not seem to work as I do get the following error:

2017-02-08 08:54:32.467 [INFO ] [.smarthome.model.script.Sprit Preise] - Der günstigste Sprit Preis seit 3 Tagen ist: org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate$1@773e8f99

I also setup some graphs in Grafana showing 3days history and confirming the “cheapest_sprit” by Grafanas MIN() function. Thus I can confirm, OH data is persisted correctly.

Any idea to get this working is appreciated.
Thanks much

Hi,
is it possible to display bool variables as gantt (one line with one color for value=0 and other for value=1) ?


Thanks.

OK, if I add mathematical shift *100 *98 *96 to basic bool variables then I can simulate gantt. Can displaying of basic value=0 be masked out ? Zero value should be not graphically displayed ?