Count Of Metric after Doing Top Hits

[misiakj (Jon)]

I have data that looks similar to the following:

(case_id, create_date, status)

I want to be able to select the most recent document for each case_id, and then sum the counts of the different statuses. So if my data looked like the below

case_id: 1 | create_date 2019-2-20-00:00:00 | closed
case_id: 1 | create_date 2019-1-20-00:00:00 | working
case_id: 1 | create_date 2019-1-10-00:00:00 | assigned

case_id: 2 | create_date 2019-2-10-00:00:00 | closed
case_id: 2 | create_date 2019-1-00-00:00:00 | assigned

case_id: 3 | create_date 2019-1-10-00:00:00 | assigned

case_id: 4 | create_date 2019-1-20-00:00:00 | working
case_id: 4 | create_date 2019-1-10-00:00:00 | assigned

The results would show

assigned: 1
working: 1
closed: 2

I would like to do this in a Kibana visualization, is this possible?
Below I have uploaded an image where I'm able to get a data table showing the latest status of each ticket, now I just need a "Count" of each of those statuses.

[tsullivan (Tim Sullivan)]

The most straightforward and scalable way would be to have an index of data modeled separately. Here, instead of an index of raw events, having a caseId centric index would make this an easy aggregation.

The caseId index would use case_id as the _id for the ES docs. When events come in, update the case document by ID and it will have the latest status.

The table then becomes an aggregation of each status keyword, and the count of documents in the case index that have that status.