Openremote - Knx - Energy consumption

I have the intention to try to record and monitor the energy used (electricity consumption) on my Knx installation.
Is there a way to record and display in open remote for each switch/sensor the time spent between 2 changes of states…

In others words, I would like to record for each point of my KNX installation, the time spent between the on state (I switch on a light) and the off state (when I switch off the light).

I remember have seen this kind of rules in the past but I was not able to find it back… Any ideas ?

Hi

I use a really simple rule to write the value of any sensor to a tiny shell script that appends text files.

I use CanvasJS to turn the resulting CSV files into graphs.

Would this be what you're looking for?

Have you looked at EmonCMS integration? https://github.com/openremote/Documentation/wiki/Graphs-with-EmonCMS

Kind regards,
Michal Rutka

Hi

Yes, this the kind of solution if I can record the time of the event and the event

Michal,

Your solution is a little bit more sophisticated and in my case, I will be only able to display the total energy used by the lamps installed otherwise I need to display more than 60 plots on a graph !

Could you please describe a little bit more about this rule and the shell script ?

Hello Olivier,

Sorry it has taken me a few days to reply, I’ve been busy working on a client’s project.

I’m really hoping that you can get up and running with this simple CanvasJS based solution really quickly.

I’ll assume you are running a Linux based OS for your controller… If you are running a Windows based system then you will have to modify the csvmaker.sh file to turn it into a compatible BATCH (.bat) file. I’m sure it can be done, but I don’t know how. Maybe someone else can advise?

There are a few steps to follow to get this working.

  • You need a catch-all command in OpenRemote that a rule can send all the harvested data to, which in turn will pass the data to the Linux script file for conversion into appended csv files.
    (For some reason I needed to map that command to a dummy custom sensor before it worked)

  • Sensors that produce numeric values, that have been named with the suffix .graph

    Custom sensors can be used to map text entries to numeric values.
    For example, I have Velbus devices that produce text outputs like “pressed” & “released”
    I create custom sensors that map these to values.
    10 = pressed
    0 = released

  • A rule to catch all sensors with .graph at the end and push the data to the OpenRemote command.

First of all, I suggest that you download and unzip the this file into your webapps folder and test that you can access the graphs that it will create,

your finished webapps folder should have :-

controller

webconsole

jscanvas

Using a web browser, navigate to " http://{controller address}:8688/jscanvas "

You should see a simple 2 frame web page with a selection of graphs, produced from the CSV data. Similar to the PDF file attached,.

Once you have confirmed you can access the graphs, double check that the two script files within your jscanvas folder have their executable bits set.

chmod +x csvmaker.sh

``

chmod +x zipcsv.sh

``

you can test the csvmaker.sh script works correctly by using this command line instruction

./csvmaker.sh --value=12345 --file=test

``

You should now have a file in the csv folder called test.csv with a single line in it that has the time and date you actioned the script, a comma and the value 12345

Now copy the jscanvas_v1.drl file into your rules folder, so that when your controller restarts it will add the rule to the knowledge base.

…/…/webapps/controller/rules

``

The next part is to get OpenRemote to pass data.

Please create a command in designer that looks like this :-

Command name :-

csvmaker

``

Protocol :-

Shell execution protocol

``

path :-

#### Note that the path must be the exact path from root to the csvmaker.sh script file.

#### WITHOUT a preceding "."

#### Your path may differ from what is shown here

/opt/OpenRemote-Controller/webapps/jscanvas/csvmaker.sh

``

command :-

–value=${param}

``

Polling Interval ;- BLANK

Regular Expression :- BLANK

Sensor name :- (I found I have to create a custom sensor for this to work, which I called “Dummy”)

Dummy

``

Just because I did, please create a custom sensor names Dummy and link it to the csvmaker command that you created.

The final step in this process is modifying or creating sensors in your design that have the suffix .graph which the new rule will monitor and pass the values to the csvmaker script.

For some of my applications, this was as easy as renaming the sensor name and adding the suffix.

(Please double check your design UI points to this new sensor name, the name change does not filter to the UI automatically, in some cases this will require you to re-assign any images)

If the sensor is used in a slider, the slider will automatically be remapped to the new name,

Alternatively, create new sensors specifically for the graphing rule.

Once you have created all your sensors, please re-sync your controller to you design and watch for newly created *.graph.csv files in the csv subfolder of jscanvas

It is probably a good idea to clean out the csv files that were created when you unzipped, as it will make life easier when looking for your own data.

That said, I would leave them in there until you are happy that your new graphs are working.

This next step is a bit fiddly and will require some close attention to the coding, however, thanks to a fabulous bit of work by a a chap called Tom VC, it is much easier than when I first started.

There are two things you need to do.

First you need to edit the graphs.js file so that graphs are created using your new data.

Each line in the top part of graphs.js holds everything you need for a each graph,

Tom created a three section graph, if you only wish to show one bit of data per graph, simple replace the 2 other components with csv/dummy.csv

As shown in the last graphs in this file…

$(document).ready(function () {

/*

Fill the array with all the graph info:

Title, current temp csv, target temp graph, relay graph, number of ms you want to show, name of the graph (for html file), zoom

1day=86400000 ms 7days=604800000 ms 30days=2592000000 ms

*/

var graphData=[

/Master Bed LX 36 hours/ [“Master Bedroom Lights - Previous 36 hours”, “csv/dummy.csv”, “csv/master_mainlx.graph.csv”, “csv/dummy.csv”, 129600000, “MASTERLX”],

/Master Bed LX 30days/ [“Master Bedroom Lights - last 30 days”, “csv/dummy.csv”, “csv/master_mainlx.graph.csv”, “csv/dummy.csv”, 2592000000, “MASTERLX-30”],

/Master Bedroom 36 hours/ [“Master Bedroom - Previous 36 hours”, “csv/master_bed_current.graph.csv”, “csv/master_bed_target.graph.csv”, “csv/MB_heater.graph.csv”, 129600000, “MASTER”],

/Master Bedroom 30days/ [“Master Bedroom - last 30 days”, “csv/master_bed_current.graph.csv”, “csv/master_bed_target.graph.csv”, “csv/MB_heater.graph.csv”, 2592000000, “MASTER-30”],

/Mid bedroom 36 hours/ [“Middle bedroom - Previous 36 hours”, “csv/midbed_current.graph.csv”, “csv/midbed_target.graph.csv”, “csv/midbedroom-heater.graph.csv”, 129600000, “MID”],

/Mid bedroom 30 days/ [“Middle bedroom - last 30 days”, “csv/midbed_current.graph.csv”, “csv/midbed_target.graph.csv”, “csv/midbedroom-heater.graph.csv”, 2592000000, “MID-30”],

/Back bed 36 hours/ [“Back bedroom - Previous 36 hours”, “csv/backbed_current.graph.csv”, “csv/backbed_target.graph.csv”, “csv/back-bed-heater.graph.csv”, 129600000, “BACKBED”],

/Back Bed 30 days/ [“Back bedroom - last 30 days”, “csv/backbed_current.graph.csv”, “csv/backbed_target.graph.csv”, “csv/back-bed-heater.graph.csv”, 2592000000, “BACKBED-30”],

/Bath 36 hours/ [“Bathroom - Previous 36 hours”, “csv/bathroom-current.graph.csv”, “csv/bathroom-target.graph.csv”, “csv/bathroom-heater.graph.csv”, 129600000, “BATH”],

/Bath 30days/ [“Bathroom - last 30 days”, “csv/bathroom-current.graph.csv”, “csv/bathroom-target.graph.csv”, “csv/bathroom-heater.graph.csv”, 2592000000, “BATH-30”],

/Landing 36 hours/ [“Landing - Previous 36 hours”, “csv/landing-current.graph.csv”, “csv/landing-target.graph.csv”, “csv/landing-heater.graph.csv”, 129600000, “LAND”],

/Landing 30days/ [“Landing - last 30 days”, “csv/landing-current.graph.csv”, “csv/landing-target.graph.csv”, “csv/landing-heater.graph.csv”, 2592000000, “LAND-30”],

/Front 36 hours/ [“Front - Previous 36 hours”, “csv/frontroom-current.graph.csv”, “csv/frontroom-target.graph.csv”, “csv/frontroom-heater.graph.csv”, 129600000, “FRONT”],

/Front 30days/ [“Front - last 30 days”, “csv/frontroom-current.graph.csv”, “csv/frontroom-target.graph.csv”, “csv/frontroom-heater.graph.csv”, 2592000000, “FRONT-30”],

/Hallway 36 hours/ [“Hallway - Previous 36 hours”, “csv/hallway-current.graph.csv”, “csv/hallway-target.graph.csv”, “csv/hallway-heater.graph.csv”, 129600000, “HALL”],

/Hallway 30days/ [“Hallway - last 30 days”, “csv/hallway-current.graph.csv”, “csv/hallway-target.graph.csv”, “csv/hallway-heater.graph.csv”, 2592000000, “HALL-30”],

/TV 36 hours/ [“TV - Previous 36 hours”, “csv/tv_current.graph.csv”, “csv/tv_target.graph.csv”, “csv/TVHeater.graph.csv”, 129600000, “TV”],

/TV 30days/ [“TV - last 30 days”, “csv/tv_current.graph.csv”, “csv/tv_target.graph.csv”, “csv/TVHeater.graph.csv”, 2592000000, “TV-30”],

/Dining 36 hours/ [“Dining room - Previous 36 hours”, “csv/dining_current.graph.csv”, “csv/dining_target.graph.csv”, “csv/dining-heater.graph.csv”, 129600000, “DIN”],

/Dining 30days/ [“Dining room - last 30 days”, “csv/dining_current.graph.csv”, “csv/dining_target.graph.csv”, “csv/dining-heater.graph.csv”, 2592000000, “DIN-30”],

/Kitchen 36 hours/ [“Kitchen - Previous 36 hours”, “csv/Kitchen-Current-temp.graph.csv”, “csv/Kitchen-Temp-Target.graph.csv”, “csv/Kitchen-Heater.graph.csv”, 129600000, “KITCHEN”],

/Kitchen 30days/ [“Kitchen - last 30 days”, “csv/Kitchen-Current-temp.graph.csv”, “csv/Kitchen-Temp-Target.graph.csv”, “csv/Kitchen-Heater.graph.csv”, 2592000000, “KITCHEN-30”],

/Electricity 36 hours/ [“Electricity Previous 36 hours”, “csv/electric-instant.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “ELEC”],

/Electricity 30days/ [“Electricity Previous - Last 30 days”, “csv/electric-instant.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “ELEC-30”],

/Gas Instant 36 hours/ [“Gas Instant - Previous 36 hours”, “csv/gas-instant.graph.csv”, “csv/dummy.csv”, “csv/heatdemand.graph.csv”, 129600000, “GAS”],

/Gas Instant 30 days/ [“Gas Instant - Last 30 days”, “csv/gas-instant.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “GAS-30”],

/Water Instant 36 hours/ [“Water Instant - Previous 36 hours”, “csv/dummy.csv”, “csv/water-instant.graph.csv”, “csv/dummy.csv”, 129600000, “WATER”],

/Water Instant 30 days/ [“Water instant - Last 30 days”, “csv/dummy.csv”, “csv/water-instant.graph.csv”, “csv/dummy.csv”, 2592000000, “WATER-30”],

/HOT Water Instant 36 hours/ [“Hot Water - Previous 36 hours”, “csv/hot-water-instant.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “HOT-WATER”],

/HOT Water instant 30 days/ [“Hot Water - Last 30 days”, “csv/hot-water-instant.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “HOT-WATER-30”],

/HOT Both Water 36 hours/ [“BOTH Water - Previous 36 hours”, “csv/hot-water-instant.graph.csv”, “csv/water-instant.graph.csv”, “csv/dummy.csv”, 129600000, “BOTH-WATER”],

/Boiler 36 hours/ [“Boiler demand 36 hours”, “csv/dummy.csv”, “csv/heatdemand.graph.csv”, “csv/boiler.graph.csv”, 129600000, “BOILER”],

/Boiler 30 days/ [“Boiler demand - Last 30 days”, “csv/dummy.csv”, “csv/heatdemand.graph.csv”, “csv/boiler.graph.csv”, 2592000000, “BOILER-30”],

/External Temp 36 hours/ [“External Temperature - 36 hours”, “csv/METEO-Temp.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “EXTTEMP”],

/External Temp 30 days/ [“External Temperature - Last 30 days”, “csv/METEO-Temp.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “EXTTEMP-30”],

/Light level 36 hours/ [“Light - 36 hours”, “csv/METEO-LUX.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “LUX”],

/Light Level 30 days/ [“Light - Last 30 days”, “csv/METEO-LUX.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “LUX-30”],

/Wind 36 hours/ [“Wind - 36 hours”, “csv/METEO-Wind-KH.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “WIND-KH”],

/Wind 30 days/ [“Wind - Last 30 days”, “csv/METEO-Wind-KH.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “WIND-KH-30”],

/Rain 36 hours/ [“Rain - 36 hours”, “csv/METEO-Rain.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “RAIN”],

/Rain 30 days/ [“Rain - Last 30 days”, “csv/METEO-Rain.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “RAIN-30”],

/External Temp 36 hours/ [“Rain - 36 hours”, “csv/METEO-RAIN.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 129600000, “RAIN”],

/External Temp 30 days/ [“Rain - Last 30 days”, “csv/METEO-RAIN.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “RAIN-30”],

/Velbus time out/ [“Velbus temperature intervals”, “csv/timeupcounter.graph.csv”, “csv/dummy.csv”, “csv/dummy.csv”, 2592000000, “Velbus”],

];

for (var k=0; k<graphData.length; k++){

makeGraph(graphData[k][0],graphData[k][1],graphData[k][2],graphData[k][3],graphData[k][4],graphData[k][5]);

}

});

function makeGraph(graphTitel,csvCurrent,csvTarget,csvRelay,numerOfMs,graphName){

var dataPoints1 = [];

var dataPoints2 = [];

var dataPoints3 = [];

$.ajax({

type: “GET”,

url: csvCurrent,

dataType: “text”,

success: function(data) {processData1(data);}

});

function processData1(allText) {

var allLinesArray = allText.split(’\n’);

if(allLinesArray.length>0){

for (var i = 1; i <= allLinesArray.length-1; i++) {

var line = allLinesArray[i].split(’,’);

if (dateDiff(line[0]) <= numerOfMs) {

dataPoints1.push({x: parseDate(line[0]),y:parseFloat(line[1])});

}

}

requestTempCsv();

}

}

function requestTempCsv() {

$.ajax({

        type: "GET",

        url: csvTarget,

        dataType: "text",

        success: function (data) { processData2(data); }

    });

}

function processData2(allText) {

var allLinesArray = allText.split(’\n’);

if(allLinesArray.length>0){

for (var i = 1; i <= allLinesArray.length-1; i++) {

var line = allLinesArray[i].split(’,’);

if (dateDiff(line[0]) <= numerOfMs) {

dataPoints2.push({x: parseDate(line[0]),y:parseFloat(line[1])});

}

}

dataPoints2.push({x: new Date(),y:parseFloat(0)});

requestTempCsv2();

}

}

function requestTempCsv2() {

    $.ajax({

        type: "GET",

        url: csvRelay,

        dataType: "text",

        success: function (data) { processData3(data); }

    });

}

function processData3(allText) {

var allLinesArray = allText.split(’\n’);

if(allLinesArray.length>0){

for (var i = 1; i <= allLinesArray.length-1; i++) {

var line = allLinesArray[i].split(’,’);

if (dateDiff(line[0]) <= numerOfMs) {

dataPoints3.push({x: parseDate(line[0]),y:parseFloat(line[1])});

}

}

dataPoints3.push({x: new Date(),y:parseFloat(0)});

drawChart(dataPoints1, dataPoints2, dataPoints3);

}

}

function drawChart(dataPoints1, dataPoints2, dataPoints3) {

var chart = new CanvasJS.Chart(graphName, {

theme: “theme2”,

title:{

text: graphTitel

},

backgroundColor: “#000000”,

axisX:{

labelAngle: 15,

valueFormatString: “DD/MMM - H:mm” ,

}, legend: {

horizontalAlign: “center”, verticalAlign: “bottom”,

fontColor: “#ffffff”,

fontSize: 15

},

    data: [

        {

        type: "stepArea",

markerType: “none”,

        dataPoints: dataPoints2

        },

        {

type: “line”,

markerType: “none”,

dataPoints: dataPoints1

        },

        {

type: “stepArea”,

markerType: “none”,

dataPoints: dataPoints3

        }

],

exportEnabled: true,

zoomEnabled:true

});

chart.render();

}

}

function parseDate(line)

{

return new Date(line.slice(0,4), line.slice(5,7)-1, line.slice(8,10),line.slice(11,13),line.slice(14,16));

}

function dateDiff(line)

{

var dateToCheck = Date.UTC (line.slice(0,4), line.slice(5,7)-1, line.slice(8,10),line.slice(11,13)-1,line.slice(14,16));

return Date.now() - dateToCheck;

}

``

Once the graphs.js file has been edited, you will need to create

sections in your html code to display the graphs.

As shown here :-

<Title=“Wollaston Overview”>

body { background-color: #000000; }

Breakdown of Master Bedroom current temperatures


Open Back suite in another window


Open Back Bedroom in another window


RED Gas consumption.

GREEN Blocks show how many rooms are calling for heat.

Open GAS in another window

Open Electricity in another window
Open Combined Water in another window

Open external temperature in another window
Open Light level in another window
Open GP06 with heating demand in another window
Open GP07 with heating demand in another window









Velbus message updates


``

ONE MASSIVE TIP…

When you refresh your web browser after editing your graph.js file (or html file) keep in mind that it is unlikely that the graph.js file will be correctly refreshed !!!

This took me hours of pain to learn…

To resolve this, direct your web browser to:-

http://{controller address}:8688/jscanvas/graphs.js

``

Then press F5 or refresh…

This will force the browser to reload the graphs,js file.

It is also an opportunity to double check that your changes have made their way to your browser.

That should be it, you should be able to create as many graphs as you like.

Keep in mind that the CSV files can get quite big if you leave them for many months.

The zipcsv.sh script file will zip them up nicely and put the dummy.csv file back so that your new data will continue to work in the graphs.

(I’ve created a command in OpenRemote designer which is linked to a button so that I can zip the files from my OR UI )

Good luck :slight_smile:

Stuart

jscanvas_v1.drl (1.07 KB)

jscanvas_a-overview.pdf (529 KB)

Dear Stuart,

I finally made lots of progress. I was able to unzip the files and to display the webpage with your csv data.

I have created the dummy sensor and the shell command.

Everything works and I am able to see my own csv files in the folder csv.

But I still have one difficulty… I have created a sensor but I was not able to have numerical value in *.graph.

I have only sensors created for knx. They act as switch and display on or off. For example, if I record in .graph one of my sensor, I have the following output :

2018-02-06 15:50:18,N/A

2018-02-06 15:50:42,on

2018-02-06 16:01:32,N/A

2018-02-06 17:03:40,N/A

2018-02-06 17:07:53,on

2018-02-06 17:07:54,off

2018-02-06 17:13:47,on

2018-02-06 17:13:58,off

2018-02-06 17:18:03,N/A

2018-02-06 18:16:56,on

Do you have any ideas why I have some N/A ?

Do you know how to create a custom sensor to produce numerical values 1 for on and 0 for off for example ?

I have tried (trying to use what you did with your custom sensor pressed or unpressed) but the file remains empty…

An a last question, is it possible to add the value of several sensor once you have numerical values ? and to record several sensor in a single csv files ?

Thanks in advance

Best regards

Olivier

Hi Olivier,

You’re really close now…

Just edit the custom sensors you’ve created and replace…

pressed with on
released with off

Maybe add something for N/A (for reference, it is my understanding that OpenRemote reports N/A when it is unable to retrieve a status from a connected device, I can force such a state by removing a Velbus module from a network)

The idea is to map whatever your devices actually output to a numeric value.

Velbus buttons just happen to output pressed, released & long_press

As for adding up values from numeric sensors, I’m sure that could be done in a rule, I’m just not capable of telling you how.

A fresh question on here should get you an answer from a coding magician.

Michal created a rule that counts “how many thermostats are calling for heat”

The results of which can be seen in my Gas consumption graph.

Here’s a link to the topic - https://groups.google.com/forum/m/#!topic/openremotecommunity/er-pDE1prGQ

As for putting all the results into one CSV file.

Just edit the csvmaker.sh file and tweak one of the last ‘echo’ lines.

The second part of that line defines where the data is written.

have a play around with the echo command on its own, outside of the sh file and you’ll work out how it operates.

echo hello >>testfile.txt

Good luck

Stuart

Hi

Thanks for the update email.

It’s great to hear that it’s working for you.

You asked if it were possible to put all the data into a single file.

This would be done by editing the csvmaker.sh script and changing where the variables are written.

The following echo command will push all the variables into a single csv file called combined.csv in the format :-

date time,source,value
date time,source,value
date time,source,value
date time,source,value

``