Calculating (a bit special) ratio using Grafana transformations

Grafana Dashboards
, ,
1

  • What Grafana version and what operating system are you using?

    Grafana 9.0.6 inside Docker.

  • What are you trying to achieve?

    Tldr: I want to calculate a ratio (calculating it is a bit special so read below) of all rows where a value from a column from one datasource is within the interval provided by two other columns from another datasource.

    Data comes from two PostgreSQL databases. First datasource contains columns id and target, and the second one contains id, low and high. Rows from both datasources should be joined/merged depending on their id.

    Then, I want to calculate a ratio of rows where target is within the interval provided by low and high, where rows with target right on the edge of an interval count as 50%. So, where low < target AND target < high, count 2; where low = target OR target = high, count 1; otherwise, count 0. Then, divide the result by a (number of rows * 2) and display that in a Grafana gauge.

    If everything was in a single PostgreSQL database, I could use this as a query:

    SELECT
  SUM(CASE
    WHEN low < target AND target < high THEN 2
    WHEN low = target OR target = high THEN 1
    ELSE 0
  END)::float / COUNT(intervals.id) / 2 AS "rate"

FROM intervals
-- This table contains columns id, low, high

INNER JOIN targets
ON intervals.id = targets.id
-- This table contains columns id, target

WHERE
  -- Some conditions...

ORDER BY 1

    However, I cannot do this, because intervals and targets tables are not in the same database. I need a way to do this using Grafana transformations or another similar Grafana feature that works across multiple datasources.

  • How are you trying to achieve it?

    I have two PostgreSQL queries, one for each datasource, that retrieve required columns:

    -- Datasource 1
SELECT id, target FROM targets
WHERE -- Some conditions...
ORDER BY 1

    -- Datasource 2
SELECT id, low, high FROM intervals
WHERE -- Some conditions...
ORDER BY 1

    Then, I have to use Grafana features to merge those two queries and calculate ratio as I described above. With Grafana transformations, I thought I could do something like:

    1. Merge
    2. Add field type from value mapping
      • Where low < target AND target < high → 2
      • Where low = target OR target = high → 1
      • Otherwise → 0
    3. Organize fields
      • Hide everything except type field
    4. Reduce
      • Mode: Series to rows
      • Calculations: Total, Count
    5. Add field rate from calculation
      • Mode: Binary operation
      • Operation: Count / Total / 2
      • Replace all fields: Yes

    And then display “rate” as a gauge.

  • What happened?

    Nothing like “Add field from value mapping” transformation exists. The closest is existing “Value mapping” panel feature, but it cannot be integrated with transformations to perform steps 2+, and has very limited conditions (everything needs to be based on exact values or exact range start/end, while I need to have this based on value from another field). The closest transformation is “Rename by regex”, but it only affects field names, and I still couldn’t use it for dynamic conditions.

    The other (smaller) problem is that “Binary operation” from “Add field from calculation” transformation can only perform one calculation/operation at a time. This means I would have to split step 5 into two separate transformations, one to calculate Count / Total and the other to calculate Previous result / 2. This is only an inconvenience, but I think it would still be very useful to perform multiple operations in one transformation.

  • What did you expect to happen?

    I expect to be able to perform these calculations using Grafana.

    I guess Grafana would need to add “Value mapping” mode to the “Add field from calculation” transformation that would allow doing what I described above, and to make “Binary operation” mode more powerful to accept more “advanced” calculations.

    Or is there any other was to do this?

  • Did you follow any online instructions? If so, what is the URL?

    Documentation for Grafana transformations.