Pie Chart with UNIQUE COUNT and Bucket AGGREGATION with TOP HIT

We have a problem creating a Pie Chart, there’s no way to implement this aggregation in Kibana.

We want to make a Unique Count of ID and make the aggregation with the LAST HIT OF STATE (last hit considering the last TIMESTAMP). For example, we have the following LOGS:

{“ID”:”1”, “STATE”:”Start”, “TIMESTAMP”:”01/01/2019”}

{“ID”:”2”, “STATE”:” Start”, “TIMESTAMP”:”01/01/2019”}

{“ID”:”3”, “STATE”:” Start”, “TIMESTAMP”:”01/01/2019”}

{“ID”:”4”, “STATE”:” Start”, “TIMESTAMP”:”01/01/2019”}

{“ID”:”1”, “STATE”:”In progress”, “TIMESTAMP”:”03/01/2019”}

{“ID”:”2”, “STATE”:”In progress”, “TIMESTAMP”:”02/01/2019”}

{“ID”:”1”, “STATE”:”Pending”, “TIMESTAMP”:”02/01/2019”}

{“ID”:”2”, “STATE”:”Pending”, “TIMESTAMP”:”05/01/2019”}

{“ID”:”3”, “STATE”:”Pending”, “TIMESTAMP”:”02/01/2019”}

{“ID”:”1”, “STATE”:”End”, “TIMESTAMP”:”04/01/2019”}

And we want a Pie Chart that represents the following count:

Start: 1 (ID = 4)

In progress: 0

Pending: 2 (ID = 2 and ID = 3)

End: 1 (ID = 1)

We tried to create a DSL query that gives us only the lost with the last state, like this:

{“ID”:”1”, “STATE”:”End”, “TIMESTAMP”:”04/01/2019”}

{“ID”:”2”, “STATE”:” Pending”, “TIMESTAMP”:”05/01/2019”}

{“ID”:”3”, “STATE”:”Pending”, “TIMESTAMP”:”02/01/2019”}

{“ID”:”4”, “STATE”:” Start”, “TIMESTAMP”:”01/01/2019”}

But we don’t know how to save this query as SAVE SEARCH so we can create a pie chart later with this saved search.

How can we create a pie chart as described above or save a DSL as a Save search?

Hello @MarcFajula
Thanks for the request.
Unfortunately is not possible to save an aggregation like that in a saved search. You can modify only the query on saved search editing the QueryDSL of a filter.

I'm currently investigating if it's possible to create a top hit aggregation on a piechart. I will keep you posted on that

Hey @markov00,

It looks like the option exists in the drop-down for "Top Hit" but no matter what field is selected, no options appear for the "Aggregate with" field. I'm having problems with this as well trying to show last statuses for test cases. (Posted here: Visualization for Automation Test Reporting)

Hi @drunningen,
sorry for the late reply.
So the Top Hits aggregation, on a piechart, works only with numbers on a pie chart.
To use it you have to first create buckets using split slices, and than use the top hit aggregation, that aggregate the top hit results, sorted by the second field on the editor, on each specific bucket with the specified aggregation function (sum, min, max, avg).
I think we have two bugs there: one is that we are showing one unneccessary input field just before the sort on input. The other bug is related to the wrong list of fields available for the aggregation, we should filter that list down to only numbers.

Anyway, unfortunately your case is a bit particular because you are looking to count the number of unique Ids in a specific state on the last available timestamp.

Maybe it's worth looking at building a Vega visualization, that will allows you to use a custom DSL for your query input

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