How to create a chart using a calculated value from fields?

[elasticheart]

Hi,

I am using ELK GA 5.0.0. I have an elasticsearch index, having a string field datax. That field has values like DataA, DataB, DataC, DataE, and DataE.

I have created a line chart, In Y-Axis, I selected Count. In X axis, I added aggregation of date Histogram. Then I did Split Lines -> Sub Aggregation -> Filters and added DataA, DataB, and DataC so that I get count of logs containing these values only. Then I made another filter and added DataE, and DataE, so that the line shows count of these 2 fields only.

The graph looked similar to the image below;

What I am trying to achieve is not this. I want a single line chart. The value of line should be (DataA+DataB+DataC) / (DataD+DataE) . For example, at time T, if the first line has a count of 20, and second line has a count of 2, I want my line to show 20 / 2, which is 10.

Is this possible, or is there any workaround?

Thanks in advance.

[weltenwort (Felix Stürmer)]

Hi @elasticcloud,

I think there is misunderstanding of how the "filter" aggregation works. It partitions the data into buckets that each match the given filter. So to create a filter that separates DataA and DataB from DataC you would create one filter with datax:(DataA OR DataB) and another one with datax:DataC. With Kibana 5.0 you can not calculate the difference between those counts in a visualization though.

You should be able to achieve this using a Timelion-based "Timeseries" visualization, roughly:

.es(q="datax:(DataA OR DataB OR DataC)", index="YOURINDEX", metric="count").divide(.es(q="datax:(DataD OR DataE)", index="YOURINDEX", metric="count"))

[elasticheart]

Hi @weltenwort , yea I understand. Could you tell me how to get rid of divide by zero? If 0, I want to display the result as -1. How can I do that?

[weltenwort (Felix Stürmer)]

You can use a combination of .if() and .multiply for that. So let's abbreviate .es(q="datax:(DataA OR DataB OR DataC)", index="YOURINDEX", metric="count") as S1 and .es(q="datax:(DataD OR DataE)", index="YOURINDEX", metric="count") as S2 for the sake of this explanation. Then the resulting query could look like this:

S1.divide(S2.if("eq", 0, S1.multiply(-1)))