Populate an item from influx

[psyciknz]

I have some data going into influx from an external source, I’d like to get it into an item to show in openhab.

It’s in a db called influximported in a table called 2degrees_usage. I thought about using the http binding to pull a query from influx.

String    internet_usage  "Usage [%s]"            (g_internet_usage)      { http="<[http://influxdb.andc.nz:8096/query?db=influximporter&q=select+value+from+%222degrees_usage%22+where+time+%3E+now%28%29+-+1h:60000:default]"}

The query when yo utake out th eurlencoding is select value from "2degrees_usage" where time > now() - 1h

Teh full curl command is

curl http://influxdb.andc.nz:8096/query?db=influximporter --data-urlencode 'q=select value from "2degrees_usage" where time > now() - 1h'

or encoded is

 curl "http://influxdb.andc.nz:8096/query?db=influximporter&q=select+value+from+%222degrees_usage%22+where+time+%3E+now%28%29+-+1h"

both these queries above return:

{"results":[{"statement_id":0,"series":[{"name":"2degrees_usage","columns":["time","value"],"values":[["2020-04-04T19:56:53.786806533Z",482385.92]]}]}]}

but with that item cofigured above I get a

2020-04-05 07:51:07.441 [ERROR] [b.core.service.AbstractActiveService] - Error while executing background thread HTTP Refresh Service
java.util.UnknownFormatConversionException: Conversion = '2'
        at java.util.Formatter.checkText(Formatter.java:2579) [?:?]
        at java.util.Formatter.parse(Formatter.java:2555) [?:?]
        at java.util.Formatter.format(Formatter.java:2501) [?:?]
        at java.util.Formatter.format(Formatter.java:2455) [?:?]
        at java.lang.String.format(String.java:2940) [?:?]
        at org.openhab.binding.http.internal.HttpBinding.execute(HttpBinding.java:144) [217:org.openhab.binding.http:1.12.0]
        at org.openhab.core.binding.AbstractActiveBinding$BindingActiveService.execute(AbstractActiveBinding.java:144) [223:org.openhab.core.compat1x:2.3.0]
        at org.openhab.core.service.AbstractActiveService$RefreshThread.run(AbstractActiveService.java:166) [223:org.openhab.core.compat1x:2.3.0]

Error

Any thought, or is there another way?

[rossko57]

I think you have to escape the % in your URL, as the error message hints it is misinterpreting those as meant for string substitution/formatting.

[psyciknz]

What do I have to escape % as?

[rossko57]

If you look at other forum posts about escaping things in http action urls … the % is an escape character here.
So you double it %22 -> %%22

[psyciknz]

Ok so I 've got a little further:

String  internet_usage  "Usage [%s]"            (g_internet_usage)      { http="<[http://influxdb.andc.nz:8096/query?db=influximporter&q=select+value+from+%%222degrees_usage%%22+where+time+%%3E+now%%28%%29+-+1h%%27:6000:JSONPATH($.results[0])]"}

Just trying to get anything out of it. This one is giving me a

2020-04-06 07:05:56.341 [WARN ] [ab.binding.http.internal.HttpBinding] - Transformation 'JSONPATH($.results[0])' threw an exception. [response={"error":"error parsing query: found BADSTRING, expected ; at line 1, char 58"}
]
org.openhab.core.transform.TransformationException: Invalid path '$.results[0]' in '{"error":"error parsing query: found BADSTRING, expected ; at line 1, char 58"}
'
        at org.openhab.core.transform.TransformationHelper$TransformationServiceDelegate.transform(TransformationHelper.java:67) [223:org.openhab.core.compat1x:2.3.0]
        at org.openhab.binding.http.internal.HttpBinding.execute(HttpBinding.java:194) [217:org.openhab.binding.http:1.12.0]
        at org.openhab.core.binding.AbstractActiveBinding$BindingActiveService.execute(AbstractActiveBinding.java:144) [223:org.openhab.core.compat1x:2.3.0]
        at org.openhab.core.service.AbstractActiveService$RefreshThread.run(AbstractActiveService.java:166) [223:org.openhab.core.compat1x:2.3.0]

But who knows what the url is being given to influx now with all the escaping. I this it must be getting an error returned to it, as the json requires the following path to get the value I want:

$.results[0].series[0].values[0][1]

[jswim788 (John Schmitz)]

This may be a bit of a digression, but perhaps it would be easier to use the sendHttpGetRequest action in a rule instead? I use this for a post request to InfluxDB and it works well without the escaping. But maybe it is harder with a get compared to a post.

InfluxDB+Grafana persistence and graphing

The persistence for InfluxDB does impose some limitations: you can’t use the tags and the multiple values and different retention policies in InfluxDB. For example, you could have a measurement “Temperature” and the tags “Inside” and “Outside”. Or maybe you want some measurements saved forever and others for much less time. You can use continuous queries to down sample, but it doesn’t cover every case. Also, you can have a measurement “SensorValues” with values such as Temperature and Humidi…

[psyciknz]

I guess as long as I can seperate the request and the data from response, then yes that’s a good idea.

[rossko57]

@psyciknz

I this it must be getting an error returned to it

Yes, it’s right there in the message - this comes from influx
{"error":"error parsing query: found BADSTRING, expected ; at line 1, char 58"}
I reckon char 58 in the query is around that +-+1h%%27 area

What might help is looking in influx log to see what it received.

@jswim788

But maybe it is harder with a get compared to a post.

I think that’s probably true, there’s a bunch of things processing the URL

[psyciknz]

Yeah I wasn’t sure where that bit came from in the url encoding. I thnk the encoding I used converted space to + and maybe it’s needs the - escaped as well.

[rossko57]

What’s the %27 for? Doesn’t seem to represent anything in your original curl.

[psyciknz]

@psyciknz

27

I think I escaped the back mark ’ - I’ve removed it. …

Finalyl got it. Full item here:

>
String  internet_usage  "Usage [%s]"            (g_internet_usage)      { http="<[http://influxdb.andc.nz:8096/query?db=influximporter&q=select+value+from+%%222degrees_usage%%22+where+time+%%3E+now%%28%%29+-+1h:6000:JSONPATH($.results[0].series[0].values[0][1])]"}

[psyciknz]

Once you figure out the format of the query, there’s probably a lot of data you might be able to pull in. For this one I’m mostly wanting to alert that the entry hasn’t updated as the grafana alerts aren’t great for lack of data.