Metrics from Aggregation results across two different buckets

Elastic Stack Elasticsearch
1

Plesse help

Index has data like below

{ID:ID1, Status:Status1, timeStamp:Timestamp1}
{ID1 - Status2 - Timestamp 2}
{ID1 - Status3 - Timestamp 3}

{ID2 - Status 1 - Timestamp 2}
{ID2 - Status 2 - Timestamp 3}

{ID3 - Status 2 - Timestamp 2}
{ID3 - Status 3 - Timestamp 3}

{ID4 - Status 1 - Timestamp 1}

When I search after Timestamp 3, I would like to get the count of IDs per Latest Status for that ID
e.g.
Status3 - Count 2 IDs (because of ID1 & ID3)
Status2 - Count 1 ID (because of ID2)
Status1 - Count 1 ID (because of ID4)

2

I hope I'm understanding you correctly, but this seems pretty straightforward actually. If you are just going to be querying for 1 TS at once

GET <index>/_search
{
  "size": 0,
  "query": {
    "term": {
      "timestamp": <Timestamp3>
    }
  },
  "aggs": {
    "statuses": {
      "terms": {
        "field": "status"
        // You may want to add additional params to the terms
        // aggregation here to set the # of buckets or ordering for example
      },
      "aggs": {
        "ids": {
          "cardinality": {"field": "ID"}
        }
      }
    }
  }
}

You can also do it w/ multiple timestamps at once if you want--you could use either two terms aggregations or a composite aggregation

3

Thanks for your revert ...
the solution which you propose does give the status count back ...
But
What I would like to have is the count of Top status (derived by Sorting on timestamp: desc)

At the moment it is giving status count per field ID but i would like to get a total count of status depending on whether the status is latest status for that ID group

Please see if you can help think further

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.