How to find second maximum value

[sivaranjani]

I have a search like this to fetch the maximum value. Now the case i wanted to add is, if the maximum value field is 4-step4 then i want my table to display second maximum value as the max step.

Desired result 

Id	Step that is taking maximum time	maxtime
12345678	3-step3	33

| makeresults | eval _raw="{
	\"Detail\": {
		\"Id\": 12345678,
		\"RequestCompleteTS\": \"2023-04-27T15:59:30.6960113-04:00\",
		\"Steps\": {
			\"0-step1\": 32,
			\"0-step2\": 15,
			\"3-step3\": 33,
			\"4-step4\": 49,
			\"5-step5\": 15,
			\"6-step6\": 9,
			\"7-step7\": 8
		},
		\"StepsCnt\": 18,
		\"TargetRegion\": \"BRD\"
	},
	\"LogType\": \"Info\",
	\"Message\": \"Success\",
	\"Time\": \"2023-04-27 15:59:30.696--04:00\"" 
  ``` ^^^ create dummy event ^^^ ```
| spath
| rename Detail.Id AS Id Detail.Steps.* AS *
| foreach *step* [ eval Steps=if(isnull(Steps), '<<FIELD>>', mvappend(Steps, '<<FIELD>>')) ]
| eval steps_list_ordered=mvmap( mvsort(mvmap(mvdedup(Steps), len(Steps) . "-" . Steps)), substr(Steps, 3) )
      ,maxtime=mvindex(steps_list_ordered, mvcount(steps_list_ordered)-1)
| foreach *-step* [ eval "maxstep"=if('<<FIELD>>'=maxtime, "<<FIELD>>", if(isnotnull('maxstep'), 'maxstep', null())) ]
| rename maxstep AS "Step that is taking maximum time"
| table Id "Step that is taking maximum time" maxtime
​

[sivaranjani]

Thank you ! that helps.

[yuanliu]

When I see a problem like this, my first instinct is to cheat.  This is simpler, and somewhat more semantic.

| makeresults | eval _raw="{
	\"Detail\": {
		\"Id\": 12345678,
		\"RequestCompleteTS\": \"2023-04-27T15:59:30.6960113-04:00\",
		\"Steps\": {
			\"0-step1\": 32,
			\"0-step2\": 15,
			\"3-step3\": 33,
			\"4-step4\": 49,
			\"5-step5\": 15,
			\"6-step6\": 9,
			\"7-step7\": 8
		},
		\"StepsCnt\": 18,
		\"TargetRegion\": \"BRD\"
	},
	\"LogType\": \"Info\",
	\"Message\": \"Success\",
	\"Time\": \"2023-04-27 15:59:30.696--04:00\"" 
  ``` ^^^ create dummy event ^^^ ```
| spath
| rename Detail.Id AS Id Detail.Steps.* AS *
| foreach *-step*
    [eval time_step = mvappend(time_step, printf("%04d", '<<FIELD>>') . ":<<FIELD>>")]
| eval time_step = mvsort(time_step)
| eval eval max = mvindex(time_step, mvcount(time_step) - 1), second_to_max = mvindex(time_step, mvcount(time_step) - 2)
| eval max = split(max, ":"), second_to_max = split(second_to_max, ":")
| eval "Step that is taking maximum time" = mvindex(second_to_max, 1), maxtime = tonumber(mvindex(second_to_max, 0))
| eval "Step that is taking next maximum time" = mvindex(second_to_max, 1), nextmaxtime = tonumber(mvindex(second_to_max, 0))
| table Id "Step that is taking maximum time" maxtime "Step that is taking next maximum time" nextmaxtime

One key step is to pad 0 to the left of time with printf.  The above uses %04d.   You can add as many as your data requires.

[yeahnah]

Yeah, I like this, @yuanliu 

I did pick up a few typos in your SPL that caused the max result to be the second max.  I've fixed it up and here it is for completeness...

...
| foreach *-step*
    [eval time_step = mvappend(time_step, printf("%04d", '<<FIELD>>') . ":<<FIELD>>")]
| eval time_step = mvsort(time_step)
      ,max = mvindex(time_step, mvcount(time_step) - 1)
      ,second_to_max = mvindex(time_step, mvcount(time_step) - 2)
      ,max = split(max, ":")
      ,second_to_max = split(second_to_max, ":")
      ,"Step that is taking maximum time" = mvindex(max, 1)
      ,maxtime = tonumber(mvindex(max, 0))
      ,"Step that is taking next maximum time" = mvindex(second_to_max, 1)
      ,nextmaxtime = tonumber(mvindex(second_to_max, 0))
| table Id "Step that is taking maximum time" maxtime "Step that is taking next maximum time" nextmaxtime

[yeahnah · Accepted solution]

Hi @sivaranjani 

Just carry the existing logic through but for the next value.

Like this...

| makeresults | eval _raw="{
	\"Detail\": {
		\"Id\": 12345678,
		\"RequestCompleteTS\": \"2023-04-27T15:59:30.6960113-04:00\",
		\"Steps\": {
			\"0-step1\": 32,
			\"0-step2\": 15,
			\"3-step3\": 33,
			\"4-step4\": 49,
			\"5-step5\": 15,
			\"6-step6\": 9,
			\"7-step7\": 8
		},
		\"StepsCnt\": 18,
		\"TargetRegion\": \"BRD\"
	},
	\"LogType\": \"Info\",
	\"Message\": \"Success\",
	\"Time\": \"2023-04-27 15:59:30.696--04:00\"" 
  ``` ^^^ create dummy event ^^^ ```
| spath
| rename Detail.Id AS Id Detail.Steps.* AS *
| foreach *step* [ eval Steps=if(isnull(Steps), '<<FIELD>>', mvappend(Steps, '<<FIELD>>')) ]
| eval steps_list_ordered=mvmap( mvsort(mvmap(mvdedup(Steps), len(Steps) . "-" . Steps)), substr(Steps, 3) )
      ,maxtime=mvindex(steps_list_ordered, mvcount(steps_list_ordered)-1)
      ,nextmaxtime=mvindex(steps_list_ordered, mvcount(steps_list_ordered)-2)
| foreach *-step* [ eval "maxstep"=if('<<FIELD>>'=maxtime, "<<FIELD>>", if(isnotnull('maxstep'), 'maxstep', null()))
                        ,"nextmaxstep"=if('<<FIELD>>'=nextmaxtime, "<<FIELD>>", if(isnotnull('nextmaxstep'), 'nextmaxstep', null()))
                  ]
| rename maxstep AS "Step that is taking maximum time" nextmaxstep AS "Step that is taking next maximum time"
| table Id "Step that is taking maximum time" maxtime "Step that is taking next maximum time" nextmaxtime

 You may want to table or rename the output to match what you want exactly.

Hope that helps