Split and use the term in sub operation in Timelion

[anthony-o]

Here is my index structure: type, from_host, to_host, times
The "type" field can be either "error" or "valid".
I would like to display distinct lines in Timelion corresponding to top 5 "from_host" keywords, and the y value should equals to the sum of "times" field of "type=valid" lines minus the sum of "times" field of "type=valid" lines for the selected "from_host" of this split.

I have tried this without success: .es(split=from_host.keyword:5, metric=sum:times, q=type:valid).subtract(.es(metric=sum:times, q=type:error))

The problem here is that the from_host that is selected in the first .es() query is not naturally injected in the second one, so the sum is done on all from_hosts in the second .es() query.

Is it possible to do this, and if yes, how?

[Bill_McConaghy]

So I see that your query has "substract" instead of "subtract" -- maybe that is the issue?

[anthony-o]

That was a typo in my question, I did use subtract in my problematic query.

[Bill_McConaghy]

I have created this against the data created by the makelogs script and it seems to work for me
.es(split=extension.raw:5, metric=sum:bytes, q=response:200).subtract(.es(split=extension.raw:5, metric=sum:bytes, q=response:404))

So I think this formulation will work for your query:
.es(split=from_host.keyword:5, metric=sum:times, q=type:valid).subtract(.es(split=from_host.keyword:5, metric=sum:times, q=type:error))

[anthony-o]

Timelion shows me the following error "Timelion: Error: in cell #1: argument must be a seriesList with a single series" (it talks about the fact that .substract() should not have a split parameter if the first series has already a split I think...).

Do you realy have no error with your query that includes 2 splits? Don't you have 25 lines at the end?

[Bill_McConaghy]

Weird worked for me once, now I see the same error as you are seeing. I will continue to play around with this.

[Bill_McConaghy]

This one does seem to work for me:

.es(split=extension.raw:5, metric=sum:bytes, q=response:200).subtract(.es(metric=sum:bytes, q=response:404))

[anthony-o]

The problem I think that in your case, for each extension, the subtraction is done over 404 responses of every extension and not only of the one coming from the split of the first query.

Actually, with your schema, I would like something like the following:

.es(metric=sum:bytes, q='response:200 AND extension.raw:jpg').subtract(.es(metric=sum:bytes, q='response:404 AND extension.raw:jpg'),
.es(metric=sum:bytes, q='response:200 AND extension.raw:css').subtract(.es(metric=sum:bytes, q='response:404 AND extension.raw:css'),
.es(metric=sum:bytes, q='response:200 AND extension.raw:png').subtract(.es(metric=sum:bytes, q='response:404 AND extension.raw:png'),
...

And I think that your queries does the following:

.es(metric=sum:bytes, q='response:200 AND extension.raw:jpg').subtract(.es(metric=sum:bytes, q=response:404),
.es(metric=sum:bytes, q='response:200 AND extension.raw:css').subtract(.es(metric=sum:bytes, q=response:404),
.es(metric=sum:bytes, q='response:200 AND extension.raw:png').subtract(.es(metric=sum:bytes, q=response:404),
...

Am I wrong?

[Bill_McConaghy]

You're right, wasn't seeing that. I have found out that what you are trying to do is not currently possible in Kibana. See this issue https://github.com/elastic/kibana/issues/13781. However, a PR was recently merged that addresses this and you should be able to do this in the 6.2 version of Kibana, which should be released very soon.