Splunk Search

How to find second maximum value

sivaranjani
Explorer

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
​

 

Labels (1)
0 Karma
1 Solution

yeahnah
Motivator

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

View solution in original post

0 Karma

sivaranjani
Explorer

Thank you ! that helps.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
Motivator

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

 

0 Karma

yeahnah
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...