I have a situation where I receive MQTT messages containing the ID of the device forwarding the data and some measured value from the sensors.
I am attempting to store the measured values in a "Measurement" named like the device.
So, If I receive a message like "DEV1/something123" I will store "Something123" in Measurement "DEV1".
I see there is a filed in that node to define the Measurement but I don't have idea on how to feed that field without manually writing into it.
Is there a way to programmatically define the Measurement to use in InfluxDb-Out node?
I am trying avoiding having to use Python to achieve this.
I understand your point. It is clear. And thanks for showing me an alternative approach.
The issue I am facing, and this is why I am trying to make one table (Measurement) for each device, is to simplify the next activity which is to select the tags in Grafana.
If I have one Measurement per device, once I have selected the measurement in Grafana, I'll have only a dozen of measures(tags) to manage inside that table (Measurement).
If I use the device name as tag and I have 1000 devices, it becomes very messy. Same when I device is replaced or deleted.
This is why I am trying to make a Measurement per device. I could use Python, but as I said, my first attempt is to verify if I can manage that into nodered.
Why would that be messy? This is what a database is designed for. If you need the data from one device you specify it in the where clause. You need all data on a measurement for all devices, use the measurement name in the where clause.
It seems like you don’t have much experience with databases (if I’m wrong, please accept my apology). I (and others) can offer help but you need to explain what you need to store and how you need to access it.
why did you choose influx as the database?
how much data are you dealing with, thousands of rows, millions?
You are right, I have no much experience with databases.
To answer your questions:
Each device sends MQTT with device ID and a dozen of measures (voltage, current, temperature, alarms, etc).
The frequency is every 5/10 seconds.
Not all devices send the exact the same measures.
Retention policy 3 months
A new device can be added/removed at any time.
Let's assume I have 100 devices in the field.
The reason to use Influx, is because it is a time-series DB and easy to use. No need to define tables and types.
Data are shown in Grafana. One user per device or multiple devices.
I do understand your approach. My idea was to create a table/measurement for each device using the device ID just to keep things easier to manage visually on the Grafana side.
Is that because you have a number of different device types? If so do all devices have a core set of data and different types have different extra data?
Correct. Some device has 12 strings of data: ID/Voltage/Current/temperature/A/B/C/8/9/19/11/12
Other devices have: ID/alarm1/alarm2/3/A/etc
The ID is always present and it is autogenerated on each device extracting the MAC address and adding some bits to it so that is recognisable and traceable.
I would have one Measurement for each device type (where all devices of that type have the same data) with the ID as a tag. That will be the most efficient way of storing and accessing the data.
That is more or less what I had in mind, a measurement per device or per type using the ID or part of it. But I would love to make it programmatically as to avoid to have to create manually a new Measurement when a new type is made.
There is a big difference between an Influx Measurement for each type, with the ID as a tag, and a Measurement for each device, where the measurement name includes the ID. With the former there will be as many Measurements as there are device types, with the latter there will be as many Measurements as there are individual devices.
Thanks for elaborating on my question. Your comments gave me some good ideas.
However, I am still eager to understand if there a way to pass the "Measurement" name to this node without having to manually type it in the tab.
Of course, as is described in the help text for the node:
"If the measurement field is not set in the node configuration, the user can send in data with a specified measurement name in msg.measurement to overwrite the measurement field in the configuration of the node."
