Using HA or HACS graphs w/ InfluxDB data in HA?

Koying - someone is a very smart lad !
I had to wipe browser cache and it worked.
I can’t beleive it is working the way I want. Its been my biggest problem with using HA and apex cards.
Here is my settings for anyone else

type: 'custom:apexcharts-card'
header:
  show: true
  title: Solar Monthly Production
  show_states: false
  colorize_states: true
apex_config:
  chart:
    height: 270
    extend_to_end: false
  yaxis:
    min: 0
    max: 800
  stroke:
    show: true
    width: 1
  legend:
    show: true
graph_span: 12 month
span:
  end: month
series:
  - entity: sensor.solar_monthly_influxdb
    type: column
    name: Monthly Production
    color: green
    extend_to_end: false
    show:
      datalabels: true
    group_by:
      duration: 1month
    data_generator: |
      console.log(start);
      var params = new URLSearchParams({
          db: "sensors",
          q: "SELECT \"value\" FROM \"solar\" WHERE \"entity_id\" = 'month' AND time < now()"
          });

      var myInit = { method: 'GET',
                      headers: {
                          'Accept': 'application/json',
                      },
                      mode: 'cors',
                      cache: 'default' 
      };

      const request = async () => {
          var result = [];
          const response = await fetch('http://10.0.0.10: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)
          }
          console.log("return: " + json) 
          return result;
      }
      return request();
update_interval: 1d

apex-working

Don’t know how much the sensor config makes, but below is what I have in configuration.yaml

  - platform: influxdb
    host: 127.0.0.1
    queries:
      - name: solar_monthly_influxdb
        database: sensors
        field: value
        measurement: solar
        where: '"entity_id" = ''month'' AND time < now()'
        unit_of_measurement: kWh

Thank you so much Chris

3 Likes

Glad it worked :slight_smile:
You can remove those loggings, to not flood your browser.

The needed change to apex-card has been incorporated in release 1.9.0.

1 Like

Thanks for putting in this work!

I’ve gone a different approach - I’m using PyScript and this script to pull the data in as a sensor (excluding them from my recorder & influx databases)

Updates daily via an Automation:

1 Like

@bacco007 @koying

This is amazing! Do you guys have anything like this for influxdb 2?
They are making it really hard to output json with flux

I havent made the jump to InfluxDB 2 yet - I was considering it, but then discovered my Influx database has become a bit unwieldy in size and I want to try and strip out some of the crap before converting first.

1 Like

Everything is hard with flux :wink:
I think I’ll never take the plunge to 2.x (unless they bring back influxql). Too hard for me to wrap my head around it…

2 Likes

THANK YOU @koying!

I hate grafana and influxdb2 which are clunky and counterintuitive. Now using influxdb 1.8.10 :innocent:

compressed version

let params = new URLSearchParams({
  db: "home_assistant",
  q: 'SELECT "value" FROM "kg" WHERE "entity_id" = \'mattias_weight\' AND time < now()'
}),
request = async () => {
  let a = [],
    r = await fetch("http://192.168.1.241:8086/query?" + params),
    j = await r.json();
  for (let r of j.results[0].series[0].values) a.push([new Date(r[0]), r[1]]);
  return a
};
return request()

I really like the approach of bacco007! If you like pls have a look at
GitHub - mf42/influxdb2-to-entity where I tried to implement this is for InfluxDB 2 (and AppDaemon).
This is my first public GitHub repo, so please don’t be too hard…

1 Like

I’m not able to get this working. Just stuck with “loading…”. Would somebody be able to pin point where it goes wrong.

I’ve installed the patched version of ApexChart Card (https://nextcloud.semperpax.be/s/D3YLw2MmPLwqoHK/download/apexcharts-card.js) .

In chart

type: custom:apexcharts-card
header:
show: true
title: ApexCharts-Card
show_states: true
colorize_states: true
series:

  • entity: sensor.vallox_outdoor_air
    data_generator: |
    console.log(start);
    var params = new URLSearchParams({
    pretty: true,
    db: “homeassistant”,
    q: “SELECT mean("value") FROM "autogen"."°C" WHERE ("entity_id" = ‘vallox_outdoor_air’) GROUP BY time(1h) fill(none)”
    });

    var myInit = { method: ‘GET’,
    headers: {
    ‘Accept’: ‘application/json’,
    },
    mode: ‘cors’,
    cache: ‘default’
    };

    const request = async () => {
    var result = ;
    const response = await fetch(‘http://192.168.1.128: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();

The query it generates below and if paste that to the browser it fetches data from Influx correctly.

http://192.168.1.128:8086/query?pretty=true&db=homeassistant&q=SELECT+mean("value")+FROM+"autogen"."°C"+WHERE+("entity_id"+%3D+'vallox_outdoor_air')+GROUP+BY+time(1h)+fill(none)

In the browser console I get below error:

apexcharts-card.js?v=1.10.0:1

   apexcharts-card:  Error: TypeError: Failed to fetch in 'console.log(start);

var params = new URLSearchParams({
pretty: true,
db: “homeassistant”,
…’
at request (eval at _generateData (apexcharts-card.js?v=1.10.0:829:41195), :20:28)
at eval (eval at _generateData (apexcharts-card.js?v=1.10.0:829:41195), :31:8)
at Ba._generateData (apexcharts-card.js?v=1.10.0:829:41296)
at Ba._updateHistory (apexcharts-card.js?v=1.10.0:829:38680)
at apexcharts-card.js?v=1.10.0:887:3507
at Array.map ()
at er._updateData (apexcharts-card.js?v=1.10.0:887:3462)
at er._firstDataLoad (apexcharts-card.js?v=1.10.0:829:72578)
at er._initialLoad (apexcharts-card.js?v=1.10.0:887:3152)

  • The patched apexchart is not necessary (I removed the link to avoid further confusion)
  • Please format your code and/or check the indentation of data_generator

Hi there. Is it possible to read data from InfluxDB2 via data_generator?
I would like to visualize monthly energy consumption over a period of one year.
Or an example reading data with influxdb2-to-entity would be nice. What do I need for that? AppDaemon and Pyscript?

Hi, I’d like to do the same. Does anybody help you with?
My goal is to visualize monthly energy consumption over a period of one year.

Asked a question here, maybe someone here can solve it too.

Do i have to add a login to the url to the database ?
If i go in my browser to the url, i have to login

For anyone who is having problems with displaying data in apexcharts from influxDB… here is a basic setup with all instructions I sent Kneh… so maybe it will help others.

I have done a fresh install of home assistant 2023.3 on a raspberry pi 4B running the latest version of piOS, and installed influxdb.

In hosts file on the RPI I have…

127.0.0.1	localhost pi2.intranet.lan pi2
::1		localhost ip6-localhost ip6-loopback
ff02::1		ip6-allnodes
ff02::2		ip6-allrouters

127.0.1.1	pi2.intranet.lan pi2

This is why I have pi2 in the following line in apexcharts, but you can put 127.0.0.1, or IP address of that PC (eg 10.0.0.5) if running on local PC, or if influxdb is running on another PC the the IP address of the other PC.

const response = await fetch('http://pi2:8086/query?' + params, myInit)

In configuration.yaml I have the following entries.

influxdb:
  host: 127.0.0.1
  database: sensors
  default_measurement: value
  measurement_attr: entity_id
  max_retries: 3
  exclude:
    entity_globs: "*"

sensor:
  #INFLUX DATABASE READ
  - platform: template
    sensors:
      influxdb_read:
        friendly_name: "Influx DB Read"
        value_template: ""

I have a database called sensors with the following data in influxdb.

root@pi2:~# influx -database sensors -execute "SELECT * FROM energy"
name: energy
time                entity_id         value
----                ---------         -----
1678024799000000000 grid_export       8.4
1678024799000000000 grid_import       1.59
1678024799000000000 solar             9.69
1678111199000000000 grid_export       10.39
1678111199000000000 grid_import       1.36
1678111199000000000 solar             11.96
1678197599000000000 grid_export       18.32
1678197599000000000 grid_import       1.49
1678197599000000000 solar             22.56
1678283999000000000 grid_export       16.8
1678283999000000000 grid_import       1.4
1678283999000000000 solar             21.17
1678370399000000000 grid_export       23.53
1678370399000000000 grid_import       1.03
1678370399000000000 solar             26.56
1678456799000000000 grid_export       24.36
1678456799000000000 grid_import       1.04
1678456799000000000 solar             28.53
1678543199000000000 grid_export       25.48
1678543199000000000 grid_import       0.97
1678543199000000000 solar             28.22
1678629599000000000 grid_export       23.39
1678629599000000000 grid_import       1.11
1678629599000000000 solar             26.33
1678715999000000000 grid_export       23.13
1678715999000000000 grid_import       1.05
1678715999000000000 solar             26.14
1678802399000000000 grid_export       22.65
1678802399000000000 grid_import       1.16
1678802399000000000 solar             25.57
1678888799000000000 grid_export       23.93
1678888799000000000 grid_import       0.93
1678888799000000000 solar             27.07
1678975199000000000 grid_export       7.33
1678975199000000000 grid_import       0.47
1678975199000000000 solar             9.22
1680271199000000000 grid_export_month 274.25
1680271199000000000 grid_import_month 15.01
1680271199000000000 solar_month       315.69
1704031199000000000 grid_export_year  274.25
1704031199000000000 grid_import_year  15.01
1704031199000000000 solar_year        315.69
root@pi2:~#

I only want to display the solar output values in apexcharts so I would execute…

root@pi2:~# influx -database sensors -execute "SELECT value FROM energy WHERE entity_id='solar'"
name: energy
time                value
----                -----
1678024799000000000 9.69
1678111199000000000 11.96
1678197599000000000 22.56
1678283999000000000 21.17
1678370399000000000 26.56
1678456799000000000 28.53
1678543199000000000 28.22
1678629599000000000 26.33
1678715999000000000 26.14
1678802399000000000 25.57
1678888799000000000 27.07
1678975199000000000 9.22
root@pi2:~#

This equals the query in apexcharts…

q: "SELECT \"value\" FROM \"energy\" WHERE \"entity_id\" = 'solar'"

So now we know we have the data in influxdb that we want to display and the correct query to get the data out of the influxdb.

I add the apexchart card to a dashboard - in my case ENERGY, and add the following code in the card configuration.


type: custom:apexcharts-card
header:
  show: true
  title: Solar Production - Daily
  show_states: false
  colorize_states: true
apex_config:
  chart:
    height: 440
    extend_to: now
  yaxis:
    min: 0
    max: 30
    forceNiceScale: true
  stroke:
    show: true
    width: 2
  legend:
    show: true
  experimental:
    brush: true
graph_span: 1month
span:
  end: day
series:
  - entity: sensor.influxdb_read
    type: column
    name: Daily Production
    color: '#acd373'
    extend_to: false
    show:
      in_brush: true
    group_by:
      duration: 24hour
      func: first
    data_generator: |
      var params = new URLSearchParams({
          db: "sensors",
          q: "SELECT \"value\" FROM \"energy\" WHERE \"entity_id\" = 'solar'"
          });

      var myInit = { method: 'GET',
                      headers: {
                          'Accept': 'application/json',
                      },
                      mode: 'cors',
                      cache: 'default' 
      };

      const request = async () => {
          var result = [];
          const response = await fetch('http://pi2: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]]);
              }
          } 
          return result;
      }
      return request();
update_interval: 5m

This is what the data looks like in apexcharts.

Thats all that is needed.

Does this work for someone using the influxdb addon, i can’t get it working.

i’m also using influxdb integration, and i get error message
InfluxDB database is not accessible due to '401:
when i set up the sensor
username and password aren’t allowed as arguments, so i don’t know to make it work.
did you find the solution @Kneh ?

Have you solved the authentication problem?