How to aggregate data by an field?

Elastic Stack Elasticsearch
1

Hello,
I'm using kibana 4.4.1 and in elasticsearch I store the status of PC, only when PC status is changed (open, closed, warings, etc)

My data into Elasticsearch looks like:
{ "status_id":1 , "pc":"lpt001" , "date":"2016-10-25T17:49:00Z" }
{ "status_id":3 , "pc":"lpt001" , "date":"2016-10-25T15:48:00Z" }
{ "status_id":4 , "pc":"lpt002" , "date":"2016-10-25T15:46:00Z" }
{ "status_id":1 , "pc":"lpt002" , "date":"2016-10-25T12:48:00Z" }

And I what to get the newest record in order to have at any time how many PC's are opened, closed or have some issues.
My query is like:

GET cb-2016.10.26/_search
{
  "query": {
    "match_all": { }
  },
  "sort": [
    {
      "date": {
        "order": "desc"
      }
    }
  ], 
  "aggs": {
    "max_date":{
      "max": {
        "field": "date"
      }
    }
  }
}

And the result is:

"aggregations": {
    "max_date": {
      "value": 1477417680000,
      "value_as_string": "2016-10-25T17:48:00.000Z"
    }
  }

But What I want is to have that max_date for each "pc": "lpt001", "lpt002".

There is any way to split max_date by "pc" field? I read something about bucket aggregations but I did not reach the result.

Thank you,
Ovidiu

2

If I understand correctly, you want the latest entry for each PC. This can be achieved with the following query:

{
  "query": {
    "match_all": { }
  },
  "aggs" : {
        "pcstatus" : {
            "terms" : {
                "field" : "pc"
            },
            "aggs": {
                "top_date_hit": {
                    "top_hits": {
                        "sort": [
                            {
                                "date": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "size" : 1
                    }
                }
            }
        }
    }
}
1 Like
3

yes,
you're right!

when I run this into sense it works as I expected.

But there is any way to integrate it into kibana? Because the final target is to make a pie with this data. :slight_smile:

4

As far as I know the top_hits aggregation cannot be used in Kibana. Maybe ask on the Kibana forum?

5

yes, seems like kibana don't allow aggregations: Discover: No query registered for [aggs]

Before came here I have tried on kibana forum but someone redirect me to elasticsearch forum. Probably I should rephrase my question. I hope now I have more clear difference between kibana queries and elasticsearch queries.

Thank you for help and have a nice weekend.