How do I subtract two search?

[politrons]

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

[ITWhisperer]

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?

[politrons]

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

regards 

[ITWhisperer]

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

[FrankVl]

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

[politrons]

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

[ITWhisperer]

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.

[politrons]

Yep silly me the query were wrong, thanks  lot!