1

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

Curious koala
  • 309
  • 1
  • 9

1 Answers1

2

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

markalex
  • 8,623
  • 2
  • 7
  • 32
  • Thanks. I used 1 solution and now I got separate charts for each `kpi`. But there's a one more problem (check Update): instead of `kpi` name for each line in Legend I see just a `Series 1`, `Series 2` labels... I tried to set `${__series.name}` to Standard options -> Display name (as suggested [here](https://community.grafana.com/t/use-query-name-as-display-name-to-label-series-in-graph/61400/2)). But didn't work. Can you suggest a solution? – Curious koala Apr 17 '23 at 08:22
  • @Curiouskoala, to rename series, click on series name (`Series 1` in your example) in Panel options, it will allow to write new name for series. – markalex Apr 17 '23 at 08:27
  • The number of Series and names are not constant, but depends on values user selects from drop-down menu (values of multi-value variable `$kpi`), so I cannot set names simply manually - it should be set automatically somehow... Should I create the new question for this issue? – Curious koala Apr 17 '23 at 08:51
  • @Curiouskoala, you need to add series for every possible value of kpi, and name it accordingly (not selected wouldn't be shown at graph automatically, I believe). But generally, it could be easier to go solution #2 in this case. – markalex Apr 17 '23 at 09:04