Kibana visualize using unique count by latest records

[Ibrahim_Z_HIDIR]

Hi all

After some search and try, I could't find a way to solve my problem. I just want to display the counts of records depending of latest records of some repeating columns.

For Example,

the latest status of "A" is "confirmed"
the latest status of "B" and "C" is "registered"
all others "waiting"

The result that I want to see is,

But I could't succeeded, any kind of visualization ok for me...

thanks

[jsanz (Jorge Sanz)]

I don't think there is at this moment a way to craft a visualization from Lens or TSVB so I came with a solution using Kibana custom visualization (Vega).

First, let's create some data similar to your example

# Create an index
PUT discuss-316236
{
  "mappings": {
    "properties": {
      "date": { "type": "date"},
      "person": { "type": "keyword"},
      "status": { "type": "keyword"}
    }
  }
}

# Bulk upload some data
PUT discuss-316236/_bulk
{ "index": {} }
{ "date": "2022-10-24T09:01:00+0200", "person": "A", "status": "waiting" }
{ "index": {} }
{ "date": "2022-10-24T09:02:00+0200", "person": "A", "status": "registered" }
{ "index": {} }
{ "date": "2022-10-24T09:03:00+0200", "person": "A", "status": "confirmed" }
{ "index": {} }
{ "date": "2022-10-24T09:01:00+0200", "person": "B", "status": "waiting" }
{ "index": {} }
{ "date": "2022-10-24T09:02:00+0200", "person": "B", "status": "registered" }
{ "index": {} }
{ "date": "2022-10-24T09:01:00+0200", "person": "C", "status": "waiting" }
{ "index": {} }
{ "date": "2022-10-24T09:02:00+0200", "person": "C", "status": "registered" }
{ "index": {} }
{ "date": "2022-10-24T09:01:00+0200", "person": "D", "status": "waiting" }
{ "index": {} }
{ "date": "2022-10-24T09:01:00+0200", "person": "E", "status": "waiting" }
{ "index": {} }
{ "date": "2022-10-24T09:01:00+0200", "person": "F", "status": "waiting" }

For Kibana to build the viz, we can create a query that aggregates our data by person, and then gets the last value by date:

# The working query is a terms agg with a top_hits to get the last status
GET discuss-316236/_search
{
  "size": 0,
  "aggs": {
    "byperson" : {
      "terms": {
        "field": "person", 
        "size": 100
      },
      "aggs": {
        "topdate": {
          "top_hits": {
            "size": 1,
            "sort": [
              {
                "date": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}

This returns a data where we can extract the different status for aggregation on the browser. There may be a way to do this directly in Elasticsearch but I haven't been able to find it.

With this, in Kibana you can create a custom visualization that takes this query and generates for example a pie chart with the different statuses:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "title": "Discuss 316236 viz",
  "data": {
    "url": {
      "%context%": true,
      "%timefield%": "date",
      "index": "discuss-316236",
      "body": {
        "aggs": {
          "byperson": {
            "terms": {"field": "person", "size": 100},
            "aggs": {
              "topdate": {
                "top_hits": {"size": 1, "sort": [{"date": {"order": "desc"}}]}
              }
            }
          }
        },
        "size": 0
      }
    },
    "format": {"property": "aggregations.byperson.buckets"}
  },
  "transform": [
    {
      "aggregate": [{"op": "count", "as": "status_count"}],
      "groupby": ["topdate.hits.hits[0]._source.status"]
    }
  ],
  "mark": "arc",
  "encoding": {
    "theta": {"field": "status_count", "type": "quantitative"},
    "color": {
      "field": "topdate.hits.hits[0]._source.status",
      "type": "nominal",
      "legend": {"title": "Status"}
    }
  }
}

Mind that this will honor the search bar and time picker (%context%: true) so be sure to grab the correct time range as always in Kibana.

If you prefer other chart types you can check the Vega lite examples for inspiration.

Hope this works!

[Ibrahim_Z_HIDIR]

Thanks for solution, after many different attempt, your solution is the only one.