Unique Count Conditions

Hello, I need to do an unique count of items that are duplicates, let me explain.
We use customer.id , it is unique for every customer. What we would like to visualize is only the customer.id's that are logged more than once in our index.

Example
Tom - Appears once
Jhon - Appears twice
Simon - Appears tree times

We only want to count Jhon and Simon, so the count should be 2 instead of 3.
Is this doable in Kibana or not ?

I can't think of a way to do this with a regular visualization, but by using transforms (https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-overview.html) you can basically pre-aggregate your data and store the result in a separate index.
So

{ customer.id: "Tom" }
{ customer.id: "John" }
{ customer.id: "John" }
{ customer.id: "Simon" }
{ customer.id: "Simon" }
{ customer.id: "Simon" }

becomes

{ customer.id: "Tom", "count": 1 }
{ customer.id: "John", "count": 2 }
{ customer.id: "Simon", "count": 3 }

On the second index you can now create a metric visualization, filter by count > 1 and show the "Unique count" of customer.id - this will give you the value you need.

If your source index has a timefield, transforms can also be set up to continuously run in the background pre-aggregating incoming data.

1 Like

Thank you, just what I needed.
I feel I can't use time options tho?
Changing date frame and adding data historygram does nothing.
I have added @timestamp as an aggregate as well.

Hi Everyone. Can I use "Transform" from elasticsearch for Basic License?
Actually I'm working on the basic license of Kibana and I'm unable to find that "Transform" panel in elasticsearch. I'm currently using Kibana 6.8.7

Please help!

Dataframe transforms were added in 7.3.0, so you would have to upgrade your stack. It's available in the basic license though.

1 Like

Thank you so much @flash1293

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