Metrics in visualization, calculate a field sum in JSON input script

Elastic Stack Kibana
1

I have a Metric control in Canvas which uses this query:

SELECT 
	SUM(AbstractData.no_of_assets_with_SIR) * 100.0 / SUM(AbstractData.count_total_assets) as percentage
FROM 
	cpg_abstract_dev_project_kpi

Now I need to do the same thing as an Visualization on a Dashboard instead and I'm struggling with achieving the same result.

My approach so far has been to use a Metrics control in visualization, aggregate using SUM on the field "AbstractData.no_of_assets_with_SIR" and then add a script in JSON input to do the rest of the calculation (multiply "_value" with 100 and then divide with the sum of the field AbstractData.count_total_assets). So far I have not been able to figure out how to get the sum of the field AbstractData.count_total_assets in the script. If not possible to calculate that sum directly in the script, I'm guessing that I would need yet another SUM aggregation, but I can´t see how I can do that in the Metric control. Any push in the right direction to solve this would be much appropriated!

This is my script so far:
{
"script": {
"lang": "painless",
"inline": "double total = 0.0; total = _value * 100; return total;"
}
}

I would also need to divide the "total" in the script with the sum of the field AbstractData.count_total_assets

Best regards
/Magnus

2

I think the Visual Builder visualization is your best option for a metric that includes that calculation. It includes a ratio selection. It's also known as "Time Series Visual Builder" or tsvb.

https://www.elastic.co/guide/en/kibana/current/time-series-visual-builder.html

There are some discuss posts here on it, as well as some YouTube videos you can find pretty easy.

Let us know if you need help with it.

Regards,
Lee

3

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