Writing to InfluxDB from HTTP Advanced controller?

Moderators: BertB, rtenklooster, Voyager, Stuntteam, Martinus

Message
Author
moebius
Normal user
Posts: 31
Joined: 22 May 2017, 19:36

Re: Writing to InfluxDB from HTTP Advanced controller?

#11 Post by moebius » 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 - 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":""}]
Attachments
Clipboard02.jpg
Almost whole flow
Clipboard02.jpg (163.33 KiB) Viewed 152 times
Clipboard01.jpg
Example
Clipboard01.jpg (12.35 KiB) Viewed 152 times

water
Normal user
Posts: 8
Joined: 29 Jul 2018, 19:42

Re: Writing to InfluxDB from HTTP Advanced controller?

#12 Post by water » 11 Aug 2018, 19:53

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?

moebius
Normal user
Posts: 31
Joined: 22 May 2017, 19:36

Re: Writing to InfluxDB from HTTP Advanced controller?

#13 Post by moebius » 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.

water
Normal user
Posts: 8
Joined: 29 Jul 2018, 19:42

Re: Writing to InfluxDB from HTTP Advanced controller?

#14 Post by water » 12 Aug 2018, 13:03

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)

moebius
Normal user
Posts: 31
Joined: 22 May 2017, 19:36

Re: Writing to InfluxDB from HTTP Advanced controller?

#15 Post by moebius » 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.

water
Normal user
Posts: 8
Joined: 29 Jul 2018, 19:42

Re: Writing to InfluxDB from HTTP Advanced controller?

#16 Post by water » 12 Aug 2018, 13:50

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

moebius
Normal user
Posts: 31
Joined: 22 May 2017, 19:36

Re: Writing to InfluxDB from HTTP Advanced controller?

#17 Post by moebius » 12 Aug 2018, 14:09

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...

spachal
Normal user
Posts: 38
Joined: 22 Feb 2018, 22:11

Re: Writing to InfluxDB from HTTP Advanced controller?

#18 Post by spachal » 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.
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
Please excuse my English, thanks!

water
Normal user
Posts: 8
Joined: 29 Jul 2018, 19:42

Re: Writing to InfluxDB from HTTP Advanced controller?

#19 Post by water » 12 Aug 2018, 15:47

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":""}]

Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 1 guest