topic Re: Substract two search in Knowledge Management

How do I subtract two search?

politrons — Mon, 31 Oct 2022 09:11:41 GMT

I´m making a union of two search, and now I´m trying to make a subtract of the two variables.

| set union [search "Output MeshRequestId" | stats avg(time) as avgTimeOut ] [search "Input MeshRequestId" | stats avg(time) as avgTimeInt ] |stats count(eval(diff=avgTimeOut-avgTimeInt)) as TimeInThrottler

Everything fine before the latest count. I can see the definition of two avg variables.

But when I do the stats count(eval(diff=avgTimeOut-avgTimeInt)) as TimeInThrottler

Always return 0. Any idea what is wrong

Re: Substract two search

ITWhisperer — Sun, 30 Oct 2022 11:16:27 GMT

union is producing 2 events, one with avgTimeOut and one with avgTimeInt - the calculation is working on one event at a time from the pipeline, so for each event, one of the fields is null.

Have you considered using appendcols in this scenario?

Re: Substract two search

politrons — Sun, 30 Oct 2022 14:35:28 GMT

Thanks for the answer, any example that I can refer of how it should be done using appendcols for this use case?

regards

Re: Substract two search

ITWhisperer — Mon, 31 Oct 2022 09:12:54 GMT

Try something like this

search "Output MeshRequestId" | stats avg(time) as avgTimeOut | appendcols [search "Input MeshRequestId" | stats avg(time) as avgTimeInt ] | stats count(eval(diff=avgTimeOut-avgTimeInt)) as TimeInThrottler

Re: Substract two search

FrankVl — Mon, 31 Oct 2022 12:36:52 GMT

At that point you could also simply replace that last stats by an eval. (Unless I'm somehow completely missing what you're trying to achieve with that stats)

search "Output MeshRequestId" | stats avg(time) as avgTimeOut | appendcols [search "Input MeshRequestId" | stats avg(time) as avgTimeInt ] | eval TimeInThrottler = avgTimeOut - avgTimeInt

Re: Substract two search

politrons — Wed, 02 Nov 2022 08:42:14 GMT

Thanks for all the answers, after apply both option of union and append. I got same result

Only avgTimeInt is present, and I cannot show the total Difference.

Also I would like to use the result in stats or timechar if is possible

Re: Substract two search

ITWhisperer — Wed, 02 Nov 2022 08:57:04 GMT

It looks like

"Output MeshRequestId" | stats avg(time) as avgTimeOut

is not producing any results - please check the events from the search to make sure they have the time field with that exact name.

Re: Substract two search

politrons — Wed, 02 Nov 2022 09:43:25 GMT

Yep silly me the query were wrong, thanks lot!