Show records based on last modified date in Kibana

[Farah_Zaidi]

Hi All ,
I have a requirement where we have same ids with different status but we need to show only the last status of the id based on last modified date .
For Example
{id : 123,
status:validated,
modifiedAt:Oct 18, 2021 @ 10:48:08.096},
{id : 123,
status:processing,
modifiedAt: Oct 18, 2021 @ 10:52:08.096}',
{id : 123,
status:finished,
modifiedAt:Oct 18, 2021 @ 10:55:08.096}

Can guide on how we can create Visualize in Kibana to achieve the same .

TIA

[warkolm (Mark Walkom)]

Use a terms aggregation on the id field and then a top hits aggregation on the modifiedAt field. That should do it.

[Farah_Zaidi]

I tried with same but i want to count the total number of ids failed or completed based on ids last status but when i group them with status , it includes same ids as twice .
I am trying to do this in Kibana Visualize

[padamrai]

Dear, What is your last status in the modification process?

[Farah_Zaidi]

Finished

[padamrai]

Then use status.keyword:"Finished" in the filter bar of the visualization. Then count a number of records in the visualization.

[Farah_Zaidi]

The issue is same id can have both Validate & Finish status.
But I want to cater only the status as per last modified date. Currently same id is counted in both Total number of validated records & Total number of Finished records.

[padamrai]

Is it possible to override your previous record with the new incoming record?

[warkolm (Mark Walkom)]

Does this look like what you want;

Here's the sample data I created;

PUT test/
{
  "mappings": {
    "properties": {
      "id": {
        "type": "short"
      },
      "status": {
        "type": "keyword"
      },
      "modifiedAt": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      }
    }
  }
}

PUT test/_doc/0
{
  "id": 0,
  "status": "validated",
  "modifiedAt": "2021-10-18 10:48:08"
}

PUT test/_doc/1
{
  "id": 123,
  "status": "validated",
  "modifiedAt": "2021-10-18 10:48:08"
}

PUT test/_doc/2
{
  "id": 123,
  "status": "processing",
  "modifiedAt": "2021-10-18 10:52:08"
}

PUT test/_doc/3
{
  "id": 123,
  "status": "finished",
  "modifiedAt": "2021-10-18 10:55:08"
}

PUT test/_doc/4
{
  "id": 234,
  "status": "validated",
  "modifiedAt": "2021-10-18 10:48:08"
}

PUT test/_doc/5
{
  "id": 234,
  "status": "processing",
  "modifiedAt": "2021-10-18 10:52:08"
}

PUT test/_doc/6
{
  "id": 234,
  "status": "finished",
  "modifiedAt": "2021-10-18 10:55:08"
}

Then set the table up with this;

[Farah_Zaidi]

Hi Mark,

Yeah this is the same use case  but I need to count the total number of unique  ids with status as failed.
I tried above approach,   but I am still getting the duplicate values in table (PFA below).
Also if I further  add filter with status as validated , it will  still show the id  whose last status  is Finished already



Also currently i have mappings as below-
{
   "my-index":{
      "mappings":{
         "properties":{
            "payload":{
               "properties":{
                  "ID":{
                     "type":"text",
                     "fields":{
                        "keyword":{
                           "type":"keyword",
                           "ignore_above":256
                        }
                     },
                     "modifiedAt":{
                        "type":"date"
                     }
                  }
               }
            }
         }
      }
   }
}
Do we need to update something in mappings as well.


TIA

[Farah_Zaidi]