Sum of field of latest unique values

[Werring]

I have an X number of records like:

{ server: 1, size: 813, @timestamp: "2018-12-28 09:00"}
{ server: 2, size: 654, @timestamp: "2018-12-28 09:00"}
{ server: 3, size: 752, @timestamp: "2018-12-28 09:00"}
{ server: 1, size: 915, @timestamp: "2018-12-28 10:00"}
{ server: 2, size: 823, @timestamp: "2018-12-28 10:00"}
{ server: 3, size: 783, @timestamp: "2018-12-28 10:00"}

What I would like is to show the latest total size (2521) of all the unique servers, preferably displayed with the "metric" visualisation.
(915 + 823 + 783 = 2521)

I've tried using the Top Hit Aggregation, but that requires me to set a fixed size.
The number of servers could change by the day.

Currently using Kibana 6.5.4

[Werring]

bump

[Bargs (Matt Bargar)]

There's no way to achieve this with a regular Kibana visualization, but you could do it with Vega.

In plain english what you'll need to do is create a terms agg on the server field and then do a top_hits agg with a descending sort on @timestamp. This will give you an aggregation response with a bucket for each unique server which contains the latest document for that server (the number of unique servers is still bounded by the size param of the terms agg, but you need some upper bound to guarantee the query won't harm your cluster). You can then use Vega transforms to grab the size from each document and sum them together to get the latest total size of all unique servers.

I wrote up an example which you can see below. I'm using a slightly different data set, but just pretend clientip is server and bytes is size.

{
  $schema: https://vega.github.io/schema/vega/v3.0.json
  title: Sum of Latest Bytes Per ClientIP
  data: [
    {
      name: latestBytes
      url: {
        %context%: true
        %timefield%: @timestamp
        index: _all
        body: {
          aggs: {
            clientips: {
              terms: {
                field: clientip
                size: 5
                order: {_key: "desc"}
              }
              aggs: {
                bytes: {
                  top_hits: {
                    _source: bytes
                    size: 1
                    sort: [
                      {
                        @timestamp: {order: "desc"}
                      }
                    ]
                  }
                }
              }
            }
          }
          size: 0
        }
      }
      format: {property: "aggregations.clientips.buckets"}
      transform: [
        {
          type: aggregate
          ops: ["sum"]
          fields: ["bytes.hits.hits[0]._source.bytes"]
          as: ["totalBytes"]
        }
      ]
    }
  ]
  marks: [
    {
      type: text
      from: {data: "latestBytes"}
      encode: {
        update: {
          text: {signal: "format(datum.totalBytes, ',')"}
          align: {value: "center"}
          baseline: {value: "middle"}
          xc: {signal: "width/2"}
          yc: {signal: "height/2"}
          fontSize: {signal: "min(width/10, height)/1.3"}
        }
      }
    }
  ]
}

[Werring]

Awesome that works!

thanks