InfluxDB+Grafana persistence and graphing

RGB73 · March 8, 2017, 4:11pm

How i can install library and all necessary file ?

rtvb · March 8, 2017, 5:32pm

I doubt running as root is a safe setup?

Udo_Hartmann · March 8, 2017, 8:43pm

It’s definitely not necessary to run influxdb as root.

As you did the first start of influxdb from user openhabian, there are probably a few files, that now belong to user openhabian and therefor the user influxdb has no write permission.

Stop influxdb, search all the files that should belong to influxdb (logs under /var/log/influxdb and stored data under /var/lib/influxdb/ ) and chown influxdb: all the stuff…
Change user and group to influxdb again and restart the service.

Running influx as root is a great security leak, as you can perform SQL queries through http://ip.of.your.pi:8083/

If you can’t find all files, try to reinstall influxdb and take a look in sudo journalctl -eu influxdb to see failing read/write requests.

RGB73 · March 10, 2017, 2:45pm

Ok … i agree but everything has influxdb as user except this:

cd /usr/bin/
-rwxr-xr-x 1 root root 6.5M Jan 24 04:24 influx*
-rwxr-xr-x 1 root root 15M Jan 24 04:24 influxd*
-rwxr-xr-x 1 root root 7.8M Jan 24 04:24 influx_inspect*
-rwxr-xr-x 1 root root 6.8M Jan 24 04:24 influx_stress*
-rwxr-xr-x 1 root root 11M Jan 24 04:24 influx_tsm*

Perhaps i need to change all of them?

sihil · March 13, 2017, 9:25pm

Hi everyone - I’ve tried to follow this tutorial without success. I would typically consider my ability to troubleshoot as pretty good, but have hit a road block with this.

I’m on OpenHAB v2 on a Pine64 setup using OpenHABian. The influxdb plugin is 1.9.0 and the version of influxDB is 1.2.0. This is a new setup that uses z-wave network containing 9 nodes. I’ve done little manual configuration (all thing and item configuration has been done through HABmin so they do not appear in /etc/openhab2), but have successfully written a couple of rules that are working as expected.

The start of the tutorial went well. InfluxDB and Grafana are both installed and the sine wave test was successful. Connecting it to OpenHAB seems to have worked, but values are not appearing in InfluxDB.

I’ve done a few things so far:

My influxdb.persist file has been stripped back to the bare minimum (the v1 docs says that “a strategies section must be included (with a default defined), or the persistence services will not work.”):

$ cat influxdb.persist
Strategies {
	default : everyChange, restoreOnStartup
}
Items {
    *                         : strategy = everyChange, restoreOnStartup
}

Having logged in to the bundled Karaf console I’ve confirmed that the config (password etc) is being loaded and the addon / ‘bundle’ is loaded using the bundle:list and config commands (config below).

Pid:            org.openhab.influxdb
BundleLocation: null
Properties:
   db = openhab_db
   password = <redacted>
   retentionPolicy = autogen
   service.pid = org.openhab.influxdb
   url = http://127.0.0.1:8086
   user = openhab

I enabled TRACE level logging on org.openhab.persistence by adding a line to /var/lib/openhab2/etc/org.ops4j.pax.logging.cfg (the line I added is log4j.logger.org.openhab.persistence = TRACE).
Checked that the InfluxDB addon starts up - the openhab log contains the following lines:

2017-03-13 20:38:20.584 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'influxdb.persist'
2017-03-13 20:38:31.994 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTING - org.openhab.persistence.influxdb
2017-03-13 20:38:31.998 [DEBUG] [.InfluxDBPersistenceServiceActivator] - InfluxDB persistence bundle has been started.
2017-03-13 20:38:32.001 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTED - org.openhab.persistence.influxdb
2017-03-13 20:38:32.031 [DEBUG] [.internal.InfluxDBPersistenceService] - influxdb persistence service activated
2017-03-13 20:38:33.046 [DEBUG] [.internal.InfluxDBPersistenceService] - database status is OK, version is 1.2.0
2017-03-13 20:38:33.059 [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=<redacted>, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=175, service.id=298, service.bundleid=201, service.scope=bundle} - org.openhab.persistence.influxdb

Checked the influxDB log to see if there are any accesses or errors there. This line shows up at the same time as the DEBUG line above indicating that the config is at least somewhat correct:

Mar 13 20:38:33 openHABianPine64 influxd[28258]: [httpd] 127.0.0.1 - - [13/Mar/2017:20:38:33 +0000] "GET /ping HTTP/1.1" 204 0 "-" "okhttp/2.4.0" 0649abaf-082d-11e7-8031-00000000

I’ve also noticed that I can see requests being made to InfluxDB by HABmin when I am trying to use the chart editor. So again the wiring of the influx side seems to be working correctly.

However nothing seems to be stored in InfluxDB (I’m looking for series or measurements in the influxDB console):

$ influx -username admin -host localhost -password ''
password:
Connected to http://localhost:8086 version 1.2.0
InfluxDB shell version: 1.2.0
> use openhab_db
Using database openhab_db
> show series
> show measurements
>

I also note that nothing appears in the influxDB logging any attempt to insert a value. Neither do I see anything in the OpenHAB logs, other than 2017-03-12 21:12:19.895 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'influxdb.persist' which indicates that it has at least read the file.

On the surface this looks similar to Influxdb: no data in db but unlike that I’m not seeing any storing xxxxx in influxdb messages - and I’m already specifying a retentionPolicy configuration to autogen that exists in the database:

> show retention policies
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true

I’m not sure if there is a way of seeing how the influxdb.persist file is parsed and used, but it seems to me at the moment that no attempt is being made to write to Influx so perhaps this isn’t being parsed in the way I expect.

I suspect that this is something simple that I’ve missed due to being unfamiliar with OpenHAB. Any further suggestions would be really helpful.

sihui · March 14, 2017, 8:00am

The only difference to my - working - influx I can see (although I’m on RPi with Raspbian):

Pid:            org.openhab.influxdb
BundleLocation: null
Properties:
   db = openhab_db
   password = xxxxxxxxxxxxxxxxx
   retentionPolicy = autogen
   service.pid = org.openhab.influxdb
   url = http://192.168.2.237:8086
   user = openhab

So maybe you could try to configure the ip directly.

ThomDietrich · March 14, 2017, 10:14am

Hey, thanks for the thorough error description. Everything looks good to me and the log line “database status is OK, version is 1.2.0” suggests the connection is correctly established (maybe you could test this by changing e.g. the password).

My bet would be, that your influxdb.persist file is not accepted… for whatever reason. Maybe it’s worth a try to replace it with my file.

If this is in fact the cause of the problem, we should consider improving the parsing of persist files.

sihil · March 14, 2017, 10:34pm

I’ve reinstalled this evening (due to some unrelated boot issues on my SBC) and now have InfluxDB 1.2.1.

I changed the password to a known incorrect value. This didn’t really show up any errors, however when I was trying to view charts in HABmin I could see 401s being served in the Influx log. After fixing the password the same queries were producing 200s. The ping worked either way, but having seen the 401/200 status codes I’m confident in the connection.

I’ve downloaded and installed your influxdb.persist file. I assume however that it will not actually do anything useful as I don’t have anything in my installation that matches any of your patterns?

ThomDietrich · March 14, 2017, 11:58pm

you are right, you need to insert items/groups of yours. I just wanted to rule out all kinds of possible errors in the file… hope it helps

sihil · March 21, 2017, 12:04am

Finally after a lot of fiddling and testing I narrowed down my problem. It turns out that my config file was indeed invalid. In the end I changed the contents of my Strategies section from:
default : everyChange, restoreOnStartup
to
default = everyChange, restoreOnStartup

Now everything is working as hoped. Thanks for your thoughts and ideas.

Some form of error in the log file saying that the file could not be parsed or similar would be great for those who venture after me

tanuj_wadhawan · March 21, 2017, 5:59am

Hi There,

I have been working on influx DB & Grafana integration in order to create dashboard for jmeter performance tests in real time.
Both Influx Db & Grafana had been installed on the local machine. Samples of jmeter tests can be seen in the Grafana dashboard Table & graphs options.

Requirement is to create a table having multiple columns

First column would be txn name like TS01_01_Launch
Second column should have value of ok.avg sample
Third column should have value of ok.max sample and so on.

Can you assist me in order to write a query under table option to create a table having multiple columns by using different samples. please find below screenshot for the same.

tanuj_wadhawan · March 21, 2017, 6:00am

As of now i am able to create table having values of only one sample like ok.avg or ok.min. Please find below screenshot for the same.

tanuj_wadhawan · March 21, 2017, 6:01am

Can you assist me in order to write a query under table option to create a table having multiple columns by using different available samples. for example

First column would be txn name like TS01_01_Launch
Second column should have value of ok.avg sample
Third column should have value of ok.max sample and so on.

please find below required table

ThomDietrich · March 21, 2017, 12:47pm

Before we go any further into Grafana specifics, did you look at http://docs.grafana.org/features/panels/table_panel ?

tanuj_wadhawan · March 21, 2017, 1:07pm

Hi ThomDietrich,

Thanks for sharing above info.

I have already gone though to above link.

Unfortunately, i didnot find solution to my problem yet.

tanuj_wadhawan · March 22, 2017, 8:15am

Hi ThomDietrich,

Did you get a chance to look into above concern?

ThomDietrich · March 22, 2017, 12:45pm

Seems to work for me:

Is this what you are looking for?

tanuj_wadhawan · March 22, 2017, 1:32pm

Hi ThomDietrich,

Thanks for sharing above info.

i would require results in below table format.

similarly, second row should have data for another sample. i.e. TS01_02_Login

ThomDietrich · March 22, 2017, 1:36pm

Ah… you are not looking for time series data but current values organized in both rows and columns? I’m not sure if Grafana offers this. You can use Grafanas build-in aggregation functions (avq, min, max, …) in combination with the right “Table Transform” setting, which will work in the way you show in your example.

tanuj_wadhawan · March 23, 2017, 5:26am

Hi ThomDietrich,

I tried using table series aggregation option and able to create table in the required format of having multiple columns.

Unfortunately, using the above approach data conflict occurs. It can not be used for my requirement.

Could you please assist me with another feasible approach.