InfluxDB+Grafana persistence and graphing

influxdb
graphs
grafana
Tags: #<Tag:0x00007fd313b99ec8> #<Tag:0x00007fd313b99cc0> #<Tag:0x00007fd313b99ae0>

(Alon Levy) #467

any idea how to remove the Grafana title bar from the shared generated url for Habpanel frame widget?

EDIT: found, need to ‘add &kiosk’ to the end of the URL (grafana v4)


( ) #469

Hey guys!

A couple of months ago I’ve added a section in how to maintain the influx database, namely how to move measurements data from one table to the other. This is often needed because measurements names follow the name of items, which you might change over time.

InfluxDB: Moving MEASUREMENTS after renaming an Item

Check the first post to find the basic commands. I found it to be cumbersome when mass-editing Items. Here is a simple bash script to move measurements quickly. Be aware, that this is a first draft and I’d ask whoever uses it, to post an improved version as payback :wink: Thanks


user='admin'
password='SuperSecretPassword123+'
db='openhab'

influx_execute() {
        influx -username $user -password $password -database=$db -execute "$1"
}

influx_execute 'SHOW MEASUREMENTS'

# Single MEASUREMENT
influx_execute "SELECT * INTO Balcony_Temperature FROM Balcony_TempSensor12"
influx_execute "DROP MEASUREMENT Balcony_TempSensor12"

#Multiple MEASUREMENTs based on Search&Replace
existing=$(influx_execute 'SHOW MEASUREMENTS')
old="LivingRoom_"
new="LR_"
for measurement in $existing; do
       if [[ "$measurement" =~ ^$old.* ]]; then
                   measurement_new=${measurement/$old/$new}
                   echo "Rename $measurement to $measurement_new"
                   if [[ $existing = *"$measurement_new"* ]]; then
                        echo "It's there! Let's do this!"
                        influx_execute "SELECT * INTO $measurement_new FROM $measurement"
                        influx_execute "DROP MEASUREMENT $measurement"
                   else
                        echo "New measurement name doesn't exist. Skip"
                   fi
           fi
done

Known issue! If the new measurement table doesn’t exist, the drop will happen anyhow.


( ) #470

@Dangar that’s amazing! You should post your own short Tutorial so more users become aware!
Edit: I’ve added your script to the first post.


(Peter Spolenak) #471

Hello guys,
I’m total beginner with openhab2 and influxdb. I set up my dashboard that shows data from my ESP8266 with DHT22 sensor. I also set up influxdb persistence few weeks ago but I found that data in database was only from 13.2.2018 between 00:00 and 20:00, even that sensor was sending data few days. I made another fresh installation of all components but now everything seems ok, but there is no measurements or data in database. In Grafana, I successfuly added data source. I remember that when I was doing it for th first time then my measurements were visible in grafana graph metrics/FROM row but now it seems like no measurements are created and also there is no data in DB.
Log file …/openhab2.log shows:

2018-02-26 20:15:06.452 [INFO ] [er.internal.HomeBuilderDashboardTile] - Started Home Builder at /homebuilder
2018-02-26 20:15:09.000 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at http://192.168.1.129:8080
2018-02-26 20:15:09.017 [INFO ] [.dashboard.internal.DashboardService] - Started dashboard at https://192.168.1.129:8443
2018-02-26 20:15:23.206 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'DHT22_1.items'
2018-02-26 20:15:24.347 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'influxdb.persist'
2018-02-26 20:15:29.571 [INFO ] [thome.model.lsp.internal.ModelServer] - Started Language Server Protocol (LSP) service on port 5007
2018-02-26 20:15:32.002 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'test.sitemap'
2018-02-26 20:15:38.209 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2018-02-26 20:15:38.390 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2018-02-26 20:15:38.579 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2018-02-26 20:15:39.185 [INFO ] [penhab.io.transport.mqtt.MqttService] - MQTT Service initialization completed.
2018-02-26 20:15:39.193 [INFO ] [t.mqtt.internal.MqttBrokerConnection] - Starting MQTT broker connection 'mosquitto'
2018-02-26 20:15:39.801 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTING - org.openhab.persistence.influxdb
2018-02-26 20:15:39.824 [DEBUG] [.InfluxDBPersistenceServiceActivator] - InfluxDB persistence bundle has been started.
2018-02-26 20:15:39.840 [DEBUG] [org.openhab.persistence.influxdb    ] - BundleEvent STARTED - org.openhab.persistence.influxdb
2018-02-26 20:15:39.890 [DEBUG] [.internal.InfluxDBPersistenceService] - influxdb persistence service activated
2018-02-26 20:15:40.995 [DEBUG] [.internal.InfluxDBPersistenceService] - database status is OK, version is 1.4.2
2018-02-26 20:15:41.571 [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://192.168.1.129:8086, password=mypassword, component.name=org.openhab.persistence.influxdb, retentionPolicy=autogen, component.id=191, service.id=314, service.bundleid=210, service.scope=bundle} - org.openhab.persistence.influxdb

My items file:

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

Items {
    Sensor_1_temperature*, Sensor_1_humidity*, Sensor_1_switch*   : strategy = everyMinute, everyHour, restoreOnStartup
}

My items:

String Sensor_1_temperature "temp [%s]▒C" (Sensor_1) {mqtt="<[mosquitto:Sensor_1/temperature:state:default]"}
String Sensor_1_humidity "hum [%s%%]" (Sensor_1) {mqtt="<[mosquitto:Sensor_1/humidity:state:default]"}
Switch Sensor_1_switch {mqtt="<[mosquitto:Sensor_1/out/status:state:ON:1],<[mosquitto:Sensor_1/out/status:state:OFF:0],>[mosquitto:Sensor_1/out/switch:command:ON:1],>[mosquitto:Sensor_1/out/switch:command:OFF:0]"}

Could anybody be so kind and help me find the reason for my problem ? I read and tryed many things from this page but so far I made no progress. And it really drives me crazy …:smiley:


(Angelos) #472

use a new thread to describe the issue (not many will see the issue within a tutorial thread)

try to remove the * from the .persist (not items) file
(or put the items in a group and then persist the group*)


(Peter Spolenak) #473

Angelos, thank you so much for the hint. Now everything works as a charm :grin:
I thought that * symbol is some kind of pointer, but it’s obviously not … I don’t know proper syntax well …
Thanks again,

Peter


(Udo Hartmann) #474

The notation group* is: Do persistence to the childs, not the group item itself.


(Peter Spolenak) #475

Thank you Udo, now it make sense to me :wink:
Can you suggest me where can I found more information about such things ?
Regards,

Peter


(Udo Hartmann) #476

Sure. https://docs.openhab.org/

In question of persistence, for this detail of configuration: https://docs.openhab.org/configuration/persistence.html#items


(Jonas Scherrer) #477

I have OH2 running on an RPi and installed influx/grafana with the configuration tool.
By following this tutorial I stumble over one issue:
I tried to copy grafana.ini to my workstation for editing with WinSCP but failed because of the permissions for this file. How can I edit this file easiest?

Furthermore I did run the sine.py to fill the db with some data. It is now running since 3 days and still inserting points. I wonder whether this is common with a RPi and takes its time.

Jonas


(M) #478

I have an issue with seting up the graphs correctly. My persistence is

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

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

Items{
	*: strategy=everyHour, everyChange, restoreOnStartup
}

and i have a status variable that stays 1 all the time (so a 1 is persisted every hour) and a temperature that updates every minute. If i plot the data for 1 hour the status variable goes from 0 to 1 on the full hour though its 1 all the time but there are no other persisted data points before


(Dave) #479

Hi
i’m not able to get this “dashboard-solo”-Link out of Grafana. When go to “SHARE” i get this window.


is this the right adress to put in this file?


<!DOCTYPE html>
<html>
<head>
        <meta http-equiv="Content-type" CONTENT="text/html; charset=utf-8">
        <link rel="stylesheet" type="text/css" href="charts.css" />
</head>

    <div class="container">
        <iframe src="http://192.168.1.20:3000/dashboard-solo/db/environment?from=now-1h&to=now&panelId=1"></iframe>
    </div>
  </br>
    <div class="container">
        <iframe src="http://192.168.1.20:3000/dashboard-solo/db/environment?from=now-1h&to=now&panelId=2"></iframe>
    </div>
</html>

BR David


(Mr. Yoinkz) #480

Hi all,

So I got this influx and grafana up and running on my openhabian on a rpi3. Everything went great without erros and now after some time I see all my units that i pupulated in my .persist file.

So far so good.

But if I set it up like this:

… nothing seems to be happen :frowning:

Does anyone have a clue here?

EDIT: I got it working and now I’m able to see the Data.
I’m still having problems showing the graphs in my BasicUI, because the share link I get from Grafana doesn’t look like the one explained in the guide.

Also if I try to set it up in the habPanel, it provides me with the possibility to edit my graph, which I don’t want to.

Any clue?

EDIT: Got it solved by restarting the service and I also had to wait for the persist file to update.


(Mr. Yoinkz) #481

Hi all,

Does anyone know how you can delete and old “item” in Grafana (influx).
Currently I had added a group to persistent file that was holding a lot of items that I don’t want to measure.

I am thinking you can use some kind of QUERY, but I’m a bit scared if I end up dropping the whole database after reading the documentation. Does anyone know how to remove no longer used items?

Thanks


(Sergey M) #482

Perhaps this is what you are looking for:
(I do not quite understand in English)

influx
USE openhab_db
DROP MEASUREMENT old_measurement

(Mr. Yoinkz) #483

@Olymp thanks for the reply :).

I tried what you wrote, but unfortunately the items still show up in there.

image

Currently it should only be the ones with _Illumination and _Temperature that should be present.


(Sergey M) #484

Check that the items are not saved in the database (not specified in the persistence settings).

influx -precision=rfc3339
USE openhab_db
SELECT * FROM my_measurement

Are there any data after these commands are executed?
If not, maybe restarting the Graphanф (computer) will help?


(Johfeu) #485

I think this should be possible with the openhab Rest API endpoint
DELETE /persistence/items/{itemname} ify ou give influxdb as service, itemname and starttime endtime to maximum
But i nether used that. Just looks like that from the REST API console


(Fg2it) #486

I finally made an aarch64/arm64 package (deb and tar.gz) for grafana (current last version is v5.0.1). As I don’t have aarch64 board, testing was limited (docker and qemu) and if some of you are interested in testing, feedback would be much appreciated (ideally in the github issue thread).

The packages (deb and tar.gz) are available on github release section. It should work on debian/stretch (older version will probably need some workaround for the png render feature used by direct link). The deb package is also available in a deb repo on bintray with the previous version v5.0.0 (v5.0.1 direct link).

For now, the packages are flagged as experimental, so

sudo apt-get install apt-transport-https curl
curl https://bintray.com/user/downloadSubjectPublicKey?username=bintray | sudo apt-key add -
echo "deb https://dl.bintray.com/fg2it/deb-arm64 stretch experimental" | sudo tee -a /etc/apt/sources.list.d/grafana.list

and

sudo apt-get update
sudo apt-get install grafana

As a side note, these builds include an aarch64/arm64 binary of phantomjs v2.1.1 which is available independently from an other github repo. Feedback is also welcome.


(Mr. Yoinkz) #487

Hi Sergey,

When I use the SELECT * FROM my_measurement, it doesn’t show me anything.