Distinct Report based on 2 columns (Text Field )

[Showman (Ahmed Showman)]

Hello,
am new to kibana & elastic
am trying to get a simple report from a table that has different status for the same Refrence id as Follows:

''''''
{
"took": 0,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 6,
"max_score": 1,
"hits": [
{
"_index": "trial14042021",
"_type": "_doc",
"_id": "0",
"_score": 1,
"_source": {
"User Reference": 1,
"Status": "Failed"
}
},
{
"_index": "trial14042021",
"_type": "_doc",
"_id": "1",
"_score": 1,
"_source": {
"User Reference": 1,
"Status": "Failed"
}
},
{
"_index": "trial14042021",
"_type": "_doc",
"_id": "2",
"_score": 1,
"_source": {
"User Reference": 1,
"Status": "Successful"
}
},
{
"_index": "trial14042021",
"_type": "_doc",
"_id": "3",
"_score": 1,
"_source": {
"User Reference": 2,
"Status": "Successful"
}
},
{
"_index": "trial14042021",
"_type": "_doc",
"_id": "4",
"_score": 1,
"_source": {
"User Reference": 3,
"Status": "Failed"
}
},
{
"_index": "trial14042021",
"_type": "_doc",
"_id": "5",
"_score": 1,
"_source": {
"User Reference": 3,
"Status": "Failed"
}
}
]
}
}
'''''''''''''''''''
What i need to do is to get the result of 1 failed User Reference & 2 successful,

while using Kibana visualization it shows 2 failed & 2 successful,
Any one can support
this is the mapping of the index
'''''''''''''''''
{
"trial14042021": {
"mappings": {
"_doc": {
"properties": {
"Status": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"User Reference": {
"type": "long"

}
}
}
}
}
}

''''''''''''''''

[AClerk]

Can you say what is the logic?
Thanks

[Showman (Ahmed Showman)]

Hello Clerk,

The logic is that i have 3 items processed,
The 1st one has retried 3 times, the first 2 runs Status failed & the 3rd run Status successes,
The 2nd ticket Status is Success
the 3rd ticket Status is retried twice & both Failed.

so what i need is to have the final report
Total unique count ticket is 3
Total Success is 2
Total Failed is 1
but what actually got is
Total unique count ticket is 3
Total Success is 2
Total Failed is 2

[AClerk]

Try using top hits aggregation.
That will give you the last status of each document.
Cheers!