InfluxDB+Grafana persistence and graphing

I’m not sure at all. Could you please open an issue for this feature request.

Interesting…

I ran the query through the REST API (apparently the Web UI is deprecated and broken) and I’m getting the exact same error.

curl -GET 'http://openhab:password@localhost:8086/query?db=openhab_db' --data-urlencode 'q=select value from default.Weather_Temperature where time > 1479193200s limit 2147483647'
{"error":"error parsing query: found DEFAULT, expected identifier at line 1, char 19"}

If I modify the query to remove the “default.” I get expected results (assuming I’m reading the meaning of the original query correctly, I’m not certain I know what the limit does).

curl -GET 'http://openhab:password@localhost:8086/query?db=openhab_db' --data-urlencode 'q=select value from default.Weather_Temperature where time > 1479193200s limit 2147483647'

Could test this query (limit removed from the query to default.Weather_Temperature):

curl -GET 'http://openhab:password@localhost:8086/query?db=openhab_db' --data-urlencode 'q=select value from default.Weather_Temperature where time > 1479193200s'
curl -GET 'http://openhab:Hes10d27@localhost:8086/query?db=openhab_db' --data-urlencode 'q=select value from default.Weather_Temperature where time > 1479193200s'
{"error":"error parsing query: found DEFAULT, expected identifier at line 1, char 19"}

And again, if I remove the “.default” I get results.

Just a wild guess: there is no retention policy “default” for “Weather_Temperature”. Can you do a:

SHOW RETENTION POLICIES ON Weather_Temperature

Hmmm. When I run the query like that I get

{"results":[{"error":"database not found: Weather_Temperature"}]}

When I run it using openhab_db instead of Weather_Temperature I get

curl -GET 'http://openhab:Hes10d27@localhost:8086/query?db=openhab_db&pretty=true' --data-urlencode 'q=SHOW RETENTION POLICIES ON openhab_db'
{
    "results": [
        {
            "series": [
                {
                    "columns": [
                        "name",
                        "duration",
                        "shardGroupDuration",
                        "replicaN",
                        "default"
                    ],
                    "values": [
                        [
                            "default",
                            "0s",
                            "168h0m0s",
                            1,
                            true
                        ]
                    ]
                }
            ]
        }
    ]
}

Based on the InfluxDB docs, the ON clause only appears to work on a DB, not a field. But based on the previous queries I know for sure that Weather_Temperature is in openhab_db and my reading of the Retention Policy docs is that one can only apply a policy to an entire DB, not individual fields.

In Grafana, in order to chart this value the query is:

which collapses to:

I got the same problem with my setup. I need to use RetentionPolicie=default in my influxDB v1.0 setup. without i get all sorts of errors. But i can’t get measurements out the DB with the REST api (error parsing query: found DEFAULT, expected identifier at line 1, char 19) Do you already know how to fix the problem?

Same here! :wink:

Not yet. I’ve temporarily worked around it for now by keeping track of the info I need in vars and vals. Charting and saving the data works fine.

I’m pretty much using rrd4j everywhere else for this sort of thing.

I suspect that if you created a non-default retention policy it will work. See @opus’s posts above for details on how to do that and @ThomDietrich posted how to move your data from your current one to the new one so you won’t lose anything. I’ve just not got around to it yet.

Since the queries build by the openHAB REST API do reflect the retention-policy now, I’m questioning the reason for using a DEFAULT retention policy. You need to set the correct name anyway! But I’m still a newbie on this topic.

Because that is what the default RP was called when I initially set up InfluxDB. For those who followed this tutorial when it was first posted, we are stuck saving all our stuff to “default”. It will require a significant amount of work to create and migrate everything to new RP and update Grafana to pull from the correct RP.

2 Likes

If you change default to “default” it works

curl -GET 'http://openhab:password@localhost:8086/query?db=openhab_db' --data-urlencode 'q=select value from "default".Weather_Temperature where time > 1479193200s'

im guessing the name default RP in influx is handled specially to require “” since its name and has nothing to do which is default RP in influx.

1 Like

Unfortunately changing it to "default" in influxdb.cfg does not work. I get the following error for each commit.

2016-11-28 12:08:00.048 [ERROR] [org.influxdb.impl.BatchProcessor    ] - Batch could not be sent. Data will be lost
java.lang.RuntimeException: {"error":"retention policy not found: \"default\""}
1 Like

@techmike there was already a similar issue I added the idea to. https://github.com/openhab/openhab/issues/4830#issuecomment-263360787

Yes one will have to alter the plugins behaivor regarding “default” name as is has to include ""
But this seems to work for all names so “autogen”.temperature is also ok!

1 Like

@theo, @fremel75, @opus

I’ve submitted an issue on github. Based on @fremel75’s latest, it sounds like the fix is as easy as just adding quotes around the RP name in queries.

1 Like

I got rid of the scrollbar in Chrome by adding scrolling="no" to the iframe tag. This is a HTML4 way of doing things. Firefox can do it using CSS but this is not in the HTML5 spec. See stackoverflow for more info.

The repo for armv6 is now up ( https://dl.bintray.com/fg2it/deb-rpi-1b ) and populated with v4.0.1. The PR is done.

1 Like

I’ve created a JavaScript library that uses the openHAB 2 REST API and server side events (SSE) to resolve items values to Grafana panel parameters. I use it to reduce the number of Webviews in my sitemap. Normally a lot of Webviews are required for user customizable parameters, i.e. using visibility[..] expressions.

openHAB always loads these Webviews, eventhough they are not visible. As a result page load times suffered, especially in combination with my RPi3 and smartphone.

The code including documentation and an example demo is available at:

The following sitemap snippet shows how this library makes it possible to customize all 5 Grafana panel parameters with a single Webview:

Frame label="Combo 2" {
	Switch item=Combo2_Panel mappings=[HUMIDITY="Humidity", TEMPERATURE="Temperature"]
	Switch item=Combo2_Dashboard mappings=[alpha="Alpha", beta="Beta", gamma="Gamma", delta="Delta"]
	Selection item=Combo2_From mappings=[HOUR="now-1h", DAY="now-1d", WEEK="now-7d", MONTH="now-30d", YEAR="now-1y"]
	Selection item=Combo2_To mappings=[NOW="now", HOUR="now-1h", DAY="now-1d", WEEK="now-7d", MONTH="now-30d", YEAR="now-1y"]
	Switch item=Combo2_Theme mappings=[light="Light", dark="Dark"]
	Webview url="/static/demo-combo2.html?dashboardItem=Combo2_Dashboard&fromItem=Combo2_From&toItem=Combo2_To&panelItem=Combo2_Panel&themeItem=Combo2_Theme" height=9
}

So far I’ve tested it with the Basic UI on Chrome (Desktop, Mobile) and Firefox (Desktop). A lot of the library can probably also be reused when you want to use the REST API and SSE in combination with a non-Grafana HTML Webview or web page.

14 Likes

@wborn Very very nice. I’ll have a play later this week, but the examples on your git page look great.

1 Like