NodeRed to InfluxDB, unable to set 'tags'

Hi All,
I'm new here but have spent the last few days attempting to write data to InfluxDB. I have successfully written data to the InfluxDB, but not in the format that I'd like. I am trying to use 'tags' in the 'Measurements & Tags' part of the database, but have been singularly unsuccessful. I have surfed the internet and both this website and InfluxDB and Grafana, but the closest I've come to seeing something address this is this discord where at the end 'ventzitt' says his next step is to do exactly what I want, only he didn't post anything anywhere after that.

I'm using a Pi4 with HA o/s with the following
HA 2023.3.3
InfluxDB 4.5.0
Grafana 8.2.0
Node-Red 14.0.4

This is the sort of thing I want to get (sub-levels aka tags):-
image

And this is what I am currently getting (no sub-levels):-
image

If anyone has any pointers, I would be extremely grateful.

Welcome to the forum @ChrisG

Are you using the Influxdb Out from node-red-contrib-influxdb to write to the database. If so then show us the format of the data you are writing, and tell us exactly what you want to achieve, in terms of fields and tags.

Hi Colin,

to the first, yes.

Two inserts to provide values differently (this is the lower one):-
<solar id='44371900527C'><timestamp>1678397251</timestamp><current><generating units='w'>0.00</generating><exporting units='w'>0.00</exporting></current><day><generated units='wh'>1906.11</generated><exported units='wh'>0.00</exported></day></solar>
This data is how it is sent via UDP from an Owl Intuition.
The switch node was copied from a flow I found on this web site which filters only the message type I want to capture (although once I've sorted how the InfluxDB is populated I will be adding the other message type).
The xml node is something I added as by-passing it causes an error at the function node

The function node sets up the InfluxDB measurement and formats the json payload (which I check with an online json script validator.

msg.payload = `{ "Fields": [{"generating": [{ "_": "657.00", "tags": { "units": "w" } }], "exporting": [{ "_": "${global.get("currentGeneratingUnitsW")}", "tags": { "units": "w" } }] }, { "tags": { "Solar": "Current" } }] }`;
//msg.payload = `{"Fields":[{"generating":[{"_":"657.00","tags":{"units":"w"}}],"exporting":[{"_":"${global.get("currentGeneratingUnitsW")}","tags":{"units":"w"}}]},{"tags":{"Solar":"Current"}}]}`;
msg.measurement = `Watts3`;

return msg;

And while composing this response, I was double checking each step and discovered that the whole msg.payload is being written 'as is' to the value field in InfluxDB which I hadn't noticed because I was in graphic (line graph) view.

The function node feeds the 'Influxdb Out' node which works as its a copy of an identical node that is capturing live data which I can view in InfluxDB but can't seem to manilupate in Grafana as it doesn't have the tags, hence this post.

The function node also feeds two debug nodes, one configured for showing msg.payload and the other the 'complete message object'.

The json node was an attempt to see what would happen with that in the flow, but it caused errors.

The result of running the flow is:-


As you can see, the mesasurement Watts3 is created and a field value (which I now realize is holding the rest of the script as text), but does show the Influx Out node is successfully interacting with InfluxDB. (Each test I increment the number , so Watts3 becomes Watts4 and after a few attemptes I delete the WattsX measurements leaving just the 'stations' which is holding live data at the moment)

I've got something now to look at, but I'm surprised that there doesn't seem to be a 'something' under the documentation on how to configure data being sent to InfluxDB to populate the tags.

Oh, and if anyone is wondering why I'm attempting this rather than using the Owl Intuition in HA, I already do for my own home Owl Intuition, what I'm trying to do here is feed another Owl Intuition in from a friends home. And having just mentioned that think I might be able to get a second instance there, but the ability to set tags in InfluxDB using Node-red is still something I want to get work.

That doesn't appear to correspond to any of the payload formats described in the node's help text. If you want to write a value with fields and tags you should be using the format described there by

If msg.payload is an array containing two objects, the first object will be written as the set of named fields, the second is the set of named tags.

So you need to format the payload like

[
  {
    field1: value1,
    field2: value2,
    ...
  },
  {
    tag1: "tag value 1",
    tag2: "tag value 2,
    ...
  }
]

where field1, field2, tag1 etc are the field and tag names.

Hi Colin,

Can you point me in the direction of the 'node's help text' please.

I've seen the supposed help under the Node-red node info page, and I've been to various other sources for how to format messages, but nothing I've found explains what you need to configure to achieve something specific.

I'm a hobbyist, and usually the web helps me with what I want to do (RaspPi, Arduino, scripting in perl/c/php/html/vbs/etc) with examples that I can modify or adapt.

But json/objects/node-red/influx seems to me to expect someone to have a much higher level of understanding than I have. Or I'm finally getting to the age of not being able to learn new stuff (I hope not).

Click on the node, then on the book tab in the right hand pane

Also, if you search for the on https://flows.nodered.org you will find there many useful examples.

It just takes a bit of time to get used to. Persevere and all will become clear (or at least less muddy). Have you watched the Node-RED Essentials videos? If not then do that. Pay particular attention to the section Working with Messages and look at the matching page in the docs for help with understanding message formats.

Influxdb itself is fairly complex, version 2 even more than 1.8, that is nothing to do with node red of course.

Hi Colin, the print you've posted is what I saw and the highlighted bit may explain what is needed, but I need something much more basic. As mentioned, my working knowledge around json and objects is somewhere between none and minimal.

I've seen the page providing the essentials video's and watched parts of a few, but only to try and find specifics on what I'm looking for, not to become proficient, although I might have to do that, (yet another 'string to my bow' of things I know something about, but won't use that often). :frowning: I did pretty much watch the working with messages, but from recollection, didn't cover how that works with InfluxDB specifically, so wasn't as much help as I wouild have liked/expected.

Hopefully this afternoon, I can follow up on the point I saw this morning regarding the string of info being written as a text value rather than individual components of an InfluxDB data point with tags.

I gave you an example of how to format the data.

I could help further with the reformatting but I don't understand what this data represents

Can you decode that for me? What are the field names and values and what are the tag names and values. You can't have arrays or hierarchical data in a Measurement, it must be just a set of fields and tags.

And that probably better describes my lack of understanding when we're not even on the 'same page'.

I've since found that the live data (a different flow) is actually putting the data I require in InfluxDB (in the 'stations') measurement(/tag) and can by a different method be pulled by Grafana:-


The red circle is where for ALL OTHER (so far) queries I've done is where the data point is selected from, not the select line on the line below. Of course, it might be that 'this' way is the correct method and the way I've been doing it elsewhere is the wrong/poor way of doing it.

I know the data that is coming in, it's simply 4 values representing 4 completely seperate bits of data. But I was hoping to break it down (under Measurement & tags) to those values that are 'instant' values (two of) and those that are daily cumulative values that start at 0 at Mdt and is max by 23:59.

decoded msg.payload:-


Strange but I've also just noticed I'm getting two messages for debug13, one showing the above and another showing it all as text , yet the debug13 node only has one feed, need to look at that again.

And full message object:-

{
    "_msgid": "795ac5bdd3f56a48",
    "payload": "{ \"Fields\": [{\"generating\": [{ \"_\": \"657.00\", \"tags\": { \"units\": \"w\" } }], \"exporting\": [{ \"_\": \"933\", \"tags\": { \"units\": \"w\" } }] }, { \"tags\": { \"Solar\": \"Current\" } }] }",
    "topic": "",
    "measurement": "Watts3"
}

which now that i've seen what has appeared in the InfluxDB value field clearly shows what I'm sending is rubbish.

I'm going to pause here and redo the flow with fresh nodes.

So out of that payload out of the xml node, which values do you want as fields in the database, and what strings do you want as tags?

Is the timestamp basically 'now'? If so then you don't need that. Influxdb will add the current time for you automatically.

the xml gives the following, which was posted in my last post.

{"_msgid":"e84d916cd1129de5","payload":{"solar":{"tag":{"id":"44371900600D"},"timestamp":["1678380208"],"current":[{"generating":[{"_":"657.00","tag":{"units":"w"}}],"exporting":[{"_":"0.00","tag":{"units":"w"}}]}],"day":[{"generated":[{"_":"6512.99","tag":{"units":"wh"}}],"exported":[{"_":"1062.67","tag":{"units":"wh"}}]}]}},"topic":""}

Yes the timestamp is 'now' and isn't carried forward out of the function node, so is relying on InfluxDB giving it the timestamp of when it arrives, for the sake of a few 100's of a second, either way would surfice.

It looks like the xml output is formatted in a way that seems to be correct but fails on the InfluxDB Out node due to not having a measurement set. I was setting it in the function node.

Changing the function to not play with the payload, so just has msg.measurement = "Wattsx"; shows an output of the xml followed by the measurement value. But this fails more abysmally than before in so much as not even creating the measurement entry.

I can't help if you don't answer the questions.

In answer to your request above.

This is the data as received from external:-

<solar id='44371900600D'><timestamp>1678380208</timestamp><current><generating units='w'>657.00</generating><exporting units='w'>0.00</exporting></current><day><generated units='wh'>6512.99</generated><exported units='wh'>1062.67</exported></day></solar>

this is the data as (would be) delivered to the InfluxDB node (and not working).

{"_msgid":"d34b19b847b2b8ce","payload":{"solar":{"tag":{"id":"44371900600D"},"timestamp":["1678380208"],"current":[{"generating":[{"_":"657.00","tag":{"units":"w"}}],"exporting":[{"_":"0.00","tag":{"units":"w"}}]}],"day":[{"generated":[{"_":"6512.99","tag":{"units":"wh"}}],"exported":[{"_":"1062.67","tag":{"units":"wh"}}]}]}},"topic":""}

The values are obvious:-
"657.00"
"0.00"
"6512.99"
"1062.67"

the format I haven't decided upon as yet, and once I can get the tags created and the data into the right tags, I can then play around with the exact finish afterwards before moving this little exercise into 'live running'.

For other things in InfluxDB, the top level tag seems to be W (for Watts) and Wh (for WattHours). Then probably Current and Day, so I'm initially thinking Measures and tags along the lines of:-
Measure/tag/tag/tag/ field
Solar/W/Current/Generating/ value/last
Solar/W/Current/Exporting/ value/last
Solar/Wh/Day/Generated/ value/last
Solar/Wh/Day/Exported/ value/last

What field names do you want against those? Each value has to be stored in the db with a field name. It might be "generated" for example.

Sorry, I have no idea what that is trying to convey. Each record in the db is a set of fields and tags. We have determined that the values of the fields are as above. So with that record what are the associated tags, if any. Tags are often things like "sensor_id" or a location such as "kitchen". Tags allow you to do things like selecting the "generated" field values over a time range, where the tag sensor_id is "whatever". Looking at your data you only seem to have data from one system so I am not sure that you have any tags.
An example of the usual use of tags is where one might have temperature and humidity for a number of rooms. Then the fields would be temperature and humidity and the room might be a tag. So you can then select the temperature over a time range where the room is kitchen.

Looking at what you have provided a first stab would be;

let data = msg.payload

let currentGenerating = parseFloat(data.payload.solar.current[0].generating[0]['_'])
let currentExporting = parseFloat(data.payload.solar.current[0].exporting[0]['_'])
let dayGenerated = parseFloat(data.payload.solar.day[0].generated[0]['_'])
let dayExported = parseFloat(data.payload.solar.day[0].exported[0]['_'])

let measurement = 'solar'

[
   [ {
        "value": currentGenerating,
    },
    {
        "power": "generated",
        "period": "current"
    } ],
   [ {
        "value": currentExporting,
    },
    {
        "power": "exported",
       "period": "current"
    } ],
   [ {
        "value": currentGenerated,
    },
    {
        "power": "generated",
        "period": "day"
    } ],
   [ {
        "value": currentExported,
    },
    {
        "power": "exported",
       "period": "day"
    } ],

]

Hopefully you can expand on this so that Colin can give a more informed answer than my suggestion as I am not sure that it will even work.

Hi Buckskin,

thanks for this, I'll see what I can do with it.
I stopped updating here as I was completely lost, so decided to try and build it piece meal, by trying to do a single data point, with a single tag, still trying to get that to work. Also starting looking at InfluxDB forums and postings, but nothing has presented itself yet. :frowning:

I'm almost at the point of binning the InfluxDB node method and concocting a http post that I know works the way I want it to as I use it on my Arduino's. I'll probably find the http request node will have 'new' quirks for me. :cry:

regards

Following on from @Buckskin's suggestion, that will give a database with records containing field value with tags power and period and would write four consecutive records containing the data from msg.payload.

A couple of minor issues there. I think it should be
msg.measurement = "solar"

though if it is always that going to the node, you could put that in the node config instead.
Also you need
msg.payload = [...
and return msg at the end.

An alternative would be to have four fields containing the values and no tags.

msg.payload = {
  currentGenerating: currentGenerating,
  currentExporting:  currentExporting,
  dayGenerated: currentGenerated,
  dayExported: currentExported
}

Since all four values come in together I would probably use the latter method.
Also consider how you will use the data. If you are likely to want all four at once (or more than one at a time) then it would probably be more efficient to put them all in one record. If you will generally fetch only one at a time then separate them.
Then of course there is a half way option, where you could keep generating and exporting together in a record, but use a tag to say current or day.

Hi Guys,

Thanks for your responses, afraid they didn't solve the issue for me, but I finally looked at the node's help and loaded the flows that were there. Could have saved a lot of hours on my part and a few on your part if only I'd 'rtfm' (engineers term - read the f***ing manual).

It's now doing what I wanted (pretty much) and I'll post an update here with suitable screen shots shortly, showing the code and the resulting InfluxDB view.

Sending data from Node-Red to InfluxDB

This post was started because I was unable to get data from Node-Red into InfluxDB in the format that I wanted. With some pointers provided in the post I still wasn’t able to get the coding necessary to get the data into InfluxDB in the format I was after. Although I had (sort of) read the Node-Red info for ‘InfluxDB Out’ node, it didn’t seem to provide the information I was seeking, but eventually I found it did have it, just not in a sufficiently basic manner of an Idiot’s Guide that I would have liked.

My browsing and looking at flows I found in other forums got me a basic method of writing data into InfluxDB, but not using ‘tags’. Reading the ‘InfluxDB Out’ node info, the text didn’t help, but the flows provided once loaded allowed me to play with a working example of writing data to InfluxDB with tags and from there I was able to write my data the way I wanted to.

So, my Idiot’s Guide.

To write data, you need to populate the InfluxDB Out node with some details, only one is optional. The none optional details are:-

Server, which requires dB version, dB url, Username and Password

Database name

The optional detail:-

Measurement, this has to be provided, but it can be done either here in the InfluxDB Out node, or as a variable set earlier in the flow by another node (msg.measurement = “”;), this latter method allows the measurement to be changed from one set of data to the next based on data being fed in.

All of the above is straight forward.

Scenario 1, Writing a single data value without tags:-

The following flow, simply writes a random number to a field in database ‘dB1’ and measurement ‘test’.
image

[{"id":"1cd3d3436ac99b73","type":"tab","label":"Flow 2","disabled":false,"info":"","env":[]},{"id":"80cc26f1225bcff7","type":"influxdb out","z":"1cd3d3436ac99b73","influxdb":"7111b0228e82b576","name":"Single Value","measurement":"_test","precision":"","retentionPolicy":"","database":"dB1","retentionPolicyV18Flux":"","org":"","bucket":"","x":590,"y":120,"wires":[]},{"id":"c8b314fe7ab955e3","type":"function","z":"1cd3d3436ac99b73","name":"single value","func":"msg.payload = Math.random()*10;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":120,"wires":[["80cc26f1225bcff7"]]},{"id":"f406b8bfc2f56bd6","type":"inject","z":"1cd3d3436ac99b73","name":"","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"str"}],"repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"date","x":280,"y":120,"wires":[["c8b314fe7ab955e3"]]},{"id":"7111b0228e82b576","type":"influxdb","hostname":"homeassistant.local","port":"8086","protocol":"http","database":"user","name":"","usetls":false,"tls":"","influxdbVersion":"1.8-flux","url":"http://homeassistant.local:8086","rejectUnauthorized":false}]

InfluxDB before running the above:-

InfluxDB after running the above:-

The measure ‘_test’ is created and a field ‘value’, and there are no ‘tags’.

Scenario 2, Writing a single data value with a tag:-
image

The following flow, writes a random number to a field in database ‘dB1’ and measurement ‘test’, but with two tags.

[{"id":"80cc26f1225bcff7","type":"influxdb out","z":"1cd3d3436ac99b73","influxdb":"7111b0228e82b576","name":"Single Value with tag","measurement":"_test","precision":"","retentionPolicy":"","database":"DB1","retentionPolicyV18Flux":"","org":"","bucket":"","x":680,"y":300,"wires":[]},{"id":"c3b7b127909164ce","type":"inject","z":"1cd3d3436ac99b73","name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"","payloadType":"date","x":280,"y":300,"wires":[["addbfbfef2286103"]]},{"id":"addbfbfef2286103","type":"function","z":"1cd3d3436ac99b73","name":"Fields and Tags","func":"msg.payload = [{\n    Value1: Math.random()*10\n},\n{\n    tag1:\"tag1-name\",\n    tag2:\"tag2-name\"\n}];\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":300,"wires":[["80cc26f1225bcff7"]]},{"id":"7111b0228e82b576","type":"influxdb","hostname":"homeassistant.local","port":"8086","protocol":"http","database":"CaponGJ","name":"","usetls":false,"tls":"","influxdbVersion":"1.8-flux","url":"http://homeassistant.local:8086","rejectUnauthorized":false}]

InfluxDB before running the above:-

InfluxDB after running the above:-

The measure ‘_test’ is created and a field ‘value’, with two 'tags'.

The code for creating this is:-

msg.payload = 
[
{
    Value1: Math.random()*10
},
{
    tag1:"tag1-name",
    tag2:"tag2-name"
}
];

return msg;

If you want multiple values with tags, then simply take the above, as a block of code, stick a comma after the closing square bracket and wrap the whole lot up in another pair of square brackets:-

msg.payload = 
[		//(new)
//----------------------------------------------  1st value
[
{
    Value1: Math.random()*10
},
{
    tag1:"tag1-name",
    tag2:"tag2-name"
}
],	// stick the comma on the end of this original block
		// from here down is a repeat of the above
//----------------------------------------------  2nd value
[
{
    Value2: Math.random()*15
},
{
    tag1:"tag1-name",
    tag3:"tag3-name"
}
]	// this is the end of the second block
]	// and the final closing square bracket to match the first
;

which gives this:-

Hopefully, this is of help to other newbies.
:slight_smile:

3 Likes