# Unique count by top status **URL:** \ **Category:** Kibana\ **Created:** [June 15, 2022, 11:48am UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262 "2022-06-15T11:48:17Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![talial](https://avatars.discourse-cdn.com/v4/letter/t/3be4f8/32.png) [@talial](https://discuss.elastic.co/u/talial)\ **Post date:** [June 15, 2022, 11:48am UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262/1 "2022-06-15T11:48:17Z")
Hello I have to build a visualize that count all event (id) with status "open" in a range of time. The problem is that I dont want to use the update option in the logstash so some events (id) can change their status to "close" after some minutes so I will have 1 document (id) with status open and afetr some minutes another document with the same id but status close so I need to ignore those events in the count because the last status for this event (id) is close for example: ```auto event with id 1234 created in 08/06/2022 10:50 time_changed 08/06/2022 10:50 state: open event with id 1234 created in 08/06/2022 10:54 time_changed 08/06/2022 10:54 state: close ``` so if I will count those two events I will get zero because this event id has state close at the last ingest time I tried to create a metric visual that have unique count by ID and filter with status=OPEN, but than I get in the count events that the last document status is closed also because it count all the documents with status OPEN Also created data table as follow: ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/6/b684fee1d6c2db5864e155eb89db3ab02e35488f.png) ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/9/b925da6bb947d3f22e0d1e7722cd28104a1f306d.png) if I add filter with status is OPEN I get the events that already has been closed also Any suggestion? Thanks, Talia ---
**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\ **Post date:** [June 29, 2022, 9:20am UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262/2 "2022-06-29T09:20:01Z")
I think I found a way for you to get your table. Take this entry data ```auto DELETE delete_top_status PUT delete_top_status { "mappings": { "properties": { "id": { "type": "integer"}, "status": { "type": "keyword"}, "timestamp": { "type": "date"} } } } PUT delete_top_status/_bulk { "index": {}} { "id": 1, "status": "open", "timestamp": "2022-06-20T10:00:00+0200" } { "index": {}} { "id": 2, "status": "open", "timestamp": "2022-06-20T11:00:00+0200" } { "index": {}} { "id": 3, "status": "open", "timestamp": "2022-06-20T12:00:00+0200" } { "index": {}} { "id": 1, "status": "close", "timestamp": "2022-06-20T14:00:00+0200" } { "index": {}} { "id": 3, "status": "close", "timestamp": "2022-06-20T16:00:00+0200" } { "index": {}} { "id": 1, "status": "open", "timestamp": "2022-06-20T17:00:00+0200" } ``` This will generate an index with three ids: - `1` is opened, closed, and open again - `2` is opened - `3` is opened and closed Create a data view with the index and then in Lens you can have a table like this ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/9/89d04e726b5eb373fd0ef04598169a44d37d7899.png) In Lens you'd have a table visualization by the `id` field and three metrics. ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/5/d5bd30cc60e17f6d9b46bd74c284b0a1874ce8e1.png) - First metric is just a `count` by `status` - Second metric shows the `last value` using the `timestamp` to sort data - Third metric assumes that: - There are only two possible statuses - There's always an `open` and then a `close` event so you can just get the `modulo` function over the `count` by `2` and you'd get a `0` if there is an even number (open: 1, 3, 5, ...), and a `1` if there is an odd number (closed: 2, 4, 6...). ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/d/2d9ba35bdbb6794abf9a33168466774a74cf1408.png) I hope you find this useful! ---
**Author:** ![talial](https://avatars.discourse-cdn.com/v4/letter/t/3be4f8/32.png) [@talial](https://discuss.elastic.co/u/talial)\ **Post date:** [June 29, 2022, 10:41am UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262/3 "2022-06-29T10:41:47Z")
Thank for the full detailed answer. I will try this ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [June 29, 2022, 3:42pm UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262/4 "2022-06-29T15:42:13Z")
Another option is using [latest transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-overview.html#latest-transform-overview) to creat another "latest" index to keep latest documents for each "id". The count of "open" documents in the latest index is the number you want. ---
**Author:** ![talial](https://avatars.discourse-cdn.com/v4/letter/t/3be4f8/32.png) [@talial](https://discuss.elastic.co/u/talial)\ **Post date:** [June 30, 2022, 1:52pm UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262/5 "2022-06-30T13:52:07Z")
Interesting solution I wonder why elk support didn't suggest it. I will try this if it will work it will open to me more visualization to use so it will be grate Thanks ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 28, 2022, 1:52pm UTC](https://discuss.elastic.co/t/unique-count-by-top-status/307262/6 "2022-07-28T13:52:26Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.