Let's Control It

Have fun with controlling everything!
https://www.letscontrolit.com/forum/

Writing to InfluxDB from HTTP Advanced controller?

https://www.letscontrolit.com/forum/viewtopic.php?t=5298

Page 1 of 1

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 09 Aug 2018, 13:23
by moebius
@drum: for more data, i would vote for influxdb (the built-in downsampling ability is really nice) in combination with grafana.
Also, it's not common to save everything in one table or to store aggregated/min/max values (usually done with queries).

@spachal: Nice sample for beginners.
Consider that, if you have more sensors, it will soon get confusing and hard to maintain (eg: if the ip address changes, you have to edit all request nodes).
A more efficient way would be, to derive the required data from the mqtt topic. So you would be able to store an unlimited number of bme sensors with only 3 nodes.

Here is an example - looks a little weird, cause i extracted it from a bigger flow (you need to have the node "node-red-contrib-influxdb" installed):

Code: Select all

[{"id":"101caa2c.161c66","type":"mqtt in","z":"c421dfeb.3e752","name":"","topic":"red/event/#","qos":"0","broker":"e36a332b.c61f3","x":243,"y":2214,"wires":[["b3315bcf.c85c88"]]},{"id":"b3315bcf.c85c88","type":"json","z":"c421dfeb.3e752","name":"","x":376,"y":2223,"wires":[["fbd0ec8.0bb921"]]},{"id":"fbd0ec8.0bb921","type":"change","z":"c421dfeb.3e752","name":"prep","rules":[{"t":"set","p":"payload.id","pt":"msg","to":"undefined","tot":"jsonata"},{"t":"set","p":"payload.name","pt":"msg","to":"undefined","tot":"jsonata"},{"t":"set","p":"device","pt":"msg","to":"$split(msg.topic, \"/\")[4]","tot":"jsonata"},{"t":"set","p":"measurement","pt":"msg","to":"event","tot":"str"},{"t":"set","p":"system","pt":"msg","to":"$split(msg.topic, \"/\")[3]","tot":"jsonata"},{"t":"set","p":"payload.system","pt":"msg","to":"undefined","tot":"jsonata"},{"t":"set","p":"type","pt":"msg","to":"$split(msg.topic, \"/\")[2]","tot":"jsonata"},{"t":"set","p":"payload.label","pt":"msg","to":"undefined","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":517,"y":2247,"wires":[["a9693cdf.3db24"]]},{"id":"a9693cdf.3db24","type":"function","z":"c421dfeb.3e752","name":"prepare data","func":"newmsg = {}\nnewmsg.payload = {}\nnewmsg.measurement = msg.measurement;\n\nif (typeof msg.payload === \"object\") {\n    for (var key in msg.payload) {\n        if (isNumeric(msg.payload[key]) && msg.measurement != \"event\") {\n             newmsg.payload[key] = parseFloat(msg.payload[key]);\n        } else {\n            newmsg.payload[key] = msg.payload[key];\n        }\n    }\n} else {\n    if (isNumeric(msg.payload[key]) && msg.measurement != \"event\") {\n         newmsg.payload.value = parseFloat(msg.payload);\n    } else {\n        newmsg.payload.value = msg.payload;\n    }\n}\n\n//add tag \"Device\"\nnewmsg.payload = [ newmsg.payload, { \"Device\": msg.device} ];\n//add tag \"Beacon\"\nif (msg.beacon !== undefined)\n    newmsg.payload[1].Beacon = msg.beacon;\n//add tag \"Location\"    \nif (msg.location !== undefined)\n    newmsg.payload[1].Location = msg.location;\n//add tag \"function\"    \nif (msg.function !== undefined)\n    newmsg.payload[1].Function = msg.function;\n//add tag \"system\"    \nif (msg.system !== undefined)\n    newmsg.payload[1].system = msg.system;\n//add tag \"type\"    \nif (msg.type !== undefined)\n    newmsg.payload[1].type = msg.type;    \n    \nreturn newmsg;\n\nfunction isNumeric(n) {\n  return !isNaN(parseFloat(n)) && isFinite(n);\n}\n","outputs":"1","noerr":0,"x":694,"y":2275,"wires":[["4e0f7466.7ef02c"]]},{"id":"4e0f7466.7ef02c","type":"influxdb out","z":"c421dfeb.3e752","influxdb":"970191b.79d187","name":"","measurement":"","precision":"","retentionPolicy":"","x":872,"y":2300,"wires":[]},{"id":"e36a332b.c61f3","type":"mqtt-broker","z":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""},{"id":"970191b.79d187","type":"influxdb","z":"","hostname":"192.168.4.240","port":"8086","protocol":"http","database":"infra","name":"opi_infra","usetls":false,"tls":""}]

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 11 Aug 2018, 19:53
by water
spachal wrote: ↑09 Aug 2018, 11:12
water wrote: ↑07 Aug 2018, 13:12 Can you tell me how you used node-red mqtt and influxdb ? HTTP Advanced is indeed to unstable :)
Hi, here is sample float of my solution, for BME280. In espeasy I send mqtt message to broker (rasppi+mosquito, IP 192.168.166.177), node-red reads these payloads and writes them to DB.
For some reason I can't import this flow?

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 12:53
by moebius
@water: the flow from @spachal is in quotes instead of code tags and the forum modifies such strings somehow.

You could also try to reconstruct his flow - only the 3 nodes: mqtt -> function -> request. Code and settings you can see in his export.

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 13:03
by water
moebius wrote: ↑09 Aug 2018, 13:23 @drum: for more data, i would vote for influxdb (the built-in downsampling ability is really nice) in combination with grafana.
Also, it's not common to save everything in one table or to store aggregated/min/max values (usually done with queries).

@spachal: Nice sample for beginners.
Consider that, if you have more sensors, it will soon get confusing and hard to maintain (eg: if the ip address changes, you have to edit all request nodes).
A more efficient way would be, to derive the required data from the mqtt topic. So you would be able to store an unlimited number of bme sensors with only 3 nodes.

Here is an example
Cool, this works in my case. But I want to sort the data a bit. How can I adjust your settings so I can use this in grafana SELECT mean("value") FROM "dust sensor" WHERE ("DHT22" = 'Humidity') AND $timeFilter GROUP BY time($__interval) fill(null)

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 13:44
by moebius
@water: there is nothing to sort, when you store data in the database
Also your sql select is a little confusing, cause you have the humidity data from a dht22 in the table "dust sensor".

In order to help you, you should export your current flow and describe exactly what you want to achieve.

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 13:50
by water
moebius wrote: ↑12 Aug 2018, 13:44 @water: there is nothing to sort, when you store data in the database
Also your sql select is a little confusing, cause you have the humidity data from a dht22 in the table "dust sensor".

In order to help you, you should export your current flow and describe exactly what you want to achieve.
Well, in grafana I want to get data from 'device 1' which has 2 sensors. The DHT22 and SDS011. SELECT mean("value") FROM "Device1" WHERE ("DHT22" = 'Humidity') AND $timeFilter GROUP BY time($__interval) fill(null)

maybe this is a little less confusing. From Device1 I want to make a graph from DHT22 to show the humidity. And in an other graph I want to select the temperature from DHT22 from device1

when I use your flow I can only use this in Grafana SELECT mean("value") FROM "Device1" WHERE ("system" = 'Temperature') AND $timeFilter GROUP BY time($__interval) fill(none)
What I want to achieve is that 'system' will change to the sensorname like DHT22, SDS011

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 14:09
by moebius
If you use my flow to store the data without modification, you have to adjust the mqtt topic (cause this defines most parameters for the influxdb insert statement).
If you cannot change the mqtt topic, you have to adapt the change node to your needs...

Maybe you can you provide a sample message...

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 14:22
by spachal
moebius wrote: ↑12 Aug 2018, 12:53 @water: the flow from @spachal is in quotes instead of code tags and the forum modifies such strings somehow.

You could also try to reconstruct his flow - only the 3 nodes: mqtt -> function -> request. Code and settings you can see in his export.
My bed, sorry for that, this is export of temperarure part only, in code:

Code: Select all

[{"id":"c854d5dd.78c568","type":"mqtt in","z":"a458e657.149bd8","name":"","topic":"/esp08test/bme280/Temperature","qos":"2","broker":"18ae8bba.64bfd4","x":210,"y":60,"wires":[["caef4d9b.7a6de"]]},{"id":"caef4d9b.7a6de","type":"function","z":"a458e657.149bd8","name":"MQTT to InfluxDB","func":"var tokens = msg.topic.split(\"/\");\nvar dest = tokens[tokens.length-1];\nmsg.url =\"http://192.168.166.177:8086/write?db=senzory\";\nmsg.payload = \"bme280temperature,device=\" + tokens[1] + \" value=\" + msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":510,"y":60,"wires":[["b8b60563.7da688"]]},{"id":"b8b60563.7da688","type":"http request","z":"a458e657.149bd8","name":"","method":"POST","ret":"txt","url":"http://192.168.166.177:8086/write?db=senzory","tls":"","x":890,"y":60,"wires":[[]]},{"id":"18ae8bba.64bfd4","type":"mqtt-broker","z":"","name":"","broker":"192.168.166.177","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]
I know that this is only very simple example, but I don't need any pro solution, I'm the beginner and my project is pretty simple too :] ..

In Grafana you can define data source and use data stored in ifluxdb for your graphs. Here is my example of metioned BME280 sensor:
Image

Great post about grafana ans inluxdb (for openhab) is here: https://community.openhab.org/t/influxd ... hing/13761

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 12 Aug 2018, 15:47
by water
spachal wrote: ↑12 Aug 2018, 14:22
moebius wrote: ↑12 Aug 2018, 12:53 @water: the flow from @spachal is in quotes instead of code tags and the forum modifies such strings somehow.

You could also try to reconstruct his flow - only the 3 nodes: mqtt -> function -> request. Code and settings you can see in his export.
Great post about grafana ans inluxdb (for openhab) is here: https://community.openhab.org/t/influxd ... hing/13761
Thanks. Im almost there
Do you know how I can replace make the next line
msg.payload = tokens[2] + ",measurement=" + tokens[3] + ",device=" + tokens[1] + " value=" + msg.payload;
to
msg.payload = tokens[1] + ",tokens[2]=" + tokens[3] + " value=" + msg.payload; <-- this one doesn't work but I haven't figured out how I can use tokens between quotes
Got it. I´ve used msg.payload = tokens[1] + "," + tokens[2] + "=" + tokens[3] + " value=" + msg.payload;

This is my flow and all works :) thanks

Code: Select all

[{"id":"2b0d8d3b.42b4b2","type":"mosca in","z":"56cdfe17.c0006","mqtt_port":1883,"mqtt_ws_port":8080,"name":"","username":"","password":"","dburl":"","x":670,"y":1280,"wires":[["8103799d.e68a78"]]},{"id":"8103799d.e68a78","type":"debug","z":"56cdfe17.c0006","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":870,"y":1280,"wires":[]},{"id":"1ba5a1e.79e5b5e","type":"mqtt in","z":"56cdfe17.c0006","name":"DHT22","topic":"/Stofzuiger/#","qos":"2","broker":"c807f5b7.930c28","x":630,"y":1340,"wires":[["2a67beee.8d3eb2"]]},{"id":"1bba56f2.6a8bf9","type":"debug","z":"56cdfe17.c0006","name":"debuginfo","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1040,"y":1420,"wires":[]},{"id":"2a67beee.8d3eb2","type":"function","z":"56cdfe17.c0006","name":"MQTT to InfluxDB","func":"var tokens = msg.topic.split(\"/\");\nvar dest = tokens[tokens.length-1];\nmsg.url =\"http://xxx.xx.18.133:8086/write?db=test\";\nmsg.payload = tokens[1] + \",\" + tokens[2] + \"=\" + tokens[3] + \" value=\" + msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":830,"y":1340,"wires":[["3d87de37.627cd2","1bba56f2.6a8bf9"]]},{"id":"3d87de37.627cd2","type":"http request","z":"56cdfe17.c0006","name":"","method":"POST","ret":"txt","url":"http://xxx.xx.18.133:8086/write?db=test","tls":"","x":1050,"y":1340,"wires":[[]]},{"id":"c807f5b7.930c28","type":"mqtt-broker","z":"","name":"","broker":"xxx.xx.18.133","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""}]

Re: Writing to InfluxDB from HTTP Advanced controller?

Posted: 14 Apr 2019, 12:38
by Ian
Hello,

I wish to do the same as @water, mqtt > function but with the influxDB node.

Would someone happen to have a flow including the function or explain how for this, I'm not too hot on Javascript?

Thanks
All times are UTC+02:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited

American English Language Pack © Maël Soucaze