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

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

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.

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)?

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