Splunk Search

## How to get max and average response time and their related fields in the same table?

Path Finder

Hi all,

I am working on calculating the response time (for max, PR99, and avg value) from Table 1.
I would like to list the detail procedure duration (Procedure-1/-2/-3) and name the ROW1 as max/PR99/AVG, the output would be similar as Table 2.

Do anyone have idea about how to implement this to include max response time and the corresponding Procedure time as well, instead of list maximum value in each field?
Moreover, is there any way to include average response time and average Procedure-1/-2/-3 time into the same table as well?

Table 1:

 (in sec) Procedure-1 Procedure-2 Procedure-3 Total Response Time Test-1 111 222 333 666 Test-2 200 100 300 600 Test-3 250 350 150 750

Table 2:

 (in sec) Total Response Time Procedure-1 Procedure-2 Procedure-3 Max 750 (Test 3) 250 (come from Test 3) 350 (come from Test 3) 150 (come from Test 3) Avg (666+600+750)/3=672 (111+200+250)/3=187 (222+100+350)/3=224 (333+300+150)/3=261

Thank you so much.
#table #chart #stats #max

Labels (3)

• ### table

1 Solution
SplunkTrust

The SPL maxim: If the question is mathematically well defined, the answer is "yes".

Exhibit A:

``````| stats max(*) as max* avg(*) as avg* list(*) as *
| eval avg = "Avg", max = "Max"
| foreach Total* Procedure* ``` actual order depends on real name of Procedure_1, 2, 3, etc ```
[eval avg = avg . ":" . 'avg<<FIELD>>' . "=(" . mvjoin(<<FIELD>>, "+") . ")/3",
max = max . ":" . 'max<<FIELD>>' . " (from Test-" . tostring(mvfind(<<FIELD>>, 'max<<FIELD>>') + 1) . ")"]
| eval maxavg = mvappend(max, avg)
| fields maxavg
| mvexpand maxavg
| eval maxavg = split(maxavg, ":")
| eval header = mvappend("(in sec)", "Total Response Time", "Procedure-1", "Procedure-2", "Procedure-3")
| foreach "(in sec)", "Total Response Time", "Procedure-1", "Procedure-2", "Procedure-3"
[eval <<FIELD>> = mvindex(maxavg, mvfind(header, "<<FIELD>>")) ]

 (in sec) Procedure-1 Procedure-2 Procedure-3 Total Response Time Max 250 (from Test-3) 350 (from Test-3) 333 (from Test-1) 750 (from Test-3) Avg 187=(111+200+250)/3 224=(222+100+350)/3 261=(333+300+150)/3 672=(666+600+750)/3

Test data emulation:

``````| makeresults
| eval _raw = "test_id,Procedure-1,Procedure-2,Procedure-3,Total Response Time
Test-1,111,222,333,666
Test-2,200,100,300,600
Test-3,250,350,150,750"
| fields - _time _raw linecount``````

Q.E.D.

Tags (5)
Path Finder

Hi Yuanliu and all,

Thank you for the help, it helps a lot.

I correct my example data in Table 2 for maximum Test Response Time description.

The purpose for the analysis is to find the the row which leads to maximum Test Response Time field.
So the Procedure-3 in Table 2 shall be 150 (from Test-3) instead of 333 (from Test-1).
Is there any way to fulfill this ?
Only the "Test Response Time" field is used to compare,  after choosing the max "Test Response Time", the Procedure-1/-2/-3 field come from that corresponding row instead of comparing Procedure-1/-2/-3 itself.

Thank you so much.

SplunkTrust

The SPL maxim: If the question is mathematically well defined, the answer is "yes".

Exhibit A:

``````| stats max(*) as max* avg(*) as avg* list(*) as *
| eval avg = "Avg", max = "Max"
| foreach Total* Procedure* ``` actual order depends on real name of Procedure_1, 2, 3, etc ```
[eval avg = avg . ":" . 'avg<<FIELD>>' . "=(" . mvjoin(<<FIELD>>, "+") . ")/3",
max = max . ":" . 'max<<FIELD>>' . " (from Test-" . tostring(mvfind(<<FIELD>>, 'max<<FIELD>>') + 1) . ")"]
| eval maxavg = mvappend(max, avg)
| fields maxavg
| mvexpand maxavg
| eval maxavg = split(maxavg, ":")
| eval header = mvappend("(in sec)", "Total Response Time", "Procedure-1", "Procedure-2", "Procedure-3")
| foreach "(in sec)", "Total Response Time", "Procedure-1", "Procedure-2", "Procedure-3"
[eval <<FIELD>> = mvindex(maxavg, mvfind(header, "<<FIELD>>")) ]

 (in sec) Procedure-1 Procedure-2 Procedure-3 Total Response Time Max 250 (from Test-3) 350 (from Test-3) 333 (from Test-1) 750 (from Test-3) Avg 187=(111+200+250)/3 224=(222+100+350)/3 261=(333+300+150)/3 672=(666+600+750)/3

Test data emulation:

``````| makeresults
| eval _raw = "test_id,Procedure-1,Procedure-2,Procedure-3,Total Response Time
Test-1,111,222,333,666
Test-2,200,100,300,600
Test-3,250,350,150,750"
| fields - _time _raw linecount``````

Q.E.D.

Tags (5)
Path Finder

Hi Sir,

Because the number of Test-id becomes quite large, after I apply the code as a solution,

`| stats max(*) as max* avg(*) as avg* list(*) as *`

The warning will be displayed along with the output:

The following caution(s) occurred while the search ran.
• 'stats' command: limit for values of field 'xxx' reached. Some values may have been truncated or ignored.

Is there any way to avoid or solve this issue ?

Thank you.

SplunkTrust

@JoumanIt is important to pose a separate subject as a new question so other people may search up the solution easier.

There are several pointers about list() running over limit.

1. If you don't have to use list, use values().
1. list(*) as * is particularly expensive if there are many fields. Eliminate as many fields before the stats as possible using fields.
2. If order is important but not all values are needed, use some method that doesn't use list(). This is a much deeper consideration that cannot be explained in a couple sentences.
3. Lastly, you can make changes in limits.conf to allow more results. (But this requires more memory to be used.)
Path Finder

Hi Yuanliu and all,

Thank for Yuanliu's help.
I modify from his code ; therefore I am able to solve the issue, "Only the "Test Response Time" field is used to compare,  after choosing the max "Test Response Time", the Procedure-1/-2/-3 field come from that corresponding row instead of comparing Procedure-1/-2/-3 itself." , whichi I listed earlier by the code in green highlight.

| stats max(*) as max* avg(*) as avg* list(*) as *
| eval avg = "Avg", max = "Max" ``` avg, max initial value ```
| foreach Total*
[eval max=max.":".'max<<FIELD>>'."(from Test-".tostring(mvfind(<<FIELD>>,'max<<FIELD>>')+1).")", max_index=tostring(mvfind(<<FIELD>>,'max<<FIELD>>'))]
| foreach Procedure*
[eval max=if(isnotnull(mvindex(<<FIELD>>,max_index)), max.":".mvindex(<<FIELD>>,max_index),max)]
| foreach Total* Procedure*``` actual order depends on real name of Procedure_1, 2, 3, etc ```
[eval avg = avg . ":" . 'avg<<FIELD>>' . "=(" . mvjoin(<<FIELD>>, "+") . ")/3"]

| eval maxavg = mvappend(max, avg)
| fields + maxavg
| mvexpand maxavg
| eval maxavg = split(maxavg, ":")
| eval header = mvappend("(in sec)", "Total Response Time", "Procedure-1", "Procedure-2", "Procedure-3")
| foreach "(in sec)", "Total Response Time", "Procedure-1", "Procedure-2", "Procedure-3"
[eval <<FIELD>> = mvindex(maxavg, mvfind(header, "<<FIELD>>")) ]

This really helps me a lot.
Thank you

Get Updates on the Splunk Community!

#### Splunk Indexers — ext4 vs XFS filesystem performance

Summary While I did not initially set out to benchmark filesystem performance on our Linux-based Splunk ...

#### Digital Resilience Made Easier With GenAI

TECH TALKS Digital Resilience Made Easier With GenAI Thursday, September 5, 2024  |  11AM PST / 2PM ...

#### Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...