Lens Table - "wrong" selection of top N elements

[tinrik]

Hi!

I want to display a table like this:

|   File Name                        |  Number of warnings |
|  path/to/foo.cpp                   |  123                |
|  path/to/bar.cpp                   |  30                 |
|  path/to/baz.cpp                   |  15                 |

They should be ordered by descending number of warnings.

So far so good with a Lens Table. However I noticed a problem. I have around 10.000 different files, whereas the Table can display at most 1000 rows. The problem is that Kibana will first pick the first 1000 files alphabetically, and then apply the sorting based on warnings. This means that there could be other 9000 files with even more warnings than the first one displayed.

Is there a good way to pick the top 1000 files based on number of warnings, instead of alphabetically?

Thanks!

[stephenb (Stephen Brown)]

Hi @tinrik

@tinrik

The problem is that Kibana will first pick the first 1000 files alphabetically, and then apply the sorting based on warnings. This means that there could be other 9000 files with even more warnings than the first one displayed.

What Version Of Kibana / Stack?

Please Show Exactly All the Settings you are using to build the Table

Here is my in 8.5.3 Example Doing a Sum there far more values than 5 but Ranks the Top 5 by the calculation

[tinrik]

Thanks for the quick reply @stephenb !

I am running Kibana 7.16.1. This is what my settings look like:

I realized that I'm also not using a "Quick Function", like "sum", but rather I have my own formula:

In this case a given file might be guarded (owned) by multiple people, so I need to divide by the number of guardians to get the real number of violations in a given file (otherwise it's double-counted).

If I switch over to using the Quick Function "sum", then I can choose "Rank by: Violations" and works as expected. Is there anything I need to do to enable Rank by using a custom formula?

Thanks!

[stephenb (Stephen Brown)]

I believe Sorting by Custom Formula is on the Road Map not sure when that is coming

Did you simply try to sort by the header...

[Marco_Liberati]

In recent versions of Lens there will be possible to specify a custom metric for Top values ranking, similar to the previous Visualization feature: [Lens] Custom rank agg for top values / pick hidden metrics · Issue #133991 · elastic/kibana · GitHub

As for rank by formula, there are some technical challenges to overcome first, but you can follow the progress on this issue: [Lens] sort by a formula column without expanding the entire range · Issue #114951 · elastic/kibana · GitHub

[tinrik]

@stephenb

Did you simply try to sort by the header...

Yes, that works, the problem is that sorting happens after the first N items have been selected alphabetically.

Put it differentely, the top 10 elements of the table should not change if I choose to display 100 or 1000 elements - the top 10 are always the top 10. However I don't observe this behavior - the top 10 elements will be different depending on how many total elements I choose to display.

[stephenb (Stephen Brown)]

@tinrik

Put it differentely, the top 10 elements of the table should not change if I choose to display 100 or 1000 elements - the top 10 are always the top 10. However I don't observe this behavior - the top 10 elements will be different depending on how many total elements I choose to display.

Yes that is correct.... if your population is less than 10000 it would work if not then no.

The Rank by formula will be the correct approach when it is available

[tinrik]

Great, thanks for the clarification!

Lastly, I would like to ask if it's possible to achieve what I want (a Table showing the top N according to custom formula) using some other visualization element? This post is about Lens Table - can I achieve this using a "Aggregation based Table", or does it have the same limitation?

[Marco_Liberati]

There's no feature like Formula in the agg based editor.