Grouping documents in kibana based on a column without losing the rest columns

[kakavoul (Διονυσης Κακαβουλης)]

Hello,

Let's assume that i have something like that in elastic search

alarm_id sequence date          alarm_text
1        1        2019-11-7     text of alarm1
2        1        2019-11-7     text of alarm2
2        2        2019-11-8     text of alarm2
2        3        2019-11-9     text of alarm2
3        1        2019-11-8     text of alarm3
3        2        2019-11-10    text of alarm3

and i want to group this information like that in kibana:

alarm_id      count         date          alarm_text
1             1             2019-11-7     text of alarm1
2             3             2019-11-9     text of alarm2
3             2             2019-11-10    text of alarm3

which means that i want to have a grouping by alarm id, a column "count" which keeps the maximum number of "sequence" column for that alarm_id (or alternatively the number of occurrences of that alarm_id), the newest date and its alarm text

i managed to have only the first two columns using a data table visualization but i couldn't have the rest columns too. Is there any way (visualization or search) to do it in kibana or is not feasible?

Thanks in advance

[flash1293 (Joe Reuter)]

Hi, you should be able to do this with the "Top Hit" metric aggregation. It can be used to use the value of one document out of the bucket - in your case every value of the field will be the same in the bucket, so you can just do a "Top Hit" on the desired field, use the "concatenate" aggregate option and the order doesn't even matter because there will be a single value anyway.

[kakavoul (Διονυσης Κακαβουλης)]

It worked, thank you very much.

But i have one extra question. Is it possible for one or more columns to not take into consideration some rows? for example for "count" column can i exclude every row that date = 2019-11-8 (without these rows being excluded for example from alarm_text column)?

[flash1293 (Joe Reuter)]

When you are applying filters they are applied before the aggregation is run, so you can not apply them partially.