Performing joins operation in Grafana using templating (Elasticsearch datasource)

Grafana Elasticsearch
1

I’m trying to perform something similar to SQL inner join operation across 3 ES indices (3 different ES datasources in grafana) having a same field in all these indices that captures unique values. The trick that I thought of is using templating option on ES datasource A, having “Include All Options” in the Selection Options in templating.

  • Template 1- $sessionA

Then in the second query I use a different index (ES datasource B) but use something like sessionid:$sessionA to filter only these sessions. Then I keep these sessions in another template.

  • Template 2- $sessionB (this would be a subset of sessions from ES datasource A)

After this, I want to filter sessions in ES datasource C which are in $sessionB (this would be a subset of sessions from ES datasource B). The entire process is carried out to create a subset and get a count of the number of sessions at all the three datasources.

Expected Result: Count of (ES datasource A sessions >= ES datasource B sessions >= ES datasource C sessions).

Questions:

  1. Even after selecting ‘All’ option in the template dropdown, it looks like the entire data isn’t considered while getting data in second template. Is there a limit on the number of values being listed in the dropdown?

  2. Is this the right way of performing joins operation?

Any inputs on this would be appreciated!

2

@schikkam. Hello
Were you able to perform this indices join in Elastic using Grafana?

3

Hi @schikkam. There is a limit of 1000 items in template variable drop down. You can increase that limit by specifying the size parameter in your query for the template variable. For example:-
{“find”: “terms”, “field”: “request_uri”, “query”:“request_uri:/api/*”, “size”:5000}
Though you should do such things only when you know what you are doing. It would be advisable to avoid doing that and find a better solution to increasing the size.