Unique count by top status

Elastic Stack Kibana
1

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:

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
image

if I add filter with status is OPEN I get the events that already has been closed also
Any suggestion?

Thanks,
Talia

2

I think I found a way for you to get your table.

Take this entry data

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

In Lens you'd have a table visualization by the id field and three metrics.

image
image993×722 32.2 KB

  • 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
image568×773 28.6 KB

I hope you find this useful!

3

Thank for the full detailed answer.
I will try this

4

Another option is using latest transform 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.

5

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

1 Like
6

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