Splunk Search

Find maximum value in one column where a second column meets criteria

Champion

I believe as with all things Splunk, there is more than one way to solve this

My data consists of this

``````| makeresults
| eval ml=mvrange(1,4)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved``````

What I want is to find the highest value of ml where achieved=1 and where there has not been achieved=0 in a lower value of ml.

I have worked out that this works, but I would like to see if there is a different solution as this seems a bit overly complicated

``````| makeresults
| eval ml=mvrange(1,4)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eval x=if(achieved=0,0,null)
| filldown x
| eventstats max(eval(if(isnull(x),ml,null))) as y
| eval max_ml=if(isnull(y),0,y)``````

where max_ml will give the desired outcome.

Anyone else see an alternative solution. Note mvrange size can be any size, so not just 3 values.

Labels (2)

• stats

1 Solution
SplunkTrust

Sorry, missed that part.

``````| makeresults
| eval ml=mvrange(1,10)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eventstats min(eval(if(achieved==0,ml-1,null()))) as max_ml``````

SplunkTrust

Does this work?

``````| makeresults
| eval ml=mvrange(1,10)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eventstats max(eval(if(achieved==1,ml,null()))) as max_achieved_ml ,min(eval(if(achieved==0,ml,null()))) as min_unachieved_ml``````

eventstats can be replaced with stats if you dont need other fields.

Champion

No that's not quite it. This image shows how the first 0 in the achieved column effectively locks the max_achieved to the level of the previous row, where the last '1' was

Like this

Essentially what I am trying to do is find the highest contiguously attained level (ml). So in the above image, even though there were 1s up to ml=8, the presence of the 0 at ml=4 means that all ml>3 are meaningless.

SplunkTrust

Sorry, missed that part.

``````| makeresults
| eval ml=mvrange(1,10)
| mvexpand ml
| eval achieved=random() % 2
| table ml achieved
| eventstats min(eval(if(achieved==0,ml-1,null()))) as max_ml``````

Champion

Almost got it - yes, clearer and simpler than my original. However, your eventstats on its own is not quite sufficient to handle the all 1 case, so this is needed

``````| eventstats min(eval(if(achieved==0,ml-1,null()))) as max_ml max(ml) as highest_ml
| eval max_ml=if(isnull(max_ml),highest_ml,max_ml)``````

to handle the null max_ml when all achieved values are 1.

Thanks for your help - I was stuck in too many dimensions to see the simplicity

Take the 2021 Splunk Career Survey

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey.Earn \$50 in Amazon cash! Full Details! >

Get Updates on the Splunk Community!