Filter using max value of a column

Elastic Stack Kibana
1

Hello!

I have an index that collects entries in batches, identified by their build number in a corresponding column, and need to show only the data from the last build on a canvas. While I can filter for an explicitly given number, I thought I would need to use something like

filters
| essql query="SELECT accession, comment, build FROM \"general*\""
| filterrows { getCell "build" | eq math "max(build)" }
| table
| render

but the resulting table is empty. Replacing math "max(build)" by a static number works fine.

Is there a way to implement such a max-filter on a Kibana Canvas?

Glad for any alternative suggestion!

Thanks,
Mona

2

Hi @monomeric

I see a few issues with your expression. First, the result of each function is passed on as the input to the next function. So when you do

getCell "build" | eq math "max(build)"

The result of the getCell is the input of the eq function and that's all the context that the eq function has. So, doing max(build) will throw an error because it's doesn't know how to find the max value of a column named build when it's only given a number (the result of the getCell).

Now, it's not throwing an error because of a small syntax error on the eq function. You would have to give the eq function an expression for its argument using {} to be able to use an additional function like math, like eq {math "max(build)"}. As it is, it's being interpreted as just two strings, "math" and "max(build)", the second string being compared to the result of getCell, causing the empty table.

Now, to fix it, the easiest way would be to just add a new column to your result so in your filterrows function you can do a comparison on two columns in the same row. Something like

essql ...
| staticColumn name="max_build" value={math "max(build)"}
| filterrows fn={math "subtract(build, max_build)" | eq 0}

Hope that helps

1 Like
3

Hi @corey.robertson

Many thanks for your input and for correcting my syntax. As you noticed, I am quite new to this flavour of filtering and I really appreciate your help.

Your suggestion works in principle, but it looks like I am running into Kibana issue #51191 now, as my index currently contains about 100k entries and the max seems to be only evaluated among the first 1000 rows of my query result. Is there a workaround/fix?

4

I understand that also with esdocs there is a limit of 10000 documents. Is there a way to apply the max-filter to larger indexes in Canvas?

5

What if you add an ordering to your sql to order by build desc, so you are only getting the largest values in the first few rows of the result.

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.