You also have
And
If you really want apexchart
1 Like
Sorry for the late reply, but after NYE i have time only now to look into it, unfortunately could not make it working.
- replaced sensor name
- replaced database name and address (do i need to provide passowrd somewhere? influx db is on the same physical machine as HA, running HA as HA OS installation on an SSD)
- modified the original query in the example to fit my needs at least for testing purposes.
All I get is a blank chart with “loading”.
I see some “console.log(start)”, but where do i find these logs?
Thank You!
type: custom:apexcharts-card
graph_span: 7d
header:
show: true
title: ApexCharts-Card
show_states: true
colorize_states: true
series:
- entity: sensor.heating_on_daily
data_generator: |
console.log(start);
var params = new URLSearchParams({
pretty: true,
db: "ha",
q: "SELECT last(\"value\") FROM \"h\" WHERE \"entity_id\"='heating_on_daily' AND time >= '" + start.toISOString() + "' AND time <= '" + end.toISOString() + "' GROUP BY time(60m) FILL(none)"
});
var myInit = { method: 'GET',
headers: {
'Accept': 'application/json',
},
mode: 'cors',
cache: 'default'
};
const request = async () => {
var result = [];
const response = await fetch('http://localhost:8086/query?' + params, myInit)
const json = await response.json();
if (json["results"] && json["results"][0] && json["results"][0]["series"] && json["results"][0]["series"][0] && json["results"][0]["series"][0]["values"]) {
for(var val of json["results"][0]["series"][0]["values"]) {
result.push([new Date(val[0]), val[1]]);
}
} else {
console.log("error: " + json)
}
return result;
}
return request();
I assume you tried the query directly in Influxdb (e.g. via Grafaba) first?
Oh, and I also assume you’re using Influxdb 1.X. 2.X has a different query language (“flex”)
Grafana was a good idea, query wasnt good enough, so i made a simplified that should do the trick for now:
SELECT last("value") FROM "h" WHERE ("entity_id" = 'heating_on_daily') GROUP BY time(1d) fill(none) tz('CET')
which looks like this inside HA:
q: "SELECT last(\"value\") FROM \"h\" WHERE (\"entity_id\" = 'heating_on_daily') GROUP BY time(1d) fill(none) tz('CET')"
it does not have the start/end section of the original PoC work.
Yet, still no results.
I do not know which version of influxdb I have, i got it from addon store inside home assistant (not from HACS), it says 4.3 which is definitely not what probably you are looking for, im sure it supports API interface 1.x and 2.x too since inside Influxdb own interface i can switch between InfluxQL and Flux interface.
The only difference that inside Influxdb I have to speciy the database and the default pocket too.
Tried both variant in Apex, unfortunately no results.
What came in my mind, do i need to add username and password somehow? Or add extra connection inside influxdb?
I’m pretty sure you’re using 2.X, but apparently, there is a “compatibility” layer that allows SQL like queries as well.
Could you show the definition of your “InfluxDB” datasource from Grafana, please
You’re using authentication, so you’ll need to add that to the headers of the request.
See
https://docs.influxdata.com/influxdb/v2.1/reference/api/influxdb-1x/
Looks and sounds like you’re using the InfluxDB addon so therefore you’re using v1.8.10 of InfluxDB since that’s what it was updated to in the latest release of the addon. And yes 1.8 does include Influx 2.0 compatibility endpoints for forward compatibility as noted in their release notes. So it supports InfluxQL fully and has a preliminary, read-only version of the Flux APIs.
History stats can only see as far back as you’ve allowed recorder to keep data. By default recorder purges data that is more then 10 days old. That can be set in config using the purge_keep_days option.
That being said, setting that to years is generally not a good idea as your DB will get way too big. You’re right to look at Influx for that although the new Long-Term Storage features do provide hope that there will be a native solution to this problem at some point without requiring InfluxDB or Grafana.
I think you’re right but just FYI the doc to look at for v1.8 is here. And as it lists here you can either provide a basic auth header or just put the username and password in query string parameters u and p.
Also just as an aside, addons can talk to each other directly using their slug. So in here for example:
You can just put http://a0d7b954-influxdb:8086 as the URL. This way grafana will talk directly to influx without going to your router. Same Home Assistant since that also runs inside the same docker network as the addons and supervisor. This also means that you don’t have to expose the 8086 port on your host since the only things talking to the InfluxDB Addon are local.
Well unless you have an external service which talks to InfluxDB on a different machine in your network. Then you do still have to expose the port on the host.
Either way you shouldn’t use homeassistant.local there. Use either localhost, 127.0.0.1 or a0d7b954-influxdb. homeassistant.local is an mDNS address and I’ve personally found using it outside of a browser can be kind of a crapshoot. I don’t fully understand the issues but I’ll just say in my personal testing integrating all my stuff trying to use the homeassistant.local URL for integrations between systems and services was a bad idea and led to a lot of communication issues. Adding a DNS rewrite for it in my local DNS server helped but honestly it was much simpler to use either the IP or a hostname.
EDIT: Oh hang on a minute. i just realized that JS you wrote here:
That runs in the browser doesn’t it? Can you open up browser dev tools when you see the “Loading…”? What does the network tab say, is it trying to connect to http://localhost:8086 from your browser? Because that’s not going to work, your laptop/phone isn’t running influx.
EDIT AGAIN: Duh the server is python, that is obviously running in the browser. You’ve also got cors on there. So yea there’s a few things you need to look at then:
- You can’t use
localhostthere, that doesn’t make sense. You also might not be able to usehomeassistant.localas that URL only works from inside your network (unless you have no external access to HA). A better option is to set the URL to this:"http://" + window.location.hostname + ":8086?query"since that will use the domain of the window. - Is CORs working? For CORs to work InfluxDB has to return headers which say access from any domain is allowed or access from the domain you are on is allowed. Is that happening?
1 Like
Thanks all the support guys!
However I still could not make it, but I see some improvement, my last version of the Apex Card:
type: 'custom:apexcharts-card'
graph_span: 7d
header:
show: true
title: ApexCharts-Card
show_states: true
colorize_states: true
series:
- entity: sensor.heating_on_testv2
data_generator: |
console.log(start);
var params = new URLSearchParams({
pretty: true,
db: "ha",
q: "SELECT last(\"value\") FROM \"h\" WHERE (\"entity_id\" = 'heating_on_daily') GROUP BY time(1d) fill(none) tz('CET')"
});
var myInit = { method: 'GET',
headers: {
'Accept': 'application/json',
},
mode: 'cors',
cache: 'default'
};
const request = async () => {
var result = [];
const response = await fetch("http://" + window.location.hostname + ":8086?query" + params, myInit)
const json = await response.json();
if (json["results"] && json["results"][0] && json["results"][0]["series"] && json["results"][0]["series"][0] && json["results"][0]["series"][0]["values"]) {
for(var val of json["results"][0]["series"][0]["values"]) {
result.push([new Date(val[0]), val[1]]);
}
} else {
console.log("error: " + json)
}
return result;
}
return request();
So what I have done:
Disabled auth in Influx config, just to make things simpler → Grafana works fine
Replaced homeassistant.local references with http://a0d7b954-influxdb:8086 → Grafana works fine
with const response = await fetch('http://a0d7b954-influxdb:8086/query?' + params, myInit)
with
const response = await fetch("http://" + window.location.hostname + ":8086?query" + params, myInit)
Disabled auth in config to avoid troubles with it, at least for now.
Your last screenshot is just showing the request headers. What does the response say? Or were there any errors in console?
So I’m a bit confused now. I was expecting a JSON response. You’re seeing what looks like the response for loading an panel in the HA frontend. I’m pretty sure that’s not the ajax call at all.
In this screenshot it looks like you have filtered the network calls to ones that contain influx in chrome dev tools. That’s actually not correct, you should type query. Chrome is confusing here, it names the requests based on the last resource path in the URL. So when you hit a URL like http://a0d7b954-influxdb:8086/query the name in that list will be query. And that search box at the top only searches on name so a search for influx won’t show that api call at all.
That being said your screenshot does highlight an issue with the code I shared. I assumed it was always http which was silly of me, if you allow external access it would be https. Please change fetch code to this:
const response = await fetch(window.location.protocol + "//" + window.location.hostname + ":8086?query" + params, myInit)
That should clean up that mixed content error.
Oh wait. Something got screwed up. The fetch code should be this:
const response = await fetch(window.location.protocol + "//" + window.location.hostname + ":8086/query?" + params, myInit)
I copied and pasted what you had and tweaked the protocol without really looking closely at it. You put the question mark in the wrong place. Now we can try it.
Unfortunately still SSL ERROR, if I copy the query from the inspector into a different chrome tab, I also receive it, however at first it looks fine…
https://xxxxxxxxxx:8086/query?pretty=true&db=ha&q=SELECT+last%28%22value%22%29+FROM+%22h%22+WHERE+%28%22entity_id%22+%3D+%27heating_on_daily%27%29+GROUP+BY+time%281d%29+fill%28none%29+tz%28%27CET%27%29
Ah duh, I’m being dumb again. Ok so I assume you have port forwarded 8086 from your router to HA which means https://xxxxxxxxxx:8086 talks directly to your influx add-on. But as you showed in the config above, SSL is disabled for the influx addon so that won’t work. But you also can’t talk to it using HTTP since that’s a mixed content error. So you have to do one of the following:
- Enable HTTPS for the InfluxDB add-on. Note that this will enable it always (LAN and external access) so that might break the dashboard when using the LAN URL (
homeassistant.local) unless you are using https there as well. - Drop the
window.locationstuff and just usehttp://homeassistant.local. Your dashboard won’t work when you are outside your network but should be when on LAN. - Use a reverse proxy like NGinx Proxy Manager so external access to Influx and HA is via HTTPS and internal access is via HTTP instead of port forwarding individual ports. But you’ll need to tweak the fetch logic then based on the subdomain you pick for influx.
Thanks, I will try, but for today need to rest, will start with nginx and reverse proxy, hopefuly that can mask everything local.
Did you solve the problem? I think I’ve also HTTPS problem.
Sorry for the late anser, unfortunately could not solve it, gave up for now as there are other things to take care of, but If i manage to solve it once, i will post the solution.
Retrieving data from influxdb is easy when sticking to the docs.
However, retrieving data only works for a sensor state, not for any history.
I tried the same thing and came to the conclusion that this all does not work, by definition.
Similar issue here where I posted the unfortunate answer.