Splunk Search

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

bowesmana
SplunkTrust
SplunkTrust

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

 

Happy Splunking!

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. 

Happy Splunking!

bowesmana
SplunkTrust
SplunkTrust

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

 

Happy Splunking!

bowesmana
SplunkTrust
SplunkTrust

@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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...