Influxdb query result into item

Add-ons Persistence Services
,
1

I am trying to update an item using the result of an influxdb query. The query doesn’t appear to be one of the standard ones as I’m trying to get the total on time for a Switch over the last day/week using the integral function:

SELECT Integral(value,1h) AS "On_time" FROM "openhab_db"."autogen"."Oculus" WHERE time >= now()-7d

The query works fine but I don’t know how to get the result to update an item.
I’m aware that I could use the Influxdb REST API directly but that would require authentication so I’m wondering if there is a simpler way of calling the query within OpenHAB.

2

I think I did it with the http item (http1- I haven’t converted it to http v2 yet). It’s not very elegant and if there’s no data it throws up errors in the log as no json item there. A rule would be cleaner.

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

It becomes a real issue with escaping the queries.

3

Thanks for that, I’ll give it a try with the OH3 version of the binding. Good to see it can get queried directly without needing to first request a token.

4

Wow, you weren’t wrong - escaping the query was a nightmare! I had some help from my Influxdb client (Chronograph), which formatted part of the url but the HTTP binding claimed some characters (> and ") are invalid so I had to replace them with their % equivalents. I also learnt that you need to format the already translated characters with double percentage signs so a url with %20 in it, needs %%20 in the thing definition.

Anyway, it’s working now:


Thing http:url:oculus7d "Oculus 7 day on time" [ baseURL="http://192.168.x.x:8086/query?q=SELECT%%20Integral%%28value%%2C1h%%29%%20FROM%%20%%22openhab_db%%22.%$
        Channels:
        Type number : Oculus7dOn [ stateTransformation="JSONPATH:$.results[0].series[0].values[0].[1]", mode="READONLY" ]
        }

After all that it looks like the query isn’t working as I need it to but that’s a different problem.

Thanks

1 Like
5

Yeah i kept thinking it might be easier to get influx to mqtt and then just make an openhab mqtt item to get the information.

But I could never find a decent bridge to do it.