Transform Outer Join when one query has no results

Grafana
1

Hi,

I have a strange / unexpected transformation outer join result if one of the query results is empty. I wonder if anyone else has come across this and has a good solution?

To illustrate (with some pseudo-results):

1) If my query results are:

  • a: {1,2,3,4)
  • b: {3,4,5,6}

I see:

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    5
null |    6

So far, so good.

2) If my query results are:

  • a: {1,2,3,4)
  • b: {3,4}

I see:

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4

Still all good.

But…

3) If my query results are:

  • a: {1,2,3,4)
  • b: {}

I would hope to see:

 a   |  b
-----+-----
   1 | null
   2 | null
   3 | null
   4 | null

…but I actually get:

nothing

:frowning:

What I’m attempting to do is join my various metrics with ALERTS from Alertmanager (this is the b query above). The problem is, if I have no alerts I’d really like to still show my metrics (from query a) with a useful “OK” status (or something like that). But it’s not working out like that…

  • If I have alerts on some metrics, I see the table to so I can show an “OK” / “Not OK” status on each.
  • If there are alerts on all metrics, I can show “Not OK” on them.
  • But if there are no alerts, I can’t show “OK” on all - since no alerts (one empty query) means an empty final result.

:confounded_face:

If anyone knows some good options or some good joining techniques, any info would be much appreciated,

Thank you

2

Have a look at the new in preview sql expression

3

Thanks @yosiasz

Yes, that looks like an interesting and useful feature. Do you know what Grafana version is required for that? If it’s new / in-preview then possibly 12, I guess?

1 Like
4

it should be available in 12

5

hello @garethsrt what datasource you are using?

6

Hi @infofcc3

The datasources are both Prometheus: ALERTS from Alertmanager and another Prometheus metric and labels.

I’d originally looked at this in terms of separate PromQL queries in the dashboard panel, joined by a transformation. I’m now wondering if there’s a better solution to do all this in PromQL with a single query and perhaps use Group Modifiers. I mentioned in the original post that Outer Join doesn’t work as I thought it would - but, actually, it’s a Left Join that I really want anyway (but there is no Left Join transformation, only Inner and Outer, so I was planning to achieve a Left with an Outer Join followed by a Filter).

But maybe the answer is to use “group_left“ within a single PromQL query instead - but I’m not sure how that would respond if one side (the right side) is empty. But I will try it and see if that works :crossed_fingers: