InfluxDB+Grafana persistence and graphing

Tutorials & Examples
, ,
284

HI Thom,

I think influxdb only able delete base on time after v0.9
on my influxdb, it got below error

delete from Pwr_CompuRoom_HamPC_RSSI where value = 100
ERR: fields not supported in WHERE clause during deletion

285

anyone knows why when use iPhone version openHab app to view grafana chart got error: {“message”:“Basic auth failed”}
by “Webview url=“http://192.168.xxx.xxx:3000/dashboard-solo/db/home-2?from=now%2Fd&to=now-1m&panelId=17&theme=light” height=10”, also tried with “‘Webview’ Element” with nothing show up.

2rcamhu.png900×1600

this error come when I use app with wifi (same network as openhab) but I got no issue by iPhone browser
also no issue on android openHab app.

I tried change the url to yahoo or just openhab ip:8080, all no issue on showing content.
any advice?

Thanks & Regards
Ham

286

My persistence config looks like this

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

And config looks like this

# 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=user

# The password of the database user.
password=password

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

I am not getting any errors or any messages at all.

Update
I am an idiot, and didn’t do the persistence configuration properly


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

Items {
	* : strategy = everyChange, everyMinute
}

This is how I got it now, and it works! :smiley:

Udklip.PNG1086×871 48.3 KB

2 Likes
287

Hello All

First off thanks for the great guide everything works, almost


And this may be stretching what is possible, or downright impossible, but here goes. For those of us who don’t have a public IP is it possible to make myopenhab do the heavy lifting in such a way that the graphs generated by the local grafana server can be seen on the great interwebs?

288

Hey @joriskofman, that’s what the integration in openHAB in the later chapters of the guide is for. If you want to access your local grafana instance from the internet (unrelated to openHAB) there are a lot of common ways to achieve this. One could be that you configure a port forwarding in your router and use a dynamic DNS service - this however depends on your kind of internet connection.

@hamwong Sorry I’m not able to help with what you gave. Being able to retrieve diagrams from grafana should be a openHAB matter and not related to the browser or device you are accessing it from. Please do some more testing.

@Mikey glad you got your system working! I am not a big fan of everyone using * : everyMinute. Did you think about the meaningfulness of that?

  1. Do not use * but instead persist selected items and groups. Persisting * is easy but do you really need the state of the e.g. astro zodiac sign item in your database?

  2. Do not use everyMinute but instead rely on everyChange and a “fallback” strategy. Looking at your example I see a Sonoff 4CH. The device will gather and send new telemetry data every 5 minutes. Logging the same value every minute doesn’t make much sense. On top of that you already got the everyChange strategy making everyMinute totally useless in the example. Other sensors are receiving change less frequent. The state of a light could stay ON for multiple hours. In this case I’d also rely on everyChange to catch the important moment, when the state changes. Additionally I like to add everyHour or every15Minutes with the sole purpose of Grafana being able to continue drawing a diagram. Besides this one reason timely strategies are pretty much unneeded in light of everyChange.

289

I did, and I also made some changes, but I needed to get it to work before starting to make changes.
Currently I got it like this

Strategies {
	everyMinute : "0 * * * * ?"
	every15Minutes : "*/15 * * * * ?"
	default = everyChange
}

Items {
	* : strategy = everyChange, every15Minutes, restoreOnStartup
	temperature, humidity, energy : strategy = everyMinute
}

But something isn’t right, because it seems to save to the database every 10 seconds now, for temperature, humidity and energy items, I want it to save every minute, and I don’t care about if the value has changed or not.

When I go back an look at what the system last night after initially got it to work with the everyMinute strategy, I see updates at 04:00:00, 04:01:00, 04:02:00, and so on. Now I see it like 13:24:00, 13:24:10, 13:24:20, and that is not what I want it to do.

I do have a Sonoff 4CH, but I put my own code on it, and added a DHT22 to it too, it sends the temperature every 30 seconds via MQTT to openHAB is works as a thermostat for a little heating system I made. (which at one point also will be upgraded to be able to cool too)

And now a new problem too
 Grafana just stopped rendering when using the direct image link. If I do it with a browser where I am logged in, it works fine, but if I change to incognito it gives me this error instead.

Udklip.PNG974×160 13.1 KB

Logging in, makes it work again on that browser. Opening a new incognito and tries the link there, again makes it fail to render the image.

290

Not sure what went wrong from Grafana, I copied the JSON for the chart, deleted it, then imported it again, and now it once again works.

291

Free Online Cron Expression Generator and Describer - FreeFormatter.com :wink:

292

Yes, I forgot it had seconds too
 too used to the cron in debian :stuck_out_tongue:

Strategies {
	everyMinute : "0 * * * * ?"
	every15Minutes : "* */15 * * * ?"
	everyHour : "0 0 * * * ?"
	everyDay  : "0 0 0 * * ?"
	default = everyChange
}

Items {
	* : strategy = every15Minutes, restoreOnStartup
	temperature, humidity, energy : strategy = everyMinute
}

However, after fixing that, I have another problem
 temperature, humidity and energy items still does not update every minute.

Number Sonoff_4CH_86_temp	"Temperature [%.2f °C]"	<temperature> 	(temperature)	{mqtt="<[broker:/Sonoff 4CH/DHT22/Temperature:state:default]"}
Number Sonoff_4CH_86_tempAvg	"Temperature [%.2f °C]"	<temperature> 	(temperature)
Number Sonoff_4CH_86_humi	"Humidity [%.1f %%]"		<humidity>	(humidity)	{mqtt="<[broker:/Sonoff 4CH/DHT22/Humidity:state:default]"}
293

Did you try deleting the * line? Probably creates a conflict.

294

No, it is in the example in the wiki too https://github.com/openhab/openhab1-addons/wiki/Persistence and how else would I be sure to get everything into the database if not using * ?

Up until now I always have had problems with restoreOnStartup, but with influxdb it works perfectly.

295

I just tried to remove the * line, temperature and humidity items still didn’t get updated with the everyMinute strategy. Adding everyMinute to the * line, makes it update every minute


I wonder what is wrong with the other line, it is like it does not read it at all

297

Oh, I just realized that your syntax is wrong. If “temperature” is a group, you need to address it with “temperature*”, see https://github.com/openhab/openhab1-addons/wiki/Persistence#configuration

298

For reference: http://www.librenms.org

Is this question related to openHAB?
Without any more details it will be hard to help. Check out the log files of both services to find the reason

299

Updated it to look like this

Strategies {
	everyMinute : "0 * * * * ?"
	every15Minutes : "* */15 * * * ?"
	everyHour : "0 0 * * * ?"
	everyDay  : "0 0 0 * * ?"
	default = everyChange
}

Items {
	* : strategy = restoreOnStartup
	Temperature*, Humidity*, Energy* : strategy = everyMinute
}

and the items

Number Sonoff_4CH_86_temp	"Temperature [%.2f °C]"	<temperature> 	(Temperature)		{mqtt="<[broker:/Sonoff 4CH/DHT22/Temperature:state:default]"}
Number Sonoff_4CH_86_tempAvg	"Temperature [%.2f °C]"	<temperature> 	(Temperature)
Number Sonoff_4CH_86_humi	"Humidity [%.1f %%]"		<humidity>	(Humidity)		{mqtt="<[broker:/Sonoff 4CH/DHT22/Humidity:state:default]"}

I am starting to feel a bit stupid
 It is still not working


300

It is strange but we already realized that the persist file evaluation is not throwing the right amount of errors. I can’t see anything obviously wrong there. Maybe you want to compare with my config


301

I can get it to work if I define the items like this

Sonoff_4CH_86_temp, Sonoff_4CH_86_tempAvg, Sonoff_4CH_86_humi : strategy = everyMinute

But doing it by group does nothing, except the * one.

302

Well are your groups correctly defined? That’s why I like this part of my sitemap.

303

Maybe the problem comes down to my groups being wrong


My items file looks like this

Group  gSensor

Switch Sonoff_4CH_86_1		"Over monitor light"							{mqtt=">[broker:/Sonoff 4CH/gpio/12:command:ON:1],>[broker:/Sonoff 4CH/gpio/12:command:OFF:0]"}
Switch Sonoff_4CH_86_2											{mqtt=">[broker:/Sonoff 4CH/gpio/5:command:ON:1],>[broker:/Sonoff 4CH/gpio/5:command:OFF:0]"}
Switch Sonoff_4CH_86_3											{mqtt=">[broker:/Sonoff 4CH/gpio/4:command:ON:1],>[broker:/Sonoff 4CH/gpio/4:command:OFF:0]"}
Switch Sonoff_4CH_86_4		"HTC One charger"							{mqtt=">[broker:/Sonoff 4CH/gpio/15:command:ON:1],>[broker:/Sonoff 4CH/gpio/15:command:OFF:0]"}
Number Sonoff_4CH_86_temp	"Temperature [%.2f °C]"	<temperature> 	(gSensor)		{mqtt="<[broker:/Sonoff 4CH/DHT22/Temperature:state:default]"}
Number Sonoff_4CH_86_tempAvg	"Temperature [%.2f °C]"	<temperature> 	(gSensor)
Number Sonoff_4CH_86_humi	"Humidity [%.1f %%]"		<humidity>	(gSensor)		{mqtt="<[broker:/Sonoff 4CH/DHT22/Humidity:state:default]"}

And in my sitemap I tried to add this

Group item=gSensor label="All Elementes" icon="house"

And it is empty, so it does look like I didn’t get the group defined properly, but why? I got a group called gSensor, and added the two temperature readings and one humidity to it, or isn’t it done like that?

304

Looks good. Could you restart openHAB? Sometimes it needs that after a lot of file based changes.