Search code examples
postgresqlgrafanagrafana-variable

Grafana: X-Y chart. How to represent data with separate lines for multi-value variable

I have chunks of data for each build that I want to visualize with Grafana X-Y chart. Let's say (simplified):

kpi average_time build_id
Start App 150 870
Stop App 890 870
kpi average_time build_id
Start App 140 871
Stop App 860 871

I used PostgreSQL to show lines for each kpi with build_id as X and average_time as Y

SELECT DISTINCT ON (kpi, build_id)
       build_id AS BUILD_ID, 
       average_time AS AVERAGE_TIME
       FROM <MY_TABLE> WHERE kpi IN (${kpi});

...and $kpi is a multi-value variable simply returned by SELECT kpi FROM <MY_TABLE>;

When I try to select Start App as single $kpi value it looks OK enter image description here

but if to select both $kpi values it looks like two lines (that's what I want) but connected with each other (that's not what I want) with extra line enter image description here

So how to display separate lines for each kpi (with possibility to set a color for each)?

UPDATE enter image description here

Solution

  • There is no easy way to do what you described, but it is possible.

    To show charts in different ways, you'll need to present values to panel as they are different series. It is possible to do in three major ways:

    1. Query data as it is, and split it on Grafana's side.
    2. Query data within single select, but average time for each kpi should be in a separate column.
    3. Query data for each kpi independently. I believe it is worst case, as you'll need to a separate query, and link every dataset into panel. I will not describe it in detail as it's fairly clear on its own.
    Way #1
    1. Open edit mode of Panel, tab Transform.
    2. Add Partition by values, select kpi. As a result data will be split into separate series.
    3. Go to panel options and manually add all new series:
      1. XY Chart > Series mapping > Manual
      2. For every series select corresponding field.
    Way #2
    1. Create query pivoting your data, (for example, using one of answers to this question), so that it returns columns in the following manner:
    • build_id,
    • start_app_avg_time,
    • stop_app_avg_time,
    • more of average time for every kpi
    1. In panel options: XY Chart > Series mapping > Auto
    2. Select X Field build_id. The rest will be selected automatically.

    Both ways you result for supplied example data would look something like this: enter image description here