Kibana: How to create a table that shows the count of the top_hits for a field

Question

I have documents with fields account_id(primary key for my relational db) and status. Whenever there is a change in status of the account, I pushed the event to the ElasticSearch.

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

The output should be:

**status** **count**
 delete   1
 save     1
 draft    1

ELK: 5.6

Answer 1

This feature has been requested in Kibana since at least 2013. We stopped waiting, and used graphana, which can do it. If you can follow all the github references, it looks like elastic might have a way to do it now.

Answer 2

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 }
}