Kibana JSON input to calculate the percentage of two fields and display in metric visualisatio

[raghav1107 (Raghavendra)]

Hi,

I am using the below code in the json input to calculate the overall percentage of two fields and display in metric visualisation. But i am getting empty value in the metric display.

Here i want to calculate the sum of rows for field A and sum of rows for field B and then calculate the percentage.
Can you please help me here ? I am using cabana version 6.1.1.

{
"script": {
"lang":"painless",
"inline": "doc['automated'].sum()*100/doc['iters'].sum()"
}
}

[Joe_Fleming]

I'll admit it's been a while since I used the JSON Input field, but I don't think you can use it like that. That input is more of a fallback for Elasticsearch Query DSL params you want to add to the aggregation that Kibana doesn't have a UI for.

You can create a scripted field in Management > Index Patterns, which is kind of like a virtual field in Kibana driven by the script. Basically, you add a new field and the script you posted is the value, then you'll have a new field available in the dropdown throughout Kibana. Then you can use that new field in the metric vis.

[raghav1107 (Raghavendra)]

Adding scripted field will apply for all the rows in the index and will not apply when we add a new filter in the visualisation. Lets say there are 10 rows with field A, field B, field C in the index and want to calculate the percentage between field A/field B based on the filter for field C , then this will not work. Please suggest if there is any other way to achieve this.

[Joe_Fleming]

Lets say there are 10 rows with field A, field B, field C in the index and want to calculate the percentage between field A/field B based on the filter for field C , then this will not work.

Won't it? With the scripted field, you effectively end up with a table of 4 columns; A, B, C, and the painless output. So filtering on C will reduce the total number of rows, but every one will still have the scripted column, and you can still calculate the metric value from that column.

Here's a contrived example, using the sample flight data available in recent version of Kibana:

Here, hour_of_day is a scripted field, and Carrier is not. I'm creating a metric that is the sum of hour_of_day and filtering based on Carrier. Based on my understanding of what you're trying to do, it's basically the same operation. Maybe I'm missing something though?

Disabling the column shows the total sum of hour_of_day: