Splunk Search

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

bowesmana
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
| head 1
| 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)
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

Sorry, missed that part.

What about this based on the test data ? 

| 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

 

View solution in original post

renjith_nair
SplunkTrust
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. 

bowesmana
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

bowesmana_0-1604554049727.png

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.

 

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Sorry, missed that part.

What about this based on the test data ? 

| 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

 

View solution in original post

bowesmana
Champion

@renjith_nair 

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

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!