Calculating ratio per date

moti_levi · October 6, 2016, 11:42am

Hi all,
I need some help to figure out if this is something possible to do using Kibana.

this is the result table Im trying to get |o:
.................2016-10-05........2016-10-04........2016-10-03
field1.........ratio%..................ratio%..................ratio%
field2.........ratio%..................ratio%..................ratio%
field3.........ratio%..................ratio%..................ratio%

Where the ratio is calculated s follows:
the sum of each field per day, devided by the sum of the total of that field per day.
for example: field1 for date 2016-10-05 should have the ratio of
(100 + 110) / (150 + 150) = 210/300 = 70%

and this a sample of the data:

{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb",
"_score": 1,
"_source": {
"field1": 100,
"field_name": "field1",
"env": "env1",
"date": "2016-10-05T14:00:00",
"total_counter": 150,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb",
"_score": 1,
"_source": {
"field1": 110,
"field_name": "field1",
"env": "env1",
"date": "2016-10-05T17:00:00",
"total_counter": 150,
"customer_global_id": "customer1"
}
}
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb",
"_score": 1,
"_source": {
"field1": 50,
"field_name": "field1",
"env": "env1",
"date": "2016-10-04T14:00:00",
"total_counter": 100,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb",
"_score": 1,
"_source": {
"field1": 25,
"field_name": "field1",
"env": "env1",
"date": "2016-10-05T14:00:00",
"total_counter": 50,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb1",
"_score": 1,
"_source": {
"field2": 100,
"field_name": "field2",
"env": "env1",
"date": "2016-10-05T15:00:00",
"total_counter": 150,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb2",
"_score": 1,
"_source": {
"field2": 50,
"field_name": "field2",
"env": "env1",
"date": "2016-10-04T15:00:00",
"total_counter": 100,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb3",
"_score": 1,
"_source": {
"field2": 25,
"field_name": "field2",
"env": "env1",
"date": "2016-10-05T15:00:00",
"total_counter": 50,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb11",
"_score": 1,
"_source": {
"field3": 200,
"field_name": "field3",
"env": "env1",
"date": "2016-10-05T16:00:00",
"total_counter": 200,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb12",
"_score": 1,
"_source": {
"field3": 250,
"field_name": "field3",
"env": "env1",
"date": "2016-10-04T16:00:00",
"total_counter": 300,
"customer_global_id": "customer1"
}
},
{
"_index": "mapping4",
"_type": "field_report",
"_id": "AVeVVavr_DKQUzatrBMb13",
"_score": 1,
"_source": {
"field3": 200,
"field_name": "field3",
"env": "env1",
"date": "2016-10-05T16:00:00",
"total_counter": 250,
"customer_global_id": "customer1"
}
}

Thanks to anyone who got this far

LeeDr · October 7, 2016, 6:29pm

Hi moti_levi,

I didn't find a way to do that in a Data Table. There might be a way to use the Advanced field in the Visualization to put in some JSON to get the right results. I'll try to take a look at that too.

But I did figured out a way to chart it with Timelion. Timelion is a plugin in Kibana 4.x, but it's built-in to Kibana 5.0 in beta now.
On my Kibana 4.6 I installed it with /opt/kibana/bin/kibana plugin --install kibana/timelion

Here's the query I used;
.es(index=test8, timefield=date, metric='sum:field1').divide(.es(index=test8, timefield=date, metric='sum:total_counter', q='field1>0') ).multiply(100).points(radius=2) .es(index=test8, timefield=date, metric='sum:field2').divide(.es(index=test8, timefield=date, metric='sum:total_counter', q='field2>0')).multiply(100).points(radius=6) .es(index=test8, timefield=date, metric='sum:field3').divide(.es(index=test8, timefield=date, metric='sum:total_counter', q='field3>0')).multiply(100).points(radius=10)

A little explanation of the query above;
.es(index=test8, timefield=date, metric='sum:field1') gets the field1 sum over the 1d interval I've selected in Timelion

.es(index=test8, timefield=date, metric='sum:total_counter', q='field1>0') gets the sum of the total_counter over the 1d interval, but only where field1>0 otherwise it gets all the total_counters on that day

That total_counter sum is used to divide the field1 sum.

.multiply(100) just makes the numbers more like a percent. There might be a better way to set as a percent format.

which produced this chart;

There's not currently a way to get the data table type of output from Timelion.

Regards,
Lee