Splunk Search

Use table value in later stats command such as avg, max

bing_zheng
New Member

I have a search which outputs a table such as
apiNAME (-- this is the column head with values as below)
apiA_MS
apiB_MS
apiC_MS

I want to use it passing all the values to search such as:
| stats avg(apiNAME), max(apiNAME), min(apiNAME)

I hope to see output as:
apiNAME avg max min
apiA_MS 234.5 345.6 123.4
apiB_MS 222.3 333.3 111.1
apiC_MS 444.4 555.5 222.2

I tried to use something like
| foreach apiNAME [eval avg=avg(<>)]

Unfortunately it didn't return the number such as 234.5
but return back as avg(apiA_MS), just a string, not the average value I expected.

Please help.

eric

0 Karma
1 Solution

somesoni2
Revered Legend

Try like this
Slight update

index=a sourcetype=somelog  | table api*MS | untable temp apiName response_time
 | stats avg(response_time), max(response_time), min(response_time) by apiName

View solution in original post

0 Karma

somesoni2
Revered Legend

Try like this
Slight update

index=a sourcetype=somelog  | table api*MS | untable temp apiName response_time
 | stats avg(response_time), max(response_time), min(response_time) by apiName
0 Karma

bing_zheng
New Member

It works. Thanks for the solution, though it is kind of mystery to me.

0 Karma

somesoni2
Revered Legend

Best way to learn is to run the search part one by one and check the output. I added field temp so that I can use it in untable command., but it seems it works without adding it.

https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Untable

0 Karma

bing_zheng
New Member

Here is the original search:

index=a sourcetype=somelog | table api*MS | fieldsummary | fields field | stats values(field) as apiNAME | foreach apiNAME [eval max=max(<>)]

BTW, apiA_MS is the response time so it is in the event as apiA_MS=223.4
I hope to find a way to get max(apiA_MS), max(apiB_MS), etc. from the list of the apiNAME.

0 Karma

rjthibod
Champion

I think there is something missing in your description. What field contains the metrics that you want to take the avg, max, and min of. That field cannot be both the text string you have under apiName. There has to be something else in your data that contains numbers that you omitted from your description.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...