How to take a column from one table and add this to another table as a column, matching on a specific columns value

Grafana Dashboards
1

GRAFANA 11.2.2

I have a dashboard, that have two tables. One table has an API datasource, the other table has a database datasource and is using MYSQL Query to populate.

I am trying to take a column from the MYSQL collected data and add this to the API collected data in the other table. Both tables has a common connection of a server name.

As I have tried to shown in the image. I would like to add the column from Table 1 (orange) into the Table 2, where the values in the two Columns highlighted in Blue match. If there is no match the it should read null or N/A or just be blank.

image
image1202×718 59.3 KB

I’ve tried to create a MIXED datasource and add both table but nothing works, I get No Data Message. I’m not sure if this is possible to achieve

any ideas woudl be much appreciated.

2

Hi @jimerobampton , maybe you can try make the API data as varaible then use varaible query in mysql datasource panel, it maybe work

3

Hi , How do I convert the API data to a variable ?

4

please post sample json data from api and sample data from mysql not as an image but as text

{
 "servername": "server01"
}

mysql

servername,datetime,value
server01,2025-03-05,33.3
5

To follow

1 Like
6

Sample JSON

{
  "id": "0-t62ce62219462574b-443c935e",
  "path": "All/Report Library/Custom Metrics and Properties",
  "name": "Custom Metrics and Properties",
  "type": "table",
  "properties": {},
  "time-range": {
    "start": 1741046400,
    "end": 1741132800
  },
  "content": {
    "columns": [
      "Collector",
      "ifDiscards ICF",
      "Adjusted for ACME",
      "TOC -> COS conversion",
      "TCA solution",
      "IPSLA"
    ],
    "rows": [
      [
        [
          "serverc-1"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        null,
        null,
        null
      ],
      [
        [
          "CustomSLA"
        ],
        null,
        null,
        null,
        [
          "Tloss",
          "Tlatency",
          "Tjitter",
          "Tiface"
        ],
        [
          "CISCO-IPSLA-JITTER-STATSTABLE",
          "CISCO-IPSLA-STATSTABLE"
        ]
      ],
      [
        [
          "Flexware"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "serverc_serverc-1"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "serverc_serverc-2"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11991-1"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11991-2"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11991-3"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11991-4"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11993-1"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11993-2"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n11993-3"
        ],
        null,
        null,
        null,
        null,
        null
      ],
      [
        [
          "n13073-2"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        [
          "COS4",
          "COS2",
          "COS1"
        ],
        null,
        [
          "CISCO-IPSLA-HISTORY",
          "CISCO-IPSLA-JITTER-STATSTABLE",
          "CISCO-IPSLA-INVENTORY",
          "CISCO-IPSLA-JITTER-LATEST",
          "CISCO-IPSLA-STATSTABLE"
        ]
      ],
      [
        [
          "n13073-3"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        [
          "COS5",
          "COS4",
          "COS3",
          "224",
          "COS2",
          "COS1",
          "COS2V"
        ],
        null,
        [
          "CISCO-IPSLA-HISTORY",
          "CISCO-IPSLA-JITTER-STATSTABLE",
          "CISCO-IPSLA-INVENTORY",
          "CISCO-IPSLA-JITTER-LATEST",
          "CISCO-IPSLA-STATSTABLE"
        ]
      ],
      [
        [
          "server001-2"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        null,
        null,
        null
      ],
      [
        [
          "server001-3"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        null,
        null,
        null
      ],
      [
        [
          "server001-4"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        null,
        null,
        null
      ],
      [
        [
          "server001-5"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        null,
        null,
        null
      ],
      [
        [
          "server11402"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        null,
        null,
        null
      ],
      [
        [
          "server11405"
        ],
        [
          "pctIfOutDiscards",
          "pctIfInDiscards"
        ],
        null,
        [
          "0",
          "136",
          "104",
          "72",
          "40",
          "184"
        ],
        null,
        [
          "CISCO-IPSLA-HISTORY",
          "CISCO-IPSLA-JITTER-STATSTABLE",
          "CISCO-IPSLA-INVENTORY",
          "CISCO-IPSLA-JITTER-LATEST",
          "CISCO-IPSLA-STATSTABLE"
        ]
      ],
      [
        [
          "TestSNMP"
        ],
        null,
        null,
        null,
        null,
        null
      ]
    ]
  }
}
7

Sample

DB results

“COLLECTOR NAME”,“SERVICE”
SERVER0103-1,
SERVER0103-3,PEPSI
SERVER1547-1,PEPSI
SERVER2009-1,PEPSI
SERVER2009-2,PEPSI
SERVER2010-1,PEPSI
SERVER2010-2,PEPSI
SERVER2050-1,PEPSI
SERVER2050-2,PEPSI
SERVER2073-1,PEPSI
SERVER2582-1,PEPSI
SERVER2582-1,
SERVER2582-2,PEPSI
SERVER2619-1,PEPSI
SERVER3266-1,PEPSI
SERVER3446-3,PEPSI
SERVER3452-1,PEPSI
SERVER3452-2,PEPSI
SERVER3452-3,PEPSI
SERVER3452-4,PEPSI
SERVER3464-1,MMM
SERVER3464-1,
SERVER3478-1,PEPSI
SERVER3729-1,PEPSI

8

Note that the Server names in the SQL matches with the JSON but I edited to remove any real names. They do not match in the two outputs shared but do match in real life

1 Like
9

Mocking your data on my grafana

image
image975×465 40.1 KB

One approach is to use join transformation but this will require you alias your server data to COLLECTOR NAME

image
image1458×605 36.6 KB

But side note, I see your rest api json as being problematic, why are the server data points arrays? Is there possibility that there could be more than one server?

Also this kind of naming convention is worrisome "TOC -> COS conversion",

10

No the server name should only be one. Its just how the report in the application shows the data. I have little control over this. I could try and edit the report or recreate without the servers being in an array. I tried the transformation as you did above but let me try again with a different alias (which I think I tried) but you might be using a different mode, Ill give it a go.

11

just a side note as stated above, not a big deal

What other data point do you want to visualize from both data sources?

12

*just need the service column from SQL query added to the API table where the Collector Names Match. Been on call all afternoon, will take a look as soon as I can. Thanks