Getting data into InfluxDB

First off, I was originally going to use SQLite, but the size of my DB was going to get ugly, so I've switched over to InfluxDB with Grafana and Telegraf.

I've install the InfluxDB npn to node red but I've also configured Telegraf to load my db via MQTT.

The problem I'm having is how to format my final output, either straight to Influx via the InfluxDB node or via MQTT.

My measurement is Bin Levels. My tag location is Bin 1 (will have other locations coming online shortly), my fields are Raw Data, Signal Strength and Bushels (after a conversion).

I have my output configured with msg.measurement = Bin_Levels, and msg.payload.Bin = SomeLocation, msg.payload.RawValue = SomeValue, msg.payload.SignalStrength = AnotherValue, msg.payload.Bushels = YetAnotherValue.

What is the best method to get it into InfluxDB, and how do I format it for the method. The instructions for the InfluxDB node don't help at all ( if msg.payload is an array of arrays, it will be written as a series of points containing fields and tags). It doesn't say how the array of arrays should be configured for my set of data.

If it's easier to set up for an MQTT publish node, I'm all for that, too. I just don't know how the output needs to be configured either way to get the data into the DB.

Thanks in advance for any help.

:question: :question: :question:

Here's my debug write showing the entire message output:

image

Here is the flow getting to here:

[{"id":"dc111c1e.b0521","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":970,"y":1020,"wires":[]},{"id":"4bc0f07.4f2e41","type":"range","z":"ff6f3b86.a45ac8","minin":"0","maxin":"100","minout":"0","maxout":"35000","action":"scale","round":true,"property":"payload.Bushels","name":"Conv. Bushels","x":560,"y":960,"wires":[["e958f7ff.657658","dc111c1e.b0521"]]},{"id":"e0167e35.07088","type":"range","z":"ff6f3b86.a45ac8","minin":"300","maxin":"25","minout":"0","maxout":"100","action":"scale","round":true,"property":"payload.Bushels","name":"Scale","x":410,"y":960,"wires":[["4bc0f07.4f2e41"]]},{"id":"6783c7ee.50fb38","type":"change","z":"ff6f3b86.a45ac8","name":"ChangeFormat","rules":[{"t":"set","p":"measurement","pt":"msg","to":"Bin_Data","tot":"str"},{"t":"set","p":"payload.Bin","pt":"msg","to":"Bin 1","tot":"str"},{"t":"set","p":"payload.Raw_Value","pt":"msg","to":"payload.bl","tot":"msg"},{"t":"set","p":"payload.Signal_Strength","pt":"msg","to":"payload.ss","tot":"msg"},{"t":"set","p":"payload.Bushels","pt":"msg","to":"payload.bl","tot":"msg"},{"t":"delete","p":"payload.bl","pt":"msg"},{"t":"delete","p":"payload.ss","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":960,"wires":[["e0167e35.07088"]]},{"id":"d63b64d0.8edb88","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin 1 Level","topic":"Bins/Bin1/LevelData","qos":"0","datatype":"json","broker":"5d86e7b6.6ca968","x":80,"y":960,"wires":[["f1a2222e.9c662","26031f4c.ddcf1","6783c7ee.50fb38"]]},{"id":"5d86e7b6.6ca968","type":"mqtt-broker","name":"Bins Broker","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"Bins/Server/Status/Comm","birthQos":"2","birthRetain":"true","birthPayload":"Connected","closeTopic":"Bins/Server/Status/Comm","closeQos":"2","closeRetain":"true","closePayload":"Disconnected","willTopic":"Bins/Server/Status/Comm","willQos":"2","willRetain":"true","willPayload":"Unexpected Disconnection"}]

I think what's giving me confusion is the InfluxDB documentation for an InfluxDB data type:

measurement, tag-set field1-set1, field2-set2, field3-set3 timestamp

I'll let Influx set the timestamp; but the missing comma between tag-set and field1-set1 is missing in their documentation so that is what makes it difficult for me to understand the formatting.

If I use the InfluxDB node to ship the data, measurement is a non-issue, but how do I format the tag-set (location) and fields-sets?

I know I'm VERY close, but I cant find any example data for this either for InfluxDB node or MQTT setup.

Marshall

I am a little closer... I have the Tag-Value set going into the DB (Bin_1), but no measurements. I've updated the function... here's the debug, flow and screenshot of the DB...

image

[{"id":"dc111c1e.b0521","type":"debug","z":"ff6f3b86.a45ac8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":950,"y":1120,"wires":[]},{"id":"56fad3bc.ab3a6c","type":"function","z":"ff6f3b86.a45ac8","name":"InfluxDB Format","func":"var msg1={};\n\nmsg1.payload = [\n        \n        {\n            RawValue: msg.payload.RawValue,\n            SigStrength: msg.payload.SigStrength,\n            CurrentBushels: msg.payload.CurrentBushels\n            },\n        {\n            Bin: msg.payload.Bin\n            }\n];\n\n\nreturn msg1;\n\n// If msg.payload is an array containing two objects,\n// the first object will be written as the set of named\n// fields, the second is the set of named tags.","outputs":1,"noerr":0,"initialize":"","finalize":"","x":740,"y":960,"wires":[["dc111c1e.b0521"]]},{"id":"4bc0f07.4f2e41","type":"range","z":"ff6f3b86.a45ac8","minin":"0","maxin":"100","minout":"0","maxout":"35000","action":"scale","round":true,"property":"payload.CurrentBushels","name":"Conv. Bushels","x":560,"y":960,"wires":[["56fad3bc.ab3a6c"]]},{"id":"e0167e35.07088","type":"range","z":"ff6f3b86.a45ac8","minin":"300","maxin":"25","minout":"0","maxout":"100","action":"scale","round":true,"property":"payload.CurrentBushels","name":"Scale","x":410,"y":960,"wires":[["4bc0f07.4f2e41"]]},{"id":"6783c7ee.50fb38","type":"change","z":"ff6f3b86.a45ac8","name":"ChangeFormat","rules":[{"t":"set","p":"payload.Bin","pt":"msg","to":"Bin_1","tot":"str"},{"t":"set","p":"payload.RawValue","pt":"msg","to":"payload.bl","tot":"msg"},{"t":"set","p":"payload.SigStrength","pt":"msg","to":"payload.ss","tot":"msg"},{"t":"set","p":"payload.CurrentBushels","pt":"msg","to":"payload.bl","tot":"msg"},{"t":"delete","p":"payload.bl","pt":"msg"},{"t":"delete","p":"payload.ss","pt":"msg"},{"t":"delete","p":"topic","pt":"msg"},{"t":"delete","p":"qos","pt":"msg"},{"t":"delete","p":"retain","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":960,"wires":[["e0167e35.07088"]]},{"id":"d63b64d0.8edb88","type":"mqtt in","z":"ff6f3b86.a45ac8","name":"Bin 1 Level","topic":"Bins/Bin1/LevelData","qos":"0","datatype":"json","broker":"5d86e7b6.6ca968","x":80,"y":960,"wires":[["f1a2222e.9c662","26031f4c.ddcf1","6783c7ee.50fb38"]]},{"id":"5d86e7b6.6ca968","type":"mqtt-broker","name":"Bins Broker","broker":"localhost","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"Bins/Server/Status/Comm","birthQos":"2","birthRetain":"true","birthPayload":"Connected","closeTopic":"Bins/Server/Status/Comm","closeQos":"2","closeRetain":"true","closePayload":"Disconnected","willTopic":"Bins/Server/Status/Comm","willQos":"2","willRetain":"true","willPayload":"Unexpected Disconnection"}]

image

I think I don't understand how to configure the field object properly in my function node.

ANY help I can get, I'll be grateful.

Thanks in advance.

Marshall

I used the instructions in this video which use only NR, Influx and Grafana (no Telegraf). It works perfectly if you follow the instructions in the video.

Your payload looks correct. Are you setting the measurement in the Influx out node? Assuming that is Bin_Levels what do you get if in the cli you say
use BinLevels
select * from Bin_Levels

Ahh! They're there!

I checked using the DB name, not the measurement name!

Now I feel VERY silly... that's one problem with using similar names for different things.

I spent HOURS on this last night trying to figure out what was wrong, changing things up slightly.

Thanks for your help, Colin and Grant!

Marshall

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.