topic Re: How to find second maximum value in Splunk Search

How to find second maximum value

sivaranjani — Thu, 25 May 2023 04:28:48 GMT

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

Re: How to find second maximum value

yeahnah — Thu, 25 May 2023 04:52:53 GMT

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

Re: How to find second maximum value

yuanliu — Thu, 25 May 2023 07:40:10 GMT

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.

Re: How to find second maximum value

yeahnah — Thu, 25 May 2023 21:04:16 GMT

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

Re: How to find second maximum value

sivaranjani — Fri, 26 May 2023 16:53:56 GMT

Thank you ! that helps.