Two fields

(Shah) #1

How can I get division of two different fields? For example I want to get the following value and check if it is more than 95% is:
Count(successful attempts)/count(attempts)*100

If it is I want to show it in green light, otherwise red light.

(Joe Fleming) #2

Field formatters can probably be used to turn the value into a color, but there's no way in Kibana to calculate percentage like that, because Kibana only does with Elasticsearch does, and that functionality doesn't exist in Elasticsearch. You'd have to query both values (which is simple) and then do the math operation to get the value (also simple), but that second step is not something Kibana does.

I believe you can do this with the new SQL query syntax in Elasticsearch, but Kibana doesn't use that yet.

You can do it in Canvas, but we don't yet have inter-op between Canvas elements and Kibana visualizations or dashboards, so you wouldn't be able to mix and match or reuse other visualizations you already created.

(Shah) #3

Hello. Thank you for your reply. I would be grateful if you could explain with more details about the query.
I am also wondering if possible to divide two different metrics and if yes, how it can be done.
Thank you for your help.

(Joe Fleming) #4

Having given this some more thought, I think you actually can calculate the success percentage in Kibana, but depending how the value in that field is stored will make a difference. If it's just a 1 or 0 value, that's pretty simple. If it's not, you can cast it into 0 or 1 using a scripted field.

Once you have a number there, you can just ask for the average value of that field as your metric and you'll know the success percentage for each bucket (or all documents if you're not aggregating on other fields). And I believe using a metric vis would allow you to show the number in a specific color based on the value.

Doing this in SQL would be something like this:

SELECT COUNT(successful_attempt) / COUNT(*) & 100 AS success_percent FROM "your_index_or_pattern*"

Again, assuming that the successful_attempt field here had a 0 or 1 value in it. And a WHERE clause can be used to query a selected time range.

(system) closed #5

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