Splunk Search

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

Jouman
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-1Procedure-2Procedure-3Total Response Time
Test-1111222333666
Test-2200100300600
Test-3250350150750


Table 2:

(in sec)Total Response TimeProcedure-1Procedure-2Procedure-3
Max750 (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)
0 Karma
1 Solution

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

 

(in sec)Procedure-1Procedure-2Procedure-3Total Response Time
Max250 (from Test-3)350 (from Test-3)333 (from Test-1)750 (from Test-3)
Avg187=(111+200+250)/3224=(222+100+350)/3261=(333+300+150)/3672=(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.

View solution in original post

Jouman
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.

0 Karma

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

 

(in sec)Procedure-1Procedure-2Procedure-3Total Response Time
Max250 (from Test-3)350 (from Test-3)333 (from Test-1)750 (from Test-3)
Avg187=(111+200+250)/3224=(222+100+350)/3261=(333+300+150)/3672=(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
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.

0 Karma

yuanliu
SplunkTrust
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.)
0 Karma

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

 

This really helps me a lot.
Thank you

0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...