Search code examples
prometheusgrafana

Grafana Table Join by Multiple Fileds

I use Grafana table visualization to display metrics stored in Prometheus. I got 4 PromQL queries to calculate the metric values.

All the metrics have labels channel and currency. Each of them has many but limited values and the queries are different aggregations by those 2 labels. I want the table to display values of channel and currency in the first 2 columns, and then display the results of the 4 PromQLs (marked as Value #A to Value #D) in the last 4 columns.

I setup the queries, changed their formats to table and then added the Join By filed transform. I use Outer Join mode. If I choose channel as the field to be joined, the table header becomes:

channel currency1 Value #A currency2 Value #B currency3 Value #C currency4 Value #D

What I actually want is:

channel currency Value #A Value #B Value #C Value #D

But I can only select one field to join. And if I add more Join by field transformations, I got fields currency1...4 to choose from, which doesn't look right.

I also tried Merge transform, but it then displays all the values in the time range as rows in the table, so I got many repeated rows for the same channel and currency values.

I wonder how can I configure the table panel to get my desired result?

EDIT: Queries ():

max_over_time( sum (increase(voucher_send_total{status=~"success"}[5m])) by(channel, currency) )[2d] // max historical value


sum (avg_over_time(voucher_backlog_total{})[5m]) by (channel, currency) 


300 / (avg by (channel, currency) (rate(voucher_requests_latency_seconds_sum{method=“/voucher/send"}[5m]) / rate(voucher_requests_latency_seconds_count{method=“/voucher/send"}[5m]))) * count by(channel, currency) (
 count by(channel, currency, id) (
  increase(voucher_wallets_total[24h]) > 0
 )
) // 5-minute theoretical max TPS

sum (increase(voucher_send_total{status=~"success"}[5m])) by(channel, curreny)

Format: Table, Type: Instant

Solution

  • What you are trying to achieve is exactly fits description of Merge transformation:

    Use this transformation to combine the result from multiple queries into one single result. This is helpful when using the table panel visualization. Values that can be merged are combined into the same row. Values are mergeable if the shared fields contain the same data.

    Here is how it looks: enter image description here

    I've used four demo queries for this:

    sum by (job, cluster) (up)
max by (job, cluster) (up + 4)
count by (job, cluster) ({job!=""})
sum by (job, cluster) ({job!=""})

    all set to Format: Table, Type: Instant. And a single transformation Merge. It automatically detected rows with the same labels and times, and merged them into a single row.

    To hide Time column you can additionally use Organize fields transformation.