Variable selecting a field to organize the data inside topics

[rovemb]

Hi, Grafana community!
I am looking for help because I am using Grafana to plot data from InfluxDB that is stored using Telegraf with data format JSON.
The data is coming from The Things Network via MQTT connection and is stored into InfluxDB:
database - mqtt_consumer - topic - deveui - field - data an example in Flux:

from(bucket: "example")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "v3/test@ttn/devices/eui-XXXXXXXXXXXXX/up")
  |> filter(fn: (r) => r["_field"] == "uplink_message_decoded_payload_ABC")
  |> filter(fn: (r) => r["host"] == "host")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

• DevEui is topic
• Per each DevEUI I have five fields(data received from the device), where one of them is ID.
• I need to be able to look all the fields per each topic that is using the same ID (it is a field).
  I am using right now InfluxQL but I am not able to organize the data like I need, is there any option in JSON formatting or they way Variables or organizing to be able to obtain the data that I need?
  Thanks in advance.
  EDIT:
  Tryin to be more clear.
  The JSON I am receiving is like this:

{
   "data": {
    "@type": "type.googleapis.com/ttn.lorawan.v3.ApplicationUp",
    "end_device_ids": {
      "device_id": "eui-xxxxxxxxxx",
      "application_ids": {
        "application_id": "nameapp"
      },
      "dev_eui": "xxxxxxxxxx",
      "join_eui": "0000000000000000",
      "dev_addr": "ABCABC"
    },
    "received_at": "2021-11-01T21:49:59.298622220Z",
    "uplink_message": {
      "f_port": 1,
      "f_cnt": 508,
      "frm_payload": "payload",
      "decoded_payload": {
        "a": 317,
        "b": 59,
        "c": 24.6,
        "d": 00003665
      }
    

I am trying to use uplink_message_decoded_payload_d (will be the same for some devices in different dates) as a selector (variable) for showing the rest of the data; uplink_message_decoded_payload_a, uplink_message_decoded_payload_b and uplink_message_decoded_payload_c with different topics (v3/test@ttn/devices/eui-XXXXXXXXXXXXX/up) is that even possible? Will be better to use other type of DB?
Thanks.

[grant2]

Welcome to the forum.

Let’s try to unpack your question in smaller pieces…

1. You have MQTT feeding into InfluxDB 2.0. Are you able to view the data correctly in Influx Data Explorer? Something like this?
   

   

2. What are the field names and tag names used for this data in Influx?

3. In Grafana, have you created a working panel of this data? Can you share it (and the underlying query)?

4. In Grafana, have you created any working variables? Are you familiar with the process of creating variables in Grafana (i.e. have you done it already)?

[rovemb]

Dear grant2,
first of all thanks for replying and trying to decompose my message.

1. 

This is how I have the data inside the bucket, main thing is that I am using grafana with InfluxQL, not with Flux.
2. from(bucket: "s1") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer") |> filter(fn: (r) => r["topic"] == "v3/1300@ttn/devices/eui-d0c/up" or r["topic"] == "v3/-1300@ttn/devices/eui-36640a/up") |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false) |> yield(name: "last")
3.SELECT mean("uplink_message_decoded_payload_Success") FROM "example-rp"."mqtt_consumer" WHERE ("topic" =~ /^v3\/1300@ttn\/devices\/eui-40a\/up$/) AND time >= now() - 2d and time <= now() GROUP BY time(10s) fill(none) ORDER BY time DESC;SELECT mean("uplink_message_decoded_payload_Attempts") FROM "example-rp"."mqtt_consumer" WHERE ("topic" =~ /^v3\/1300@ttn\/devices\/eui-40a\/up$/) AND time >= now() - 2d and time <= now() GROUP BY time(10s) fill(none) ORDER BY time DESC Do you mean this part?
4. Yes, I am not an expert but I have done for example some variables that work like:

[grant2]

OK, thanks for your replies. Let’s focus on your Grafana query that is currently working:

SELECT mean("uplink_message_decoded_payload_Success") FROM "example-rp"."mqtt_consumer" WHERE ("topic" =~ /^v3\/1300@ttn\/devices\/eui-40a\/up$/) AND time >= now() - 2d and time <= now() GROUP BY time(10s) fill(none) ORDER BY time DESC;SELECT mean("uplink_message_decoded_payload_Attempts") FROM "example-rp"."mqtt_consumer" WHERE ("topic" =~ /^v3\/1300@ttn\/devices\/eui-40a\/up$/) AND time >= now() - 2d and time <= now() GROUP BY time(10s) fill(none) ORDER BY time DESC

In the above, the WHERE clause is where you want a variable, correct?

WHERE ("topic" =~ /^v3\/1300@ttn\/devices\/eui-40a\/up$/)

Basically, instead of hard-coding the above info, you want to set up a variable with different topics, for example: v3/test@ttn/devices/eui-XXXXXXXXXXXXX/up, correct? Did you try to do this already?

Secondly, you mentioned that you are trying to use uplink_message_decoded_payload_d as a selector (variable) for showing the rest of the data. Is this a field or a tag? You have a field in your Grafana query called uplink_message_decoded_payload_Success and also uplink_message_decoded_payload_Attempts, but I do not know if either is related to uplink_message_decoded_payload_d

[rovemb]

Dear grant2,
thanks again for your help. I have tried the solution you are saying the main problem is that I guess the data is stored as:

• v3/test@ttn/devices/eui-XXXXXXXXXXXXX/up
• uplink_message:
  • decoded_payload{
    “a”: 317,
    “b”: 59,
    “c”: 24.6,
    “d”: 00003665
    }
    And I need selecting d to be able to see all the devices v3/test@ttn/devices/eui-XXXXXXXXXXXXX/up and the data from them so I guess it should be instead of a variable a tag or key but I do not know how to deal with it.

[grant2]

@rovemb I know a few weeks have passed, so maybe you already solved this question, but in case not, I am 95% sure you can do what you want if you were to use Node-RED between your MQTT data and InfluxDB. Then, in Grafana, you will have “d” as a variable (or “a”, or “b” or whatever). Here is the article that can guide you through this process.