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 (1)
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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...