How to add column values based on subsearch?

[Gabriel]

Hi everyone

I got the following sample search that yields the table below.

index=server
| stats avg(response_time) by server_name
| sort + avg(response_time)
| streamstats count as rank
| head 3

rank	server_name	avg(response_time)	new_performance_metric
1	best.server	300
2	second.best.server	350
3	third.best.server	400

Once I know the top servers, I want to calculate additional new_performance_metric for each of the three servers. Does anyone know how this can be done?

Note:
- I can't use foreach since the metric I want to calculate involves streaming commands. Foreach does not support that.
- I think I can't use a subsearch since it is executed first where the top servers are not known yet.
- I can't precompute the new_performance_metric for all servers and then use something like a lookup since this is computationally too expensive.

My guess is that the solution involves a macro but I couldn't figure it out yet. Many thanks in advance.

[PickleRick · Accepted solution]

Your guess is wrong. Macro is expanded before spawning the search. So you can either use subsearch (but the other way around - run this search as a subsearch to generate set of conditions) or use the map command.

[Gabriel]

I just tried the mapping command. It did precisely what I needed. Thanks a lot, you saved me heaps of time! 💪

[PickleRick]

Just remember that map spawns subsearches which have its own limits (in terms of returned results and execution time). It is also considered a "risky command" and as such might be restricted.

[Gabriel]

I testet it yesterday and it turns out our production environment suppresses dashboards and reports that make use of the mapping command. I had to use subsearches after all. Thanks again for pointing me in the right direction, I am sure the mapping command will come in handy, at some point, even if I can't use it this time 🤓