Node Red and Influx DB

[Dan Hoover]

I'm hoping someone can point me in the right direction on this. I'm looking to get started with influxdb. It's installed and working, but I'm having a hard time parsing the data in NR. Data is going in fine, but it's one giant blob instead of parsed data and "columns" (sorry I still think in SQL terms). 

Basically, MQTT is getting my data in this format...

?temp=58.10&set=50.00&st=0&id=40

temp is the temperature

set is the setpoint

st is the state of the system

id is the id of the sensor

How can I take this payload and parse it so that influx sees it as one query with a timestamp and those 5 columns?

Thanks in advance for all your help!

[Colin Law]

Are you trying to insert data into the influx db or get it out? You
say MQTT is getting the data in that format, don't know what you mean
by 'MQTT getting'. Where is the data coming from in that strange
format?

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/9e147f3b-f292-4907-8e6d-68d5652a3ef0%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Dan Hoover]

The data is coming from many arduinos via MQTT. The formatting is preformatted for a get request to a strictly whitelisted server, but I would also like to start getting the data into influx so I can do some visualization.

[Patrik Åkerfeldt]

I suppose you are using node-red-contrib-influxdb?

You should know that InfluxDB stores data as either fields or tags. The main difference is that tags are indexed whereas fields are not. I.e. the values that change often should be put as fields and values which you typically want to put in the WHERE clause you store as tags.

In your example you could use

temp as field

set as field

st as field

id as tag

Having said that and if you use node-red-contrib-influxdb what you can do is to create a javascript array that looks like this:

[{"temp": tempValue, "set": setValue, "st": stValue}, {"id": idValue}]

and this would be what you feed into the InfluxDB node. InfluxDB will interpret the first object in the array as a set of fields, and the second object as a set of tags. This is also described in the Info tab of the InfluxDB node in NR.

-pa

[Colin Law]

An alternative to using multiple tags is to generate field names that
are unique for each sensor/value. I do this by using names like
"bedroom/temperature", "bedroom/setpoint" and so on. In your case you
would need to build these from the idvalue and temp, set etc. In fact
in your situation I would have a separate flow that gets the packed
data that you describe and splits it up into meaningful topics and
values and puts these back into mqtt. Then you just need to subscribe
to those in the influx flow and feed them straight into influx using
the topic as the influx field name.

Colin

> https://groups.google.com/d/msgid/node-red/d1aa7d07-26d1-4b10-8c93-9af99e6272f5%40googlegroups.com.

[Dan Hoover]

Sorry for the slow response. People kept coming into my office. It's annoying when work gets in the middle of fun!

Yes, I am using the node-red-contrib-influxdb.

I'm sort of following what you're saying, I'm just not sure how to make that json string happen.  I was able to parse all the data by splitting like this....

[{"id":"43bdd79d.b23e58","type":"inject","z":"d2fd89d5.b11fd8","name":"actual","topic":"actual","payload":"?temp=58.10&set=50.00&st=0&id=40","payloadType":"str","repeat":"","crontab":"","once":false,"x":83,"y":156,"wires":[["69f9883f.059ed8"]]},{"id":"ddd7122a.77ac5","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"&","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":322,"y":158,"wires":[["cc6d2da2.1104c"]]},{"id":"69f9883f.059ed8","type":"function","z":"d2fd89d5.b11fd8","name":"?","func":"var str = msg.payload;\nvar temp = str.substr(1,99);\nmsg.payload = temp;\nreturn msg;","outputs":1,"noerr":0,"x":204,"y":155,"wires":[["ddd7122a.77ac5"]]},{"id":"cc6d2da2.1104c","type":"switch","z":"d2fd89d5.b11fd8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"cont","v":"set=","vt":"str"},{"t":"cont","v":"temp","vt":"str"},{"t":"cont","v":"id=","vt":"str"},{"t":"cont","v":"st=","vt":"str"}],"checkall":"true","outputs":4,"x":454,"y":158,"wires":[["92156eb1.f1fbf"],["ee46cf07.4aadd"],["6ecfb1e9.7ac3e"],["47cac871.bd6228"]]},{"id":"92156eb1.f1fbf","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":138,"wires":[["63765632.b61768"]]},{"id":"ee46cf07.4aadd","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":175,"wires":[["63765632.b61768"]]},{"id":"6ecfb1e9.7ac3e","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":211,"wires":[["63765632.b61768"]]},{"id":"47cac871.bd6228","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":248,"wires":[["63765632.b61768"]]},{"id":"63765632.b61768","type":"debug","z":"d2fd89d5.b11fd8","name":"","active":true,"console":"false","complete":"false","x":998,"y":194,"wires":[]}]

But I don't know how to concatenate that JSON string other than using globals and I don't think that would work in this situation. Can you point me in the right direction?

[Dan Hoover]

Yeah. That's sort of how I have everything else setup in terms of my MQTT. It's configured as business/campus/building/more/detailed/info, but right now  all of that is tied to that id that I'm passing in. The SQL db can infer all that info from that id number.

Where I'm at right now is that I've figured out how to "parse" that formerly poorly constructed string into something that I'm pretty sure I can use to get to work with influx... I just need a way to turn those 4 pieces of data into a JSON or whatever influx needs.  Again, this is day 1 of me using influx, so I'm not sure what I don't know.

[Colin Law]

You just have to pack the values up as an object in the payload with
fieldname and value pairs. I have just picked up some debug node
output from one of mine, showing what is in the payload of a typical
message being passed to the influx node. It is
{ bedroom/setpoint: 21.159158008642937, bedroom/radsetpoint:
31.062172575405018, kitchen/setpoint: 20.75, kitchen/temperature:
20.96662472222222 }

If you need help doing that tell us the exact format of the data you
have got, now that you have split it up.

Colin

> https://groups.google.com/d/msgid/node-red/2440d29c-258a-46dd-b956-61945f0c94b7%40googlegroups.com.

[Dan Hoover]

Yeah. I actually could use some help doing that.  I just need id, temp, set, st, id 

I know there is a gap in my knowledge here. I know how to set them and concatenate them as global variables, but I don't think that would work with a flood of messages coming in. I need to basically reorganize this flow into that JSON string like yours is.  Thanks so much for your help.  Here is a copy of my flow with that data broken out. Thanks so much for your patience.

[{"id":"43bdd79d.b23e58","type":"inject","z":"d2fd89d5.b11fd8","name":"actual","topic":"actual","payload":"?temp=58.10&set=50.00&st=0&id=40","payloadType":"str","repeat":"","crontab":"","once":false,"x":83,"y":156,"wires":[["69f9883f.059ed8"]]},{"id":"ddd7122a.77ac5","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"&","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":322,"y":158,"wires":[["cc6d2da2.1104c"]]},{"id":"69f9883f.059ed8","type":"function","z":"d2fd89d5.b11fd8","name":"?","func":"var str = msg.payload;\nvar temp = str.substr(1,99);\nmsg.payload = temp;\nreturn msg;","outputs":1,"noerr":0,"x":204,"y":155,"wires":[["ddd7122a.77ac5"]]},{"id":"cc6d2da2.1104c","type":"switch","z":"d2fd89d5.b11fd8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"cont","v":"set=","vt":"str"},{"t":"cont","v":"temp","vt":"str"},{"t":"cont","v":"id=","vt":"str"},{"t":"cont","v":"st=","vt":"str"}],"checkall":"true","outputs":4,"x":454,"y":158,"wires":[["92156eb1.f1fbf"],["ee46cf07.4aadd"],["6ecfb1e9.7ac3e"],["47cac871.bd6228"]]},{"id":"92156eb1.f1fbf","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":138,"wires":[["63765632.b61768"]]},{"id":"ee46cf07.4aadd","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":175,"wires":[["63765632.b61768"]]},{"id":"6ecfb1e9.7ac3e","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":211,"wires":[["63765632.b61768"]]},{"id":"47cac871.bd6228","type":"split","z":"d2fd89d5.b11fd8","name":"split","splt":"=","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":602,"y":248,"wires":[["63765632.b61768"]]},{"id":"63765632.b61768","type":"debug","z":"d2fd89d5.b11fd8","name":"","active":true,"console":"false","complete":"false","x":998,"y":194,"wires":[]}]

[Dan Hoover]

If it's not clear in my export that I shared, the input node is the data as I'm receiving it in NR.  The rest of it is just my attempts to split that into something usable. 

[Colin Law]

Is the id a sensor identifier? If so then you presumably have, for
example, temp values for other id values too. If so then what field
names do you want to use for the different id values?

Colin

> https://groups.google.com/d/msgid/node-red/08d94b2a-1bf2-4b3d-bc61-0e5ee951465e%40googlegroups.com.

[Dan Hoover]

id is actually the id in a sql database.  Essentially, it serves as the unique identifier for the sensor that sent the data.  

So the field name is just id and the number will be somewhere between 1 and 500.  Does that make sense?   

So my string that came in ?temp=58.10&set=50.00&st=0&id=40

will be converted to

{ id:40, temp:58.10, set:50.00, st:0 } (and I think influx just puts the timestamp in automatically.

[Dan Hoover]

I think I'm on the wrong track with my example node above.  I definitely need to convert

?temp=58.10&set=50.00&st=0&id=40

to

{ id:40, temp:58.10, set:50.00, st:0 } 

but splitting it out doesn't seem to be doing the trick.

[Dan Hoover]

Ok. I think I'm following. I made a function that assigns variables to the data in ?temp=58.10&set=50.00&st=0&id=40

var str = msg.payload;

var res = str.split("&");

//parse temperature

var temp = res[0];

temp = temp.split("=");

temp = temp[1];

//parse set

var set = res[1];

set = set.split("=");

set = set[1];

//parse state

var st = res[2];

st = st.split("=");

st = st[1];

//parse id

var id = res[3];

id = id.split("=");

id = id[1];

msg.payload = set;

//msg.payload = res;

return msg;

Obviously setting the payload manually at the end just proves that I can grab an arbitrary data point.  My last question... do I need to convert it to a JSON string or a javascript object.  Not sure how to get from these variables to 

[Dan Hoover]

I got it! Thanks for your help pointing me in the right direction. For posterity, I parsed it like this. I'm sure there's a more elegant way to do it, but I'm terrible at javascript.

var str = msg.payload;

var res = str.split("&");

//parse temperature

var temp = res[0];

temp = temp.split("=");

temp = temp[1];

//parse set

var set = res[1];

set = set.split("=");

set = set[1];

//parse state

var st = res[2];

st = st.split("=");

st = st[1];

//parse id

var id = res[3];

id = id.split("=");

id = id[1];

//left side is the field name, right side is the actual variable

var object = {

    temp:temp,

    set:set,

    st:st,

    id:id,

};

msg.payload = object;

return msg;

[Colin Law]

If I understand correctly then no, you are still on the wrong track.
Consider the fact that id has completely different meaning to the
other values. The values for temp, set and st identify values at for
the sensor identified by id at the current time. You need to tell
influx that id is the tag defining the sensor for those values, so as
Patrik suggested in his post you need to generate, in the payload
[{"temp": 58.10, "set": 50.00, "st": 0}, {"id": 40}]

The first element in the array gives the field names and values and
the second gives the tag name and value which will be used to identify
which sensor those values relate to.

However I suggest that you don't just use 40 as the sensor name unless
you really want to do that. I would have thought it would be much
easier when you come to extract the data if you gave the sensors
meaningful names, so for example id 40 might relate to the room
temperature in the office, so in that case you would want something
like
[{"temp": 58.10, "set": 50.00, "st": 0}, {"sensor": "office_temp"}]

Have you read through the influx documentation so you understand about
tags and fields? This is a good place to start
https://docs.influxdata.com/influxdb/v1.4/concepts/key_concepts/

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/04283993-bcbc-4def-bd2a-47a563aa36b3%40googlegroups.com.

[Dan Hoover]

Colin, 

Thanks so much for your time.  I'm tracking with you on moving the id to a separate array. This isn't an influx forum so I wasn't trying to ask those sorts of questions, but that helps a lot. I've watched some videos, but I need to read through those docs.

My application is a little bit different than how most people probably use this and to be honest, I may not be doing it right.

40 is the id because these sensors are primarily connecting to a SQL db.  So, for my purposes, I know the organization/campus/building/zone for every sensor with its id.  I don't have to send that to my db every time. 

Basically, the way it works, the arduinos send the info to Node Red, which sends it (now) to both influx and a PHP app. The php app does all sorts of other processing (alerts, etc) and is part of something much bigger that allows employees to get a quick overview of the data that they need access to. However, because it's already a big db that serves a lot of other purposes, I'm only storing current info in that SQL DB.

I'm using influx to track historical data and do analytics, which is why I'm sort of shoehorning it in like this.  

Am I off track on that? 

Completely unrelated (to NR) question: Do you know if there's a way for grafana to detect a new sensor and automatically build a chart for it? I feel like this should be doable because all the field labels are static. 

Anyway...thanks for your patience. I don't even pretend to know what I'm doing in this area.  This is all to stretch me and try to take things to the next level.

[Dan Hoover]

Colin,

Thanks so much for that link to the docs. Whoever wrote that did a fantastic job describing the concepts and now what you're saying makes a ton more sense. Thanks again.

[steve rickus]

There is a more elegant way... called JSONata!

Not that you probably want to learn yet another syntax (at this point), but in a change node, "Set:" msg.payload to type "J:" using this expression. 

$substringAfter(payload, "?").$split("&").($kv := $split($, "="); {$kv[0]: $kv[1]})~>$merge()

It splits everything after the "?" on the ampersand, and passes that array of key=value strings to the next step in the chain. That step splits each string into a [key,val] array, and returns a new object {key: val}. Finally the array of objects is merged into one object and returned a msg.payload. Voila!

If you want to see it in action, you can play around with it using this exerciser page on the jsonata.org site. As you modify the expression in the upper right pane, you will see the new results displayed in the lower right pane -- fun and educational!

--

Steve

P.S. As there is always more than one way to do things, here is another expression that may be a bit more readable:

$substringAfter(payload, "?").$split("&") {
    $substringBefore("="): $number($substringAfter("="))
}

This takes the array of "key=val" strings, and builds an object with keys taken from the part of the string before the "=", and values taken from after the "=" converted to a number... all without using any javascript

[steve rickus]

Actually, this is the syntax that makes the most sense to me, now that I understand why it works:

payload.$split("&").[$split("=")] {
    $[0]: $[1]
}

The trick is to keep the [key, val] arrays returned from the second split function from being flattened into a single level array -- which is what the square brackets around the function does -- very cool!

[Colin Law]

Unfortunately we have now decided he probably wants the id as an
influx tag so he needs
[{"temp": 58.10, "set": 50.00, "st": 0}, {"id": 40}]

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/696d7241-5e36-43e9-b8a0-057f2fe03f70%40googlegroups.com.