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
Legend

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

 

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

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. 

---
What goes around comes around. If it helps, hit it with Karma 🙂

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
Legend

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

 

---
What goes around comes around. If it helps, hit it with Karma 🙂

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 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...