Ankündigung

**TheMax74** · 08.04.2020, 21:03

Hi Carsten
thanks. You can safely use notepad or wordpad or vs code with github plugin.

**Alloc** · 10.04.2020, 12:08

Hi xrk ,

didn't want to take over the other thread with that topic ... I was curious about your Node-RED setup for Influx/Grafana. Do you just collect specific GAs or do you log every GA to the InfluxDB and just decide on what GAs to show in graphs? Would you mind sharing further details on how you set up all that stuff?

**xrk** · 10.04.2020, 12:24

Hi Chris,
I log every KNX message (directed to a group address (GA)) into InfluxDB. This gives me the opportunity to analyze/visualize all info (using e.g. Grafana) afterwards if desired.

The flow for this is very simple (someone with more experience with Node-Red might surely find a simpler solution, but it works for me very well):

filedata/fetch?id=1491872&d=1586517541&type=thumb
KNX Ultimate node is in Universal mode listening to all GAs, the output has type Write, Input is reacting to GroupValue write & response.

The function node "Fields & Tags" is doing:

Code:

// No need to log Date & Time, in my case in GA 0/0/1&2
if ((msg.topic == "0/0/1") || (msg.topic == "0/0/2")) return null;

// Boolean values cannot be compacted by InfluxDB into lower resolution retention policies (=database tables)
// The reason is that the mean/average computation of boolean results in null value in InfluxDB.
// Thus we turn all booleans into numeric values: false=0, true=100.
// See Post #200 below for further information about InfluxDB retention policies and compacting data
if (typeof msg.payload === 'boolean'){
     if (msg.payload === true){
         msg.payload = 100;
     } else {
          msg.payload = 0;
     }
}

msg.payload = [{
     // First object lists the set of named fields (for further info, see InfluxDB Node Help in Node-Red)
     value: msg.payload,
},
{
     // The second object lists the set of named tags
     source : msg.knx.source,
     dpt: msg.knx.dpt,
     description: msg.devicename,
     event: msg.knx.event
}];
return msg;

and change node "Filter" is configured like this:
filedata/fetch?id=1491873&d=1586517805&type=thumb
This will save to InfluxDB (see InfluxDB help to understand concepts such as measurement, field and tag):

date and time at the time of writing to InfluxDB as timestamp
group address of the KNX message as measurement
the value as the field value
source (physical address of the transmitting KNX node) as the tag value
dpt (KNX datapoint type) as the tag value
group address description as the tag value
event type (GroupValue write, response or read) as the tag value

**Alloc** · 10.04.2020, 12:30

Thanks Risto,

that's really compact

Probably would have just created a new msg object in the function node so you'd not have to add the change node afterwards, but that's really a minor improvement.

I must admit I haven't looked at InfluxDB/Grafana yet, but was there anything "special" for this setup to work or is everything after this Node-RED setup for storing the data a regular setup of the other two tools?

**xrk** · 10.04.2020, 13:13

It works with standard settings out-of-the-box for InfluxDB and Grafana.

I needed some fiddling though for creating the retention policies (and the required continuous query) in InfluxDB, until I was satisfied. This is what I use presently (note the unusual infinite retention policy for the high-res "input" data):

Code:

CREATE database "db_knx"
USE db_knx
CREATE retention policy "rp_inf_highres" ON "db_knx" duration INF replication 1 DEFAULT
CREATE retention policy "rp_inf_lowres" ON "db_knx" duration INF replication 1

(the next command should be entered all in one line; here only for improved readability I split it into 5 lines)
CREATE continuous query "cq_1day" ON "db_knx"
BEGIN
  SELECT mean(value) AS value, min(value) AS min, max(value) AS max, stddev(value) AS stddev INTO "db_knx"."rp_inf_lowres".:MEASUREMENT
  FROM (select mean(value) AS value FROM db_knx.rp_inf_highres./.*/ GROUP BY time(1s) fill(previous)) GROUP BY time(1d),*
END

The lowres version of the data is needed for plotting something over a very long period (such as a year of data or so), as with highres this could otherwise be slow due to possibly too many datapoints. The lowres aggregates the values over 1 day, but also retains true min, max and stddev on input data that otherwise would not be included anymore in lowres data. The asterisks in the statement tell the query to go over each measurement series one by one (meaning each KNX group address separately).

Here's one important point to understand with InfluxDB, especially when used with KNX data. InfluxDB mean value computation expects data to have a fixed time period in the database, which is not the case with KNX (consider for example a value in KNX that gets transmitted on an event, e.g. on change). The reason for the continuous query to first group by 1 second and then fill is to avoid this false mean/average value in computed lowres data. The problem with irregular time series data is nicely explained in this blog post at InfluxDB website.

Do note that this continuous query solution is computationally quite expensive (every MEASUREMENT (=KNX group address) is expanded to 24*3600=86400 values per day and then the mean is computed), but is my way to produce more correct "average" values in the lowres data. As a reference, for example, on my Xeon server it takes roughly a minute to calculate the lowres data for all the data points across all GAs of the last 24h (the cq_1day runs once a day).

**sinn3r** · 11.04.2020, 08:57

Hey xrk

nice, this works fine. I was using some linux command line tools to do this job, but i'll switch it over to nodered now.
I'll leave it running a few days and check if lowres is filled.

I have a few questions though: isn't the highres retention exactly the same as the autogen retention influxdb creates on its own?
Does Grafana autoswitch to the lowres retention when filtering longer date ranges?
Wouldn't it make sense to limit the retention time of the highres policy, as the lowres has all data for long date ranges?
How can I get the runtime of cq_1day?

The influxDB node logs some errors with different GAs, for example:
A 400 Bad Request error occurred: {"error":"unable to parse '0/1/15,source=1.1.100,dpt=10.001,description=(Zentral-\u003eWetter)\\ Wetterstation.Messwert\\ GPS.Uhrzeit,event=GroupValue_Write value=Sat Apr 11 2020 09:44:16 GMT+0200 (GMT+02:00)': invalid boolean"}

Thanks!
Marc

**xrk** · 11.04.2020, 09:23

Hi Marc,