Show number of servers with a number of uniques IDs reporting

[tisiman (Matthias Locher)]

I have an X number of records like:

{ server: 1, ID: A1X, @timestamp: "2018-12-28 09:00"}
{ server: 1, ID: A2X, @timestamp: "2018-12-28 09:00"}
{ server: 1, ID: A3X, @timestamp: "2018-12-28 09:00"}
{ server: 2, ID: B1X, @timestamp: "2018-12-28 09:00"}
{ server: 3, ID: C1X, @timestamp: "2018-12-28 09:00"}
{ server: 3, ID: C2X, @timestamp: "2018-12-28 09:00"}
{ server: 4, ID: D1X, @timestamp: "2018-12-28 09:00"}
{ server: 1, ID: A1X, @timestamp: "2018-12-28 10:00"}
{ server: 1, ID: A3X, @timestamp: "2018-12-28 10:00"}
{ server: 2, ID: B1X, @timestamp: "2018-12-28 10:00"}
{ server: 3, ID: C1X, @timestamp: "2018-12-28 10:00"}
{ server: 4, ID: D1X, @timestamp: "2018-12-28 10:00"}

Waht I would like to show in Bargraph (or Pie) is how many servers have X number of IDs reporting. IDs are not reporting every timestamp. IDs are unique Hash Valiues in Real.
The number of servers is about 1000.

Result should be
1 Server with 3 IDs (Server 1)
1 Server with 2 IDs (Server 3)
2 Servers with 1 IDs (Server 2 and 4)

So I should be able to stack Unique Count on top of Unique Count.

Any advice on how to do this? Thanks

[flash1293 (Joe Reuter)]

With the visualization UI it's not possible to chain aggregations like this. You can use a transform though to bring your data into the right shape to execute the query and save the pre-aggregated data: https://www.elastic.co/guide/en/elasticsearch/reference/7.6/ecommerce-transforms.html

By grouping by the server name and adding a cardinality metric on the ID field, the transformed dataset will look like this:

{ server: 1, ID_cardinality: 3 },
{ server: 2, ID_cardinality: 1 },
{ server: 3, ID_cardinality: 2 },
{ server: 4, ID_cardinality: 1 }

Now you can create a visualization based on this "view" of your data to get what you want: Splitting the pie slices by terms of ID_cardinality and using a "Count" aggregation for the size of the slices.

Creating this kind of transformed view can also be helpful for other insights - you can also set the transform job up to continuously add incoming data the view to keep it up to date.