# Kibana: How to get the top hits for a field and then do a count of them in terms of another field
**URL:** https://discuss.elastic.co/t/kibana-how-to-get-the-top-hits-for-a-field-and-then-do-a-count-of-them-in-terms-of-another-field/106446
**Category:** Kibana
**Created:** [November 5, 2017, 8:40pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-the-top-hits-for-a-field-and-then-do-a-count-of-them-in-terms-of-another-field/106446 "2017-11-05T20:40:55Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@suchitgupta](https://discuss.elastic.co/u/suchitgupta)
#### Post date: [November 5, 2017, 8:40pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-the-top-hits-for-a-field-and-then-do-a-count-of-them-in-terms-of-another-field/106446/1 "2017-11-05T20:40:55Z")
I have documents with fields account\_id(primary key for my relational db) and status. Whenever there is a change in the status of the account, I pushed the event to the Elasticsearch with the account\_id and status.
So over a period of time there will be multiple documents for an account with different statuses.
My requirement is to get the recent document for each account\_id and do a count in terms of the status.
SQL equivalent "select status, count(\*) from account group by status ;"
Example: consider following logs
> account\_id: 1, status:delete, @timestamp: November 4th 2017, 18:42:09.445
> account\_id: 1, status:save, @timestamp: November 3rd 2017, 18:42:09.445
> account\_id: 1, status:draft, @timestamp: November 2nd 2017, 19:42:09.445
> account\_id: 2, status:draft, @timestamp: November 3rd 2017, 19:42:09.445
> account\_id: 3, status:save, @timestamp: November 4th 2017, 18:42:09.445
> account\_id: 3, status:draft, @timestamp: November 3rd 2017, 19:42:09.445
Output should be like:
> status count
> delete 1
> save 1
> draft 1
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [November 7, 2017, 5:10pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-the-top-hits-for-a-field-and-then-do-a-count-of-them-in-terms-of-another-field/106446/2 "2017-11-07T17:10:06Z")
Hmm, after playing around with this, I'm not sure it's possible to do what you're wanting. I can't even really think of a way to get an Elasticsearch query to get the results you are wanting here without possibly using some sort of script. I think it boils down to not being able to do sub aggregations on a top hits aggregation.
---
### Author:  [@suchitgupta](https://discuss.elastic.co/u/suchitgupta)
#### Post date: [November 7, 2017, 6:45pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-the-top-hits-for-a-field-and-then-do-a-count-of-them-in-terms-of-another-field/106446/3 "2017-11-07T18:45:01Z")
Thanks for the response!! I wish that feature was in Kibana, it would have added a lot of value into our project.
For now as a workaround, I updated my logstash.conf so as to push the same log message to two indexes. In first index, I always create a new record and in the second index, I create/update the record.
Snippet of the logstash.conf
> output {
> if [index\_name] == "Account" {
> elasticsearch {
> hosts =\> ["IP:PORT"]
> index =\> "account"
> }
> }
> stdout { codec =\> rubydebug }
> }
> output {
> if [index\_name] == "Account" {
> elasticsearch {
> hosts =\> ["IP:PORT"]
> index =\> "account\_latest"
> document\_id =\> "%{field\_id}"
> }
> }
> stdout { codec =\> rubydebug }
> }
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 5, 2017, 6:45pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-the-top-hits-for-a-field-and-then-do-a-count-of-them-in-terms-of-another-field/106446/4 "2017-12-05T18:45:05Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.