How to modify the influxdb line protocol data during mqtt to influxDB point using telegraf

Question

I'm new to mqtt, time series databases and all vps, iot networks. I have setup a VPS for Ubuntu 20.04 and installed mosquitto as mqtt agent, telegraf as the bridge, influxDB as the time series database and grafana as visualizer. Was able to setup all these to get my IOT network data to store in influx and visualize with the support of Bard and ChatGPT.

The message receiving is like this (in line protocol),

measurement,deviceId="A" temperature="xx",humidity="xx"

I also made a mysql database that store the 'location' and 'area' of each device based on the device_Id.

deviceId | Location | Area
A | 101 | 1
B | 102 | 1
C | 102 | 2

Then I have tried grafana to combine these 2 databases 'influxDB" and 'mysql" to categorize the data from each location and area to visualize them using seperate gauges.

Because I could not do that, now I'm trying to change the message using telegraf before it goes to influxDB. I need to combine the mysql data to the message before it saves in influxDB like this,

measurement,deviceId="A",location="101",area="1" temperature="xx",humidity="xx"

Do you guys know an easy way to do this? or if my method is correct, please support me to do this. I'm unable to get the resolution for this anywhere.

Please find some important information below.

Ubuntu : 20.04.6 LTS (Server)
mosquitto : 1.6.9
Telegraf : 1.27.2
InfluxDB : 1.8.10
Grafana : 10.0.2
mysql : 8.0.34-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Answer 1

There are 2 approaches for your consideration,

#1 Static location info defined by Telegraf config

If the location info can be defined by telegraf level (assume telegraf is installed on a concentrator/gateway device and all sensor data collected via this concentrator will be treated as from the same location), then you can use [global_tags] in the telegraf config file to define a static location info for each device.

#2 Dynamic location info provided by Data source (via MQTT)

If you want to achieve sensor level location info, then you have to contain those info in MQTT topics or messages, because each sensor will have different location. Telegraf can use [[inputs.mqtt_consumer]] or [[processors.regex]] to dynamically process the MQTT message to add tags to metrics. For example, the below code snippet is used to parse location info in MQTT message,

[[inputs.mqtt_consumer]]
  servers = ...
  username = ...
  password = ...
  ...
  tag_keys = [
    "location",
    "area"
  ]

So if the MQTT message contains location info like below,

{"deviceId"="A", "temperature"="xx", "humidity"="xx", "location"="101", "area"="1"}

the measurement will be tagged with the below location info,

measurement,deviceId="A",location="101",area="1" temperature="xx",humidity="xx"