How to render multiple queries as multiple rows in a table with a single Value column

Grafana Dashboards
1

  • What Grafana version and what operating system are you using?
    Grafana v10.4.1 (d3ce857c0e)

  • What are you trying to achieve?
    I have a table chart with several queries (“targets”) that can each result in from zero to many time series. I want to have all the resulting rows from all the queries rendered in a single table.

  • How are you trying to achieve it?
    I created a single table chart and added all my targets to it.

  • What happened?
    The table rendered with the results of the first query only, with a selector at the bottom to let me choose the results of a different query. And instead of a Value column, there was a “Value #Query Name” column for each of the queries.

  • What did you expect to happen?
    I expected all rows to render in the same table, with all labels and the Value columns matched up.

  • What did you do to try and address this?
    I started playing with transformations but discovered that I didn’t really understand the underlying model that grafana was using. After applying the series to rows transformation and the merge series transformation, I ended up with one table with all the data, but with separate Value #A, Value #B, etc. value columns that I could not join to a single value column. (I don’t understand why this helped! The merge is required but I’m not actually merging rows!)

  • What do you still need help with?
    I need to merge all the Value #A columns into a single Value column that I can sort. And it’d be really helpful to have an explanation of why the merge series step was necessary.

2

Why you can’t get all data in single query?

3

10 different source metrics, each of which needs to be scaled/aggregated differently.

4

Pls provide datasource and those queries.

5

Datasource is GEM.

Some sample queries:

label_join(
  label_replace(
    min by (deployment_name) (
      primary_certificate_ttl{${SELECTION:raw}}
    ) 
    / 
    1000, 
    'cert', 
    'primary cert', 
    '\_\_name__', '(.*)'
  ),
  'chart_key',
   '', 
  'deployment_name'
) < 1209600

  • label_join(label_replace(min by (deployment_name) (secondary_certificate_expiry{${SELECTION:raw}}) - time(), ‘cert’, ‘secondary’, ‘__name__’, ‘(.*)’), ‘chart_key’, ‘’, ‘deployment_name’) < 1209600

  • label_join(label_replace(min by (deployment_name) (proxy_cert_expiry{${PROXY_FILTER:raw}}), ‘cert’, ‘proxy cert’, ‘__name__’, ‘(.*)’), ‘chart_key’, ‘’, ‘deployment_name’) < 1209600

  • label_join(label_replace(min by (deployment_name) (min_days_to_xxx_creds_expiration_value{${SELECTION:raw}}) * 86400, ‘cert’, ‘internal token’, ‘__name__’, ‘(.*)’), ‘chart_key’, ‘’, ‘deployment_name’) < 1209600

  • label_join(label_replace(min by (deployment) (external_certificate_expiry_sec{deployment=~"${DEPLOYMENT:regex}"}), ‘cert’, ‘external cert’, ‘__name__’, ‘(.*)’), ‘chart_key’, ‘’, ‘deployment’) < 1209600

6

Did you try Merge series/tables transformation?

It should work if columns are the same.

Example before transformation (two queries, only result of first is shown, dropdown list to switch between queries):

before
before1097×371 19.5 KB

Example after transformation (everything is merged):

after
after1085×431 25.2 KB

7

Yes, I used the merge series transformation. I ended up with one table with all the data, but with separate Value #Query A, Value #Query B, etc. value columns that I don’t know how to join to a single Value column.

8

Why you can’t get all data in single query?

This is actually the right way to do it! If you " or " all the queries together into one giant query, you don’t have to wrangle with joining the individual Value #A columns.

1 Like
9

I don’t agree. That is only one way. IMHO the easiest. Otherwise you will have to fight with transformations - I didn’t say that’s not possible, but it’s not worth it, when it can be done in simpler way.

10

Can you do it with transformations? I’m all ears if you have an idea on how to merge all those Value #A columns to a single column, but nobody seems to know how.

11

I “merged” results from 3 different datasource with ~30 transformations, so I know how painful process is it. You are expecting to do it for you for free as part of community support.
That’s asymetrical request - you spend a few minutes just to write that request and I should spend a few hours (that’s my estimation) to find transformation solution for free. I think everybody knows that’s not worth it. But I already gave idea for IMHO better solution.

Anyway I give you my kudos if you do that on your own :+1:

12

Jan, if your intent is to say “I have solved a similar problem, and I have no intention to share my knowledge with the community”… then what on earth are you doing here?

You certainly don’t have to solve my particular problem, but you can at least explain how you solved your problem.

13

How to solve it: read doc about transformations, try some, test&debug them, iterate until you reach desires result.