Lookup IDs from query in custom datasource for display

Using latest Grafana 10 release, Ubuntu standard install.

  • Situation
    We have a dashboard in our organization. However, some visualizations can only show IDs rather than useful information. I created a custom datasource that can map those IDs to names and other useful fields. The question has now become, how do I connect both of these? Importantly, the custom datasource is slow (and massive).

  • What I tried
    I tried to make 2 queries, but I do not think that there is a way to use the result of the first query in the second query in our setup. I then resorted to look at transformations and I found the “Merge”, join, and lookup transformation…

  • What happened
    Unfortunately, Merge relies on gathering information first, then merging them. There is no way to only query what you need. Hence, the custom datasource needs to download a massive amount of data to make it work. Join also has limitations and lookup is in alpha/beta and very restricted (to spatial data).

  • What I expected
    I hoped that there was some way that the second query could get IDs or other fields from the first query, using templates. I definitely expected that the Transformations were a bit more powerful and could perform simple lookup in custom datasource.

I looked through many issues, none of which laid out the same problems that I have. For most people, merge seems to work fine, but that will not work with the amount of data that is involved.

Is there anything that can help me with this, anything planned by Grafana, or how can I approach this problem?

Welcome @jaqxues

Can you elaborate on this? What are your primary datasources? Can you share details about the custom datasource you created? Can you share your queries from the primary datasources and from this custom datasource you created?

1 Like

I wanted to keep it more generic as it is a general concern.

The primary datasource is a MySQL database. It performs a query returning the id of an object and a metric. Basically, it is a

SELECT * FROM table WHERE $__timeFilter()

The custom datasource connects to an existing GRPC API and gathers name, age, serial number & co of the object. So input is one ID, output is table of id, name, age, serial_no, …

Not quite sure if it is helpful to build you some fake data around it, but if it is and you want it, let me know! But it really is just about the generic problem, nothing to do with specific datasources

This may be an unrealistic suggestion, but can you bring in your GRPC data (with the name, age, etc.) into your MySQL database and then write a new query for Grafana using only the MySQL db? As you have discovered, Merge, Join and Lookup all have some shortcomings, esp. when trying to do the operation using different datasources. It might totally be possible, but I have no personal experience doing that.

I did think about that, but having regular syncs and every other issues coming from 2 datasources would make it very resource-intensive and far more complex than necessary…

I would prefer Grafana having a “lookup” transformation, or some way of accessing result of query A via templating variables in query B. Ideally, I would want a way such that you can write multiple queries one by one, taking a table as input, outputting another table. In our case, that would be the only scalable solution (in case of more complex queries). This would also scale well resource-wise.

Like I said, it might (should?) be totally possible, but I have no personal experience doing that.

I will try to mess around with some test data if I can find some time.

Create 2 queries: main mysql query and reference grpc query. Make sure that they have the same name for id field/column. Then add join type transformation and use that id column for joining. At the end you can add organize transformation to hide that id column in the result.

It is not lookup, but join - but I would say it will produces desired result.

Problem is, as explained, that querying all of the data of the “lookup” datasource is insane, multiple gigabytes of data… Not an option

Maybe use a plugin that has javascript enabled to make the gprc api call (if that is possible) and send in the ID as paramete?

If you can filter on grpc datasource, then request to return only selected ids (used atm in main query) , not all of them.

I can filter yes, but I do not know what Ids I need to fetch. That is resolved in the first query, the results of which are not available in the second query afaik. That is the problem.

Make the 1st query a dashboard variable and use ${id} in second query

That’s a really dirty way to do it, and not really scalable… I would rather want what I mentioned earlier. I would rather not accept this as solution

Which one would that be? Lets build on that

It should not be a dashboard setting, but rather private to the Panel itself. Having query A and query B, where B can use a result variable from query A would be a great addition. To me, it also seems very easily optimizable and I don’t really see a reason to not allow it, but that might be my ignorance here.

A less flexible solution, which would still work in this specific case (but not in many others) would be to allow the “lookup” Transformation for datasources, not only pre-defined constants.

That sounds like a feature request. That won’t solve it either

Well I am just trying to solve a problem nicely, but doesn’t seem like there is a solution to it right now.

The dashboard variable hack seems okay for my problem, I think. I was just hoping for something better, more elegant. Thanks though:)

There is. All the above top notch experts have proposed great ideas.
Does query A bring back only one result? Or many

It returns many, list of IDs

you feed all of these ID’s to the GRPC API?