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
target, and the second one contains
high. Rows from both datasources should be joined/merged depending on their id.
Then, I want to calculate a ratio of rows where
targetis within the interval provided by
high, where rows with
targetright 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
targetstables 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:
- Add field
typefrom value mapping
low < target AND target < high→ 2
low = target OR target = high→ 1
- Otherwise → 0
- Organize fields
- Hide everything except
- Hide everything except
- Mode: Series to rows
- Calculations: Total, Count
- Add field
- Mode: Binary operation
Count / Total / 2
- Replace all fields: Yes
And then display “rate” as a gauge.
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 / Totaland 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.