Splunk Search

How to add column values based on subsearch?

Gabriel
Path Finder

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.

Labels (3)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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.

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Path Finder

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 🤓

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...