How to display only positive values after a tophit (latest) aggregation


currently I am exporting (once per hour) certificate expiration data to elasticsearch and visualize it with Kibana. So far so good, but I can't figure out to properly display certificates that are within 30 days to expire without showing expired certificates.

The approach I currently have is to visualize using a data table and use a Top Hit aggregation on field days_before_expire, sorted on timestamp and concat size 1. Rows are splitted on the certificate name.

This results in 1 entry per certificate (their latest available document in elasticsearch) in the table. But what I would like to do is to filter out the expired certificates, thus certificates where days_before_expire is < 0.
If I do this as a kibana filter (under the search) this results in (to me) unexpected behavior as the displayed days_before_expire will be non-negative but is not guaranteed to be the most recent value (as negatives are filtered out).

Can someone elaborate on how to avoid this behavior? The example doesn't stand on its own as I encounter this challenge often when using the Top Hit aggregation.

My ultimate goal might be better to be explained by an example, assume the following data:

date domain match
Feb 4, 2021 @ 11:00:39.000 false
Feb 4, 2021 @ 11:00:39.000 true
Feb 4, 2021 @ 11:00:39.000 true
Feb 4, 2021 @ 11:00:39.000 true
Feb 4, 2021 @ 10:00:39.000 true
Feb 4, 2021 @ 10:00:39.000 false
Feb 4, 2021 @ 10:00:39.000 true
Feb 4, 2021 @ 10:00:39.000 false

In Kibana I would like to create a table that displays the domains whose last known match status is false, thus:


However, when using the Top Hits aggregation in combination with a match: false filter I get the following (due to filter is applied before the actual aggregation?):

How can I grab the last known document of each domain and only display them if their match field is false?

Hmm, I can't think of a good solution here. I think what you need is a bucket selector, which isn't yet supported by Kibana [1].

[1] Support Bucket Selector Aggregation to allow filtering of Data Table visualization · Issue #11167 · elastic/kibana · GitHub

Well that is disappointing to read as this might be a great feature for Kibana (Am I alone?).

I can imagine that this could be very beneficial to Kibana as it serves a proper and direct use-case; an operational dashboard that only displays statuses that require attention.

I know for that use-case there is also the alerting, but not always direct alerts are needed and just a quick lookup of overall statuses is required without noise.

Are there any plans to get this sort of visualization/filtering implemented? Mentioned ticket is already there for almost 4 years.

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