NodeRed - InfluxDb - Chart

[Cristiano Nunes]

Hello, I would like to know your opinion /help,to convert the information I have in influxdb to a graphic.

[Colin Law]

I would use Grafana. It will take the data straight out of influx and graph it for you.

Colin

On 9 January 2018 at 17:34, Cristiano Nunes <crt....@gmail.com> wrote:

Hello, I would like to know your opinion /help,to convert the information I have in influxdb to a graphic.

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/589fcb8b-1b5f-4265-8312-05e5d5737198%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Cristiano Nunes]

Hello, Yes I know the Grafana platform, I never used it. I would like to continue to use only a Node-Red platform and the dashboard.

[David Caparrós]

Hello Cristiano,

The problem with node red and the graph will be if you use a lot of points then dashboard will become with a lot of lag,

For the test I made the graph on node red is useful whenever you are going to display short series of data if not another option will be better....  As colin told Grafana is a good option but there are many other options, sure you will be able to find something that fit your needs depending what you expect to do.

Regards

David

[Colin Law]

Do you mean you want to fetch data from an influx db and display it
using the standard dashboard chart? If so which bit are you having
problems with? Querying the db or formatting that for passing to the
chart?

Colin

On 10 January 2018 at 11:53, Cristiano Nunes <crt....@gmail.com> wrote:
>
> Hello, Yes I know the Grafana platform, I never used it. I would like to
> continue to use only a Node-Red platform and the dashboard.
>

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to node-red+u...@googlegroups.com.

> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/61b8368e-ddf3-424e-bbb6-b03cdebafe3e%40googlegroups.com.

[Cristiano Nunes]

What I want to do is a flexible data search, where to select the start and end of the search

My first attempt was to use mysql, but it was very slow when trying to process many records. The next step was to switch to influxdb, and wanted to try to do the same thing, but now with influxdb.

Yesterday I was able to process the data for a table.

today I'm trying to move to graph to test the performance

function code:

var now1 = new Date();

var CommsStatus;

var sql = [];

var outputs = [];

var msg1, msg2, msg3, msg4, msg5, msg6, msg7, msg8, msg9, msg10, msg11, msg12, msg13, msg14 , msg15 , msg16, msg17, msg18, msg19, msg20, msg21 = {};

var length

var m = {};

var msgrtn = 'arroz';

//Recepeção varios msg.payload's atravez do topic

context.data = context.data || new Object();

context.data1 = context.data || new Object();

length = msg.payload.results[0].series[0].values.length;

for (i = 0; i < length; i++) { 

    context.data[i] = msg.payload.results[0].series[0].values[i][0];

    context.data1[i] = msg.payload.results[0].series[0].values[i][1];

}

     //estrutura para criar grafico de barras duplas

  m.labels = ["Time", "Value"];

  m.series = ['time', 'Value'];

  m.data = [

    [context.data[i]],

    [context.data1[i]],

  ]

return {payload:[m]};

good or bad opinions, and help welcome

[steve rickus]

Cristiano-

I see three errors in your function -- the array of "series" names should be ["ActiveEnergy"],

the data points should be an array of objects with "x" and "y" properties,

and the x values need to be multiplied by 1000 to convert from seconds to milliseconds.

According to the ui_chart readme example, your function output should look something like this:

[
  {
    "series": [
      "ActiveEnergy"
    ],
    "labels": [
      "time",
      "value"
    ],
    "data": [
      [
        {
          "x": 1515542400000,
          "y": 2.7
        }
      ],
      [
        {
          "x": 1515549600000,
          "y": 2.8
        }
      ]
    ]
  }
]

I think that once you get that format corrected, your graphs will work as expected. Just be careful to build your queries so there are not hundreds or thousands of data points, otherwise your browser will slow to a crawl.

Finally, just to give you an alternate solution, you should check out the Jsonata syntax available in a change node. I pasted a bit of your data into the Jsonata exerciser site, and used the following expression to transform it into the graph format above:

payload.results.[{
    "series": series.[name],
    "labels": series.[columns],
    "data": [
        series.values.[{
            "x": $[0]*1000,
            "y": $[1]
        }]
    ]
}]

You can see it working (and make your own changes) at http://try.jsonata.org/HJfrIhQEf

--

Steve

[Cristiano Nunes]

Thanks for help-me :)

I tried putting this code in the function, but the error.

first started by giving us an error '[{' tried to fix, then gave error "series" not set, probably I'm doing something wrong.

Can you help me?

[Colin Law]

Feed the output of the function into a debug node and look at what is coming out. Check that the format is as it should be, as described in the information for the chart node.

Colin

--

http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/e725bb3b-4d18-4f90-8cf1-2aba0df4d5b1%40googlegroups.com.

[Cristiano Nunes]

Hello,

If I try to use Steve code I can not compile.

[erwin....@gmail.com]

You have to use this in a "change" node, like Steve said, not inside a function node.

Finally, just to give you an alternate solution, you should check out the Jsonata syntax available in a change node.

Op vrijdag 12 januari 2018 11:34:23 UTC+1 schreef Cristiano Nunes:

[Cristiano Nunes]

Olá, peço desculpa, mas voces tem de ter paciencia comigo :(

Mas eu não estou a entender, depois do meu nó do influx, tenho de colocar  um nó de "change" e depois o grafico, right?

how to configure, the change node?

Many thanks to you for your help and for your patience with me.

[steve rickus]

Choose "Set" option, then the "msg." pulldown -- type "payload" in the first input box

Next to "to" on the 2nd line, select the "J:" pulldown, click the three dots "..." next to the input box.

This brings up the Jsonata expression editor window. Then paste the expression I sent previously.

If this still doesn't work, send a picture of the change node properties.

--

Steve

[Cristiano Nunes]

Hello,

The function I think is working, but the graph does not connect the points: s

I tried to change the type of graph between lines and bars, without success.

[steve rickus]

That is strange -- on the chart property page, there is a checkbox near the bottom called "Use deprecated (pre 2.5.0) data format"

That will need to be unchecked in order to work with this data format. Also, which "Interpolate" setting are you using?

If you reply with your raw (query) data, I can try to reproduce the problem here. In the meantime, please send the software versions that are displayed in the node-red log at startup...

On Friday, January 12, 2018 at 1:10:47 PM UTC-5, Cristiano Nunes wrote:

The function I think is working, but the graph does not connect the points: s

[Cristiano Nunes]

Hello, 

My Steam Info:

13 Jan 15:39:14 - [info] Node-RED version: v0.17.5

13 Jan 15:39:14 - [info] Node.js  version: v4.2.6

13 Jan 15:39:14 - [info] Linux 4.10.0-42-generic x64 LE

13 Jan 15:39:14 - [info] Loading palette nodes

13 Jan 15:39:16 - [info] Dashboard version 2.6.2 started at /ui

Linux Lubuntu 16.04 x64

I tried to change the interpolation of the graph, also I tried several different options in the graph.

I also changed the query, but it did not work.

The graphic appears with dots of different colors, I think, should be different series.

My influx query 

select last(value) - first(value) from ActiveEnergy where time > '2018-01-11T00:00:00Z'  and time < '2018-01-12T00:00:00Z' group by time(1h) fill(null)

Data from influx query

{"_msgid":"69d03669.962fc8","topic":"","payload":{"results":[{"series":[{"name":"ActiveEnergy","columns":["time",""],"values":[[1515628800,5.600000000000364],[1515632400,5.699999999999818],[1515636000,5.5],[1515639600,5.699999999999818],[1515643200,5.600000000000364],[1515646800,5.5],[1515650400,7],[1515654000,6.599999999999454],[1515657600,10.5],[1515661200,12],[1515664800,13.099999999999454],[1515668400,15.400000000000546],[1515672000,13.399999999999636],[1515675600,9.200000000000728],[1515679200,13.5],[1515682800,13.599999999999454],[1515686400,14.100000000000364],[1515690000,15.599999999999454],[1515693600,12.599999999999454],[1515697200,7.099999999999454],[1515700800,5.600000000000364],[1515704400,5.5],[1515708000,5.400000000000546],[1515711600,5.600000000000364]]}]}]}}

[steve rickus]

Ok, I've been able to use your real data to find the problem in my jsonata syntax... instead of the data being an array of an array of 24 points, I was generating an array of 24 arrays of 1 point each. This is why you were seeing disconnected points, each interpreted as a series with its own color. Sorry about that!

Try this instead:

payload.results.[{
    "series": series.[name],

    "labels": series.[columns],

    "data": [
        [series.values.{
            "x": $[0]*1000,

            "y": $round($[1], 2)
        }]
    ]
}]

I also added the $round function to eliminate all those trailing 0's 

I noticed that url I sent previously for the jsonata exerciser was NOT the one I thought it was.

Here is the correct link -> http://try.jsonata.org/BJgHHpv4M

--

Steve

[Cristiano Nunes]

Yes, it's working now. 

You do not have to apologize for anything. 

I have to thank you for your patience, thank you very much for your help.

[Bruno Suarez]

Hello,
Your answer helped me to plot a measurement with ui_chart.
But how to modify the JSONata to draw several series on the same graph:
If the result of the influxdb request is:

{
  "_msgid": "1d3299b9.736ca6",

  "topic": "",
  "payload": {
    "results": [
      {
        "series": [
          {

            "name": "indexeau",
            "columns": [
              "time",
              "value"
            ],
            "values": [
              [
                1519906805,
                2174.041
              ],
              [
                1519906807,
                2174.041
              ],
              [
                1519906809,
                2174.041
              ]
            ]
          },
          {
            "name": "indexgaz",
            "columns": [
              "time",
              "value"
            ],
            "values": [
              [
                1519906820,
                2000.041
              ],
              [
                1519906822,
                2000.041
              ],
              [
                1519906823,
                2000.041
              ],
              [
                1519906826,
                2000.041
              ]
            ]
          }
        ]
      }
    ]
  }
}

Thanks

[Colin Law]

Have you looked at what Grafana can do for you if you are already
using influxdb? There is no way you will ever get anything like as
good as the grafana graphs using ui_chart.

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to node-red+u...@googlegroups.com.

> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/03ec6bfb-df14-4498-b055-a4a05dc4778d%40googlegroups.com.

[steve rickus]

Bruno,

Colin is right that Grafana is the preferred graphing package for Influx data... but that doesn't help if you want to show the data in the node-red dashboard, along with other node-red data or controls.

So to answer your own question, you should go the the jsonata exerciser site, paste your JSON data into the left-hand input window, paste the Jsonata expression into the upper-right, and modify it until you get the output that ui_chart needs.

In the end, you will may end up with something like this:

payload.results.[{
 "series": series.name,
    "labels": series[0].columns,
    "data": [
    series.[values.{
        "x": $[0]*1000,
            "y": $round($[1], 3)
        }]
    ]
}]

But the fun and learning are in using the exerciser site on your real data...

--

Steve

[Bruno Suarez]

Thank you Colin and Steve for your comment on Grafana, I will look closely at all this, but it's true that currently I test with Node-Red.

Thank you Colin for the modification of JSONat is perfect ..
I tried with jsonata exercise but the syntax still remains for me mysterious!

If I abuse your knowledge: how now apply a different coefficient "y" for 2 sets:
For example :
"y": $ round ($ [1], 3) * 2 for the first series and
"y": $ round ($ [1], 3) * 3 for the second.

Thanks again for quick help
Bruno

[Colin Law]

On 2 March 2018 at 13:32, Bruno Suarez <bruno.s...@gmail.com> wrote:
> Thank you Colin and Steve for your comment on Grafana, I will look closely
> at all this, but it's true that currently I test with Node-Red.
>
> Thank you Colin for the modification of JSONat is perfect ..

That was from Steve actually.

Colin

> --
> http://nodered.org
>
> Join us on Slack to continue the conversation: http://nodered.org/slack
> ---
> You received this message because you are subscribed to the Google Groups
> "Node-RED" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to node-red+u...@googlegroups.com.
> To post to this group, send email to node...@googlegroups.com.
> Visit this group at https://groups.google.com/group/node-red.
> To view this discussion on the web, visit

> https://groups.google.com/d/msgid/node-red/4cb6cf13-bc48-4490-8b11-692b73dc1d03%40googlegroups.com.

[Bruno Suarez]

Error, yes thanks to specialist : Steve

[steve rickus]

I don't generally like to put such positional logic inside my expressions. Can you ensure that the two series are always in that order? What happens when you add a third or fourth series? Why the need to increase the values by 2 or 3 times -- so they fill the graph better?

Instead of trying to inflate the values after the query, I would probably just change my queries -- so the select statement would do the multiplication as necessary for each series.

If you really want to do it inside the jsonata expression, you could add a conditional check on the series name and use that as the multiplier, I suppose:

    "data": [
    series.[(
            $mult := name = "indexeau" ? 2: name = "indexgaz" ? 3 : 1;

            values.{
            "x": $[0]*1000,

                "y": $round($[1], 3) * $mult
            }
        )]
    ]

[Rewe Node]

Hi Bruno,

I realized about the same problem for 4 temperature sensors.

I read the sensors one by one in RAV format from the Influxdb

I save the individual query results in an array in the flow context.

From the JSON ARRAY I generate the line data structure with JSONata.

The expression is then very simple:

(
   $ts := $flowContext('tempSerie');
   $series := $ts.**.columns[1];
   $data := $ts.[
       results.series.$map(
           values,
           function($v)
                       {
               {
                   "x": $v[0],
                   "y": $v[1]
                         
               }
                       
           }
                 
       )
   ];
   [
       {
           "series": $series,
           "data": $data
       
       }
   
   ]


)

Here the complete Flow

[{"id":"d1c59fcb.25593","type":"influxdb in","z":"b9acf092.52dba8","influxdb":"d268d9d8.f29f2","name":"WG SELECT Query (Raw)","query":"SELECT mean(\"temperature\") AS \"Wintergarten\" FROM \"rwhome\".\"autogen\".\"temperature\" WHERE time > now() - 1d AND \"device\"='HEQ0031167:1' GROUP BY time(10m) ","rawOutput":true,"precision":"ms","retentionPolicy":"","x":421,"y":164,"wires":[["ac50e102.e5729","478c7f4f.227de8"]]},{"id":"d3a78ea5.174e6","type":"inject","z":"b9acf092.52dba8","name":"eyery 10 minutes","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":176,"y":163,"wires":[["d1c59fcb.25593"]]},{"id":"3e688dd4.4804ba","type":"ui_chart","z":"b9acf092.52dba8","name":"myTemp Chart","group":"45ccb249.d78b94","order":1,"width":"6","height":"5","label":"Home Temperatures","chartType":"line","legend":"true","xformat":"auto","interpolate":"linear","nodata":"hole Daten","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"colors":["#1F77B4","#AEC7E8","#FF7F0E","#2CA02C","#98DF8A","#D62728","#FF9896","#9467BD","#C5B0D5"],"useOldStyle":false,"x":705,"y":809,"wires":[[],[]]},{"id":"2d7399c.bbf80e6","type":"comment","z":"b9acf092.52dba8","name":"WG","info":"","x":362.5,"y":111,"wires":[]},{"id":"cdfd959d.e9cec","type":"influxdb in","z":"b9acf092.52dba8","influxdb":"d268d9d8.f29f2","name":"Garten SELECT Query (Raw)","query":"SELECT mean(\"temperature\") AS \"Garten\" FROM \"rwhome\".\"autogen\".\"temperature\" WHERE time > now() - 1d AND \"device\"='HEQ0118187:1' GROUP BY time(10m) ","rawOutput":true,"precision":"ms","retentionPolicy":"","x":432,"y":332,"wires":[["8cad15d8.1bfdc"]]},{"id":"31f91162.26146e","type":"comment","z":"b9acf092.52dba8","name":"Garten","info":"","x":363.5,"y":297,"wires":[]},{"id":"8f1a3280.9eb8b8","type":"influxdb in","z":"b9acf092.52dba8","influxdb":"d268d9d8.f29f2","name":"Keller SELECT Query (Raw)","query":"SELECT mean(\"temperature\") AS \"Keller\" FROM \"rwhome\".\"autogen\".\"temperature\" WHERE time > now() - 1d AND \"device\"='HEQ0355273:1' GROUP BY time(10m) ","rawOutput":true,"precision":"ms","retentionPolicy":"","x":431,"y":503,"wires":[["69fcf38c.049194"]]},{"id":"8b006b03.5451a","type":"comment","z":"b9acf092.52dba8","name":"Keller","info":"","x":355.5,"y":465,"wires":[]},{"id":"dde198ca.738458","type":"influxdb in","z":"b9acf092.52dba8","influxdb":"d268d9d8.f29f2","name":"WZ SELECT Query (Raw)","query":"SELECT mean(\"temperature\") AS \"Wohnzimmer\" FROM \"rwhome\".\"autogen\".\"temperature\" WHERE time > now() - 1d AND \"device\"='HEQ0355711:1' GROUP BY time(10m) ","rawOutput":true,"precision":"ms","retentionPolicy":"","x":411,"y":678,"wires":[["dbae278d.e4f95"]]},{"id":"4383578b.13b49","type":"comment","z":"b9acf092.52dba8","name":"Wohnzimmer","info":"","x":376.5,"y":640,"wires":[]},{"id":"ac50e102.e5729","type":"change","z":"b9acf092.52dba8","name":"","rules":[{"t":"set","p":"temperatureSerie[0]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":429.5,"y":212,"wires":[["cdfd959d.e9cec"]]},{"id":"8cad15d8.1bfdc","type":"change","z":"b9acf092.52dba8","name":"","rules":[{"t":"set","p":"temperatureSerie[1]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":429,"y":381,"wires":[["8f1a3280.9eb8b8"]]},{"id":"69fcf38c.049194","type":"change","z":"b9acf092.52dba8","name":"","rules":[{"t":"set","p":"temperatureSerie[2]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":547,"wires":[["dde198ca.738458"]]},{"id":"dbae278d.e4f95","type":"change","z":"b9acf092.52dba8","name":"","rules":[{"t":"set","p":"temperatureSerie[3]","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":422,"y":730,"wires":[["d8e5e8af.09e2d8"]]},{"id":"d8e5e8af.09e2d8","type":"change","z":"b9acf092.52dba8","name":"prepare line chart data structure","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t    $ts := $flowContext('temperatureSerie');\t    $series := $ts.**.columns[1];\t    $data := $ts.[results.series.$map(values,\t                   function($v) \t                       {\t                           {\t                               \"x\": $v[0],\t                               \"y\": $v[1]\t                           }\t                        }\t                 )];\t   [\t       {\t           \"series\": $series,\t           \"data\": $data\t       }\t    ]\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":426,"y":810,"wires":[["3e688dd4.4804ba","cd7a6b6f.1ee5d8"]]},{"id":"cd7a6b6f.1ee5d8","type":"debug","z":"b9acf092.52dba8","name":"neu","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":688,"y":865,"wires":[]},{"id":"478c7f4f.227de8","type":"debug","z":"b9acf092.52dba8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":694.5,"y":164,"wires":[]},{"id":"d268d9d8.f29f2","type":"influxdb","z":"","hostname":"192.168.99.139","port":"8086","protocol":"http","database":"rwhome","name":"rwhome","usetls":false,"tls":"dbdbee3a.f5c3c8"},{"id":"45ccb249.d78b94","type":"ui_group","z":"","name":"carts","tab":"117d29fb.cbb31e","order":1,"disp":true,"width":"6"},{"id":"dbdbee3a.f5c3c8","type":"tls-config","z":"","name":"local-tls","cert":"","key":"","ca":"","certname":"","keyname":"","caname":"","verifyservercert":false},{"id":"117d29fb.cbb31e","type":"ui_tab","z":"","name":"Homematic","icon":"dashboard","order":1}]

Test chart

Reiner

Am Donnerstag, 1. März 2018 14:40:53 UTC+1 schrieb Bruno Suarez:

Hello,
Your answer helped me to plot a measurement with ui_chart.
But how to modify the JSONata to draw several series on the same graph:

...