How to find events where the event time duration is greater than the overall dynamic average time?

[wandi]

I have a field named "time" where I have the time that an event took and a field named "tag" with the name of the event. I want to search all the tags that time are greater than overall time average. Something like:


source=avpiv2 | where time > [search source=apiv2 | stats avg(time) as averageTime | return averageTime]


but I'm receiving the error:
Error in 'where' command: The operator at '="20.436350"' is invalid.
(note that 20.43... is the dynamic number that I want to use in the comparison)

[gauldridge · Accepted solution]

Try this:

source=avpiv2 | where time > [search source=apiv2 | stats avg(time) as averageTime | fields averageTime | rename averageTime AS search]

When you rename a field to search in a subsearch, you get just the value of the field returned to your main search pipeline vice returning a field/value pair. So, your search is returning averageTime=20.436350 instead of just 20.436350 like you expect.

[unnigec]

since we are pulling out only averageTime here ...|fields averageTime does not do any extra job I suppose.

[somesoni2]

Alternatively, your can use "| return $averageTime", if just running a search on Search App [ user "|return $$averageTime" in case of dashboards searches] instead of just " |return averageTime" in the subsearch.