Create Dynamic Data Link based on cell value on table

[albertoglezc]

I found a trick to create a dynamic data link in a table panel using PostgreSQL datasource (although it could be using another SQL-based datasource).

The key is to use a variable containing the different values that will be used as the key for a join. Later create another variable based on this as an expression to generate an SQL query with a SELECT VALUES. Generate said query as part of the table and, using a panel transform, perform a merge, a join by field, an organize fields and a filter data by values.

If anyone is interested I can provide a detail of how I accomplished this.

[genc]

I am, interested. Can you provide the details please?

[albertoglezc]

Let me explain next week. Actually I am on Hollydays. As soon as I return to the office, I’ll provide you the details.

It’s a little tircky. But that was the only way to get that

[albertoglezc]

OK. Here you are…

1. First create Constant variable with each url for each dashboard (or link):

2. Now we need to note every identificative value for each row (that will be used to determine witch link we need to present). On this example we can see there is only one row for host, however Process DB value can be repeated for more than one host. So we will use (on this example) a variable that will contain a host list:

Now we will create a new variable (for ejemple expr ) that will generate an expression necessary for a PostgreSQL. This new var will be Query type, using a working PostgreSQL (or other SQL datasource). Really we don’t need to query data, only need a Datasource to a SQL server (PostgreSQL, Oracle, MariaDB…). New var will be like this:

Query will be like this:

SELECT '(''' || REPLACE('${host:raw}', ',', '''),(''') || ''')'

This will be create a string needed to PostgreSQL (or other SQL) that will be used on other query:

3. Now we create a new table panelwith a PostgreSQL query. The query that I used was like this:

SELECT
  tt2.host_windows,
  tt.instance_bd,
  tt.description,
  tt.url
FROM
  (VALUES ${expr:raw}) AS tt2(host_windows)
CROSS JOIN
  (VALUES
    ('influxd', 'Dashbaoard Influx', '$urlINFLUX'),
    ('sqlservr', 'Dashbaoard SQL', '$urlSQL')
  ) AS tt(instance_bd, description, url);

EXPLANATION: With this query we are generating an output of all the combinations of HOSTS (contained in the host variable using the expression generated in the expr variable) combined with the possible options of the Process DDBB values (influxd and sqlservr), along with their respective description (which will appear as a tooltip of the data link) and its respective URL (contained in the constant type variables generated in step 1). This generates a table with values like this:

host_windows	instance_bd	description	url
sv**********04	influxd	Dashbaoard Influx	./d/**********/influxdb?orgId=1&var-ServerName=
sv**********04	sqlservr	Dashbaoard SQL	./d/**********/sql-server?orgId=1&refresh=1m&var-host=
sv**********06	influxd	Dashbaoard Influx	./d/**********/influxdb?orgId=1&var-ServerName=
sv**********06	sqlservr	Dashbaoard SQL	./d/**********/sql-server?orgId=1&refresh=1m&var-host=
sv**********07	influxd	Dashbaoard Influx	./d/**********/influxdb?orgId=1&var-ServerName=
…	…	…	…

NOTE: It is important that the name of the first field MATCH the name of the field in the InfluxQL query:

SELECT "host" AS "host_windows", "instance_bd"
 FROM (
   SELECT last("Percent_Processor_Time"), "instance" as "instance_bd"
   FROM  "1week"."Windows.Process"
   WHERE "host" =~ /^$host$/ AND  "instance" =~ /^$process_bd$/ AND $timeFilter
   GROUP BY "instance", "host"
)

[albertoglezc]

4. Finally we need to go to Transform section of table panel, and select following options on this order:

Merge

Join by field

Organize fields (especially DO NOT hide the url and description columns from the transform, since otherwise these fields cannot be used afterward in the data link)

Filter data by values

5. Now we will get a table like this:

6. Now we are interested in hiding these columns on the one hand and on the other hand making use of the values of these cells for the data links. To do this we will go to the Process DDBB column and create the data link as follows:

In this case we will set as Title the ${__data.fields.description} that corresponds to the name of the field of the query generated in step 3. The URL will point to ${__data.fields.url}, which corresponds to the other field established in the query of point 3.

[albertoglezc]

7. Finally we will generate an override for the description and url columns, where we will set the Hide in table property:

Finally this works like this:

As you can see yo can create a different dataLink for each row…

It’s quite complicated but I couldn’t find a better way to do it.

I hope I have been fairly clear in my explanations.