Using Grafana 7.0.6 with elasticsearch data source.
I have a number of queries, for each of which I’m interested in the count. I also have a number of derived metrics from these counts.
For example, lets say I groupby my
created_at time field on a 1-day interval, and count the
Total Count number of items on that day. Then, I have another field, e.g.
Success Count with the same groupby, but with query
success:true. Then I use an outer join transformation on
created_at to join these two counts, and compute a new field called
Success Rate using a transformation:
Success Count / Total Count.
However, in addition to my 1-day interval, I also want to know the all-time aggregate. For
Total Count and
Success Count this is easy - I can just use the
Reduce: Total transformation. However, this does not work for the
Success Rate. Not only can I not pick and choose different reduces for different columns, even if I could, I’d need a Weighted Average which does not exist. I could first perform the Reduce, and then compute the metrics I need afterwards, but Reduce turns the columns into rows, meaning I can no longer compute an additional column based on my existing columns…
A second approach I’ve tried is grouping by a term, which would get me the aggregates for that term. However, for some reason that is beyond my understanding, Aliases do not work in this situation. This means that I cannot have a
Total Count and
Success Count column, because they are both still just called
Count. If I then do an outer join on this term, it will just give me two identical columns (same name, and the values from whichever query was last). So I cannot compute the
Success Rate grouped by term either.
Both of these situations seem like they should be trivial but neither seems possible. Am I missing something? What is the reason that these things are impossible?