Merge multiple stats to a single one / availability report

Dear community!

I am attempting to create an availability report for a component based on the metrics. I have put together a “definition” of availability which is composed out of multiple different metrics (and in most cases these metrics do not have labels in common). I am able to create Stats out of those “partial” results just fine:

these values represent how much of time (in %) was the specific metric in desired state. The final step for me is to create an “overall” availability result which would combine each of those results in the screenshot to a single number. The rules would be:

  • if all are 100% then the availability is 100%
  • if any of these are <100% than the overall availability is <100%

The problem I can’t solve right now is, that I don’t know how to merge/transform these partial results into a single number.

Note: avg is not a way to go as any of these “partial” availability check may report unavailability at any time, these may overlap, but it’s also possible that these do not overlap at all. I don’t want to sum the time when any of these are overlapping (if availability is <100%), the overall result should only count if any of the metrics are <100, taking possible overlapping into consideration (if multiple metrics report <100 it shouldn’t be summed).

I hope I made this clear enough ^^. Let me please know if any other details are needed.

Thanks!

What I have tried so far was to:

but this seems to not to give me the desired result, it also sometimes doesn’t reflect the data correctly it seems e.g. overall nr. (at the top middle) is calculated to 99.99% even though all the queries return 100% for the chosen time:

Ultimately it seems that this merge is not doing what I want, see at the below screenshot, if some of the metric is reporting a longer (unavailability) time, than the other I don’t understand why the overall number (at the top middle) is not reflecting this:


e.g.in the example above, the overall avaialbility (top middle) should be at least 99.91 or lower.

It seems that it’s really the merge which is problematic;

Grouping to matrix only:


and with enabled “Merge series/tables”:

also one thing I have noticed - having the Matrix transformation enabled/disabled makes no difference to the overall result

Ok, so I’ve tried a different approach, I’ve changed all the subqueries I want to merge, to only report either 0 or 1 (via bool).
This allows me to do a “Add field from calculation” transformation → to merge the results by doing Min:


so now I don’t count the same results from multiple queries twice (or more). This leaves me with following:

is there a way to calculate all values in chosen period and then % of value = 0 from the above produced table via another transormation?