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

[Jouman]

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

[Jouman]

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.

[yuanliu · Accepted solution]

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>>")) ]
| fields - header maxavg

(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"
| multikv forceheader=1
| fields - _time _raw linecount

Q.E.D.

[Jouman]

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.

[yuanliu]

@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.)

[Jouman]

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>>")) ]
| fields - header maxavg

This really helps me a lot.
Thank you