How to aggregate data like mysql query? - kibana

[bu.gworks.mobi (Bilal Usean)]

Below mysql query return life time sales report and avg sales report from sales_order table. I have pushed sale_order table into elasticsearch but I don't know how to aggregate for the below query in kibana to view life time and avg sales report.

SELECT 
SUM((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(main_table.base_tax_refunded, 0) - IFNULL(main_table.base_shipping_refunded, 0))) * main_table.base_to_global_rate) AS `lifetime`, 
AVG((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(main_table.base_tax_refunded, 0) - IFNULL(main_table.base_shipping_refunded, 0))) * main_table.base_to_global_rate) AS `average` 
FROM `sales_order` AS `main_table` 
WHERE (main_table.status NOT IN('Canceled')) AND (main_table.state NOT IN('new', 'pending_payment'))

above query return results like

I have tried in metric table but it only sum up the one column value.

can anyone assist me what is the right way to display reports in kibana?

[LeeDr (Lee Drengenberg)]

Hi Bilal,
It would probably be best to get the "net" value in to Elasticsearch as the data is being ingested, so you would have one field in Elasticsearch which has the result of;

  main_table.base_total_invoiced 
- main_table.base_tax_invoiced 
- main_table.base_shipping_invoiced 
- main_table.base_total_refunded 
- main_table.base_tax_refunded 
- main_table.base_shipping_refunded
 *
 main_table.base_to_global_rate 

But another option is to create a scripted field on the index in Kibana which combines those values. Let me know if you need help creating the scripted field.

In either case, then it should be easy to create a Data Table (or another other visualization type) with SUM and AVG using that new field.

Regards,
Lee

[bu.gworks.mobi (Bilal Usean)]

Hi @LeeDr ,

I am stuck with write script field in kibana. e.g) calculation of life time sales

actual key mysql query to calculate lifetime sales is

SUM(
		(
			IFNULL(main_table.base_total_invoiced, 0) - 
			IFNULL(main_table.base_tax_invoiced, 0) - 
			IFNULL(main_table.base_shipping_invoiced, 0) - 

			(
				IFNULL(main_table.base_total_refunded, 0) - 
				IFNULL(main_table.base_tax_refunded, 0) - 
				IFNULL(main_table.base_shipping_refunded, 0)
			)
		) * main_table.base_to_global_rate
	) AS `lifetime`

below one express above calculation in a simple manner

a = sum up all the base_total_invoiced field value
b = sum up all the base_tax_invoiced field value
c = sum up all the base_shipping_invoiced field value

d = sum up all the base_total_refunded field value
e = sum up all the base_tax_refunded field value
f = sum up all the base_shipping_refunded field value

g = sum up all the base_to_global_rate field value

lifetime=a-b-c-(d-e-f)*g

I don't know, how to get the total value of each field? If I get, I can easily apply the above formula

below one is my try in script field but it is return one record for each row which is not right.

doc['base_total_invoiced'].value - doc['base_tax_invoiced'].value - doc['base_shipping_invoiced'].value -( doc['base_total_refunded'].value - doc['base_tax_refunded'].value - doc['base_shipping_refunded'].value)*doc['base_to_global_rate'].value

[LeeDr (Lee Drengenberg)]

Hi Bilal,

I was thinking that the lifetime could also be expressed as

lifetime=sum( base_total_invoiced - base_tax_invoiced - base_shipping_invoiced - ((base_total_refunded- base_tax_refunded - base_shipping_refunded) * base_to_global_rate)

So the scripted field calculates each net, and the Kibana aggregation does the sum or avg.

Here's an example. I have some numeric data in fields named machine.ram and bytes. I can create a scripted field which is the difference of those for each doc like this;

Now if I create a new Data Table visualization and add 3 metric, sum, for the 2 real fields plus the 1 scripted field I get these results. If you subtract the sum(machine.ram) - sum(bytes) you see that it's equal to sum(ramMinusBytes);

With Painless scripts in 5.0, you can also use slightly more complex scripted fields and check for things like your canceled, new, pending, etc. Here's an example (with different fields and values obviously);

if (doc['extension.raw'].value == "png") return doc['machine.ram'].value - doc['bytes'].value; else return 1;

BUT, if you really want to take the difference of the sums, instead of the sum of the differences, you could use Timelion (was a plugin for Kibana 4.x, and is built-in to Kibana 5.x).
But it only shows charts, not data tables. There's a pretty good tutorial built right in for Timelion. If you need help with it let me know.

Regards,
Lee

[tbragin (Tanya Bragin)]

If interested, there is now a new blog on Painless in Kibana scripted fields that has examples and best practices on how to create them: https://www.elastic.co/blog/using-painless-kibana-scripted-fields