Relating 2 pannels from 2 datasources

Grafana Dashboards
1

Hello. Im using Grafana 10.4.2. I’m looking for some help on the interaction between 2 pannels, each of them from a different data source.

I have a BigQuery pannel and a ElasticSearch pannel. The 1st pannel shows all the equipment present on my factory, and 2nd shows the algorithms associated to every equipment. The pannels are both related by a field called “*num”, wich is the ID of the equipment.

What I need is too complex for me. I need a 3rd pannel showing the elements from the 1st pannel that are not in the 2nd one. For example, if an “*num” (equipment id) is on my BigQuery database but not in the Elastic one, that would mean that we dont have any algorithm associated to the equipment, so the 3rd pannel should contain all the info of every equipment present in the first pannel, but not in the second one.

Thanks for help!

2

you could try joining your data:

4

you could try a merge or a join

5

Okay Raymond so following your instructions and the documentation I made a join by the ID of the equipment, so now I know wich equipments are covered by at least one algorithm.

But now I want to do the reverse operation, I want to know the elements from my equipment table that are not in the other table. Like a join, but keeping only the elements that can’t join, I don’t know if I’m explaining myself.

I tried an outter and inner join but the result stay the same. Can you help me please?

6

can you share a table view of your data from your queries?

7

Hello again Raymond. My data is classified so I can’t share it here. Anyways, i can send you screenshots of my tables and queries, covering the critical data:
This is the query of the equipment data:

foto1
foto1901×85 5.86 KB

Where *num is the id of my equipment. The result table looks like this:
foto2
foto21250×66 4.39 KB

So as you can see, this table is quite simple.

Also, I was able to migrate the second database to Google Cloud, so the 2 datasources are now from BigQuery.
This is the second query, the one with the algorithms:

foto3
foto3886×62 3.97 KB

And the result table is this one:
foto 4
foto 4940×71 3.36 KB

There are 80 different equipments, and 12 of them have an algorithm. I can know this by doing the join. As the *num is a common field between both tables, the join will turn return a table with 12 rows. But the table I need is one with the other 68 fields, those wich do not have any *num in common.

Hope you can help me, tysm!

8

I think I got it. This is how I’ve done it:

As I’ve seen in some guides, the join is limited in the Grafana default options, so doing it directly on the SQL query is a better option. Migrating my DB to BQ made it a lot easier too. The SQL query I’ve used was this:

foto5
foto5925×317 16.5 KB

Thanks to @sowdenraymond for your help, this time I was faster than you :rofl:

1 Like