Alert for complex elasticsearch query

[alexandrpaliy]

Initially I was going to create topic “expressions don’t work for elasticsearch queries” about one specific issue in the end of the post, but then I decided to describe my “issue” in general.

---

I use latest (9.2.5) self-hosted grafana with legacy alerting.

My data is stored in elasticsearch indices. My goal:

1. Get documents count where field X equals “value1”
2. Get documents count where field X equals “value2”
3. Calculate ratio, i.e. count(value1) / count(value2)
4. Use grafana to send me alert when ratio drops below some threshold

Since, AFAIK, I can’t use complex elasticsearch queries in grafana (elasticsearch “query DSL”, I guess it’s called like that), i.e. I can’t use elasticsearch “aggs” in order to calculate point 3 of my “goals” above - I assume that I need to calculate rate inside grafana itself.

And this leads me to problems:

1. If I use “Transform” feature to calculate ratio - it works, and panel looks exactly how I want it to, but I can’t create an alert based on transform (and, I guess, the new alerting system has the same limitation).
2. If I use “Expression” - it simply doesn’t work for any of my elasticsearch queries.

Just an example. First, one simple elasticsearch query:

Now, if I add any math expression to the same query:

If, according to my “goal”, I use 2 elasticsearch queries, and then use “expression” to calculate ratio - result is zero for any input. I have tested the same expression for my other data source (ClickHouse in my case) - it works correctly there.
edit: Also, just in case, I’ve tried to use “mixed” data source for panel, and then my elasticsearch data source for query A, and then apply expression over it - didn’t help, same result.

I have tried some quick search through github issues, didn’t find any relevant reports. Maybe, it’s documented somewhere, that expressions don’t work for elasticsearch queries, i.e. it’s expected behavior at this point?

And, in any case, any ideas about my “goal”? Any workarounds?

[alexandrpaliy]

Good news: I have figured out, why didn’t Expressions work for me.

Simple query [with timestamp field not explicitly set] - OK:

Simple query [with timestamp field not explicitly set] and math expression - not OK:

Simple query [with timestamp field explicitly set] and math expression - OK:

Bad news for me: I can’t use Expression in my alert, because I use Legacy alerting And I’m not really going to switch to Unified alerting in the nearest future.