Splunk Search

Subquery in an else statement

frozenpy
Explorer

Hello,

I trying to perform a subquery on an else statement, I believe that the way I'm trying to do it is not right. I searched a bit over the internet but I couldn't find a way of doing it.

My problem is as follows, I have two inputlookups, I want to:

  1. Read from first lookup extract a value name associated to a maximum in another column (done)
  2. Join over that column (model_name) with the second inputlookup and check whether evaluation metric is superiror to a given treshold keep the model_name. (done)
  3. Else query the second inputlookup to find model_name associated with maximum value. (my problem is here...how to write a subquery in an else statement

Here's a not working code in the else statement:

 

 

 

|inputlookup model_evaluation.csv 
| eventstats max(evaluation_metric) as maxf 
| eval maxf=tonumber(maxf) 
| eval evaluation_metric=tonumber(evaluation_metric) 
| where  evaluation_metric>=maxf  
| dedup maxf
| rename evaluation_metric as training_score
| table model_name training_score

| join type=inner model_name

[|inputlookup model_evaluation_month.csv
| eval good_model_name = case (evaluation_metric > 0.95, model_name, 1=1, [search | eventstats max(evaluation_metric) as maxf | eval maxf=tonumber(maxf) | eval evaluation_metric=tonumber(evaluation_metric) | where  evaluation_metric>=maxf |dedup maxf | return model_name])
| table *, good_model_name
]

 

 

 

Thank you in advance !

Labels (2)
Tags (1)
0 Karma
1 Solution

frozenpy
Explorer

I finally made it to work, first it was the evals made before the join occurs and then it was multiple if statement that needed to be checked. Bellow the code if that might someday help anyone and thank you for your replies  

 

|inputlookup model_evaluation.csv | rename evaluation_metric as training_score | table model_name training_score

	| join type=inner model_name

	[|inputlookup model_evaluation_month.csv]
	| eventstats max(training_score) as max_training
	| eventstats max(evaluation_metric) as max_evaluation
	| eval model_max_evaluation =  if(tonumber(evaluation_metric) == tonumber(max_evaluation), model_name, "void")
	| eval good_model_name = if(training_score == max_training and evaluation_metric >= 0.94, model_name, model_max_evaluation)
	| dedup good_model_name
	| where like(training_score,  "%".max_training."%") OR NOT like(good_model_name, "void")
	| eventstats count(model_name) as count_lines
	| eval model_to_keep = if(count_lines > 1, if(training_score == max_training and evaluation_metric >= 0.94, "True", "False"), "None")
	| search model_to_keep IN ("True", "None")
	| table model_name
 

 

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

By default, the return command returns a result in the form of "field=value", which doesn't make sense in a case statement.  Try return $model_name, which should return just the value.

---
If this reply helps you, Karma would be appreciated.
0 Karma

frozenpy
Explorer

Thanks for your quick reply and thanks for the return tip...I'm a SPLUNK beginner. Well it seems though to not be working.

It's because I lose the row that I want to get in the else statement since it has a different model_name and it is lost with the join

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Run the subsearch by itself to confirm it returns the expected results.  Next, run the enclosing search using the output from the subsearch, for example:

|inputlookup model_evaluation_month.csv
| eval good_model_name = case (evaluation_metric > 0.95, model_name, 1=1, "foo")
| table *, good_model_name

and confirm that returns the expected results.  Once that works, then it's time to attempt the join. 

Consider using append instead of join since the latter can be inefficient.

---
If this reply helps you, Karma would be appreciated.
0 Karma

frozenpy
Explorer

I finally made it to work, first it was the evals made before the join occurs and then it was multiple if statement that needed to be checked. Bellow the code if that might someday help anyone and thank you for your replies  

 

|inputlookup model_evaluation.csv | rename evaluation_metric as training_score | table model_name training_score

	| join type=inner model_name

	[|inputlookup model_evaluation_month.csv]
	| eventstats max(training_score) as max_training
	| eventstats max(evaluation_metric) as max_evaluation
	| eval model_max_evaluation =  if(tonumber(evaluation_metric) == tonumber(max_evaluation), model_name, "void")
	| eval good_model_name = if(training_score == max_training and evaluation_metric >= 0.94, model_name, model_max_evaluation)
	| dedup good_model_name
	| where like(training_score,  "%".max_training."%") OR NOT like(good_model_name, "void")
	| eventstats count(model_name) as count_lines
	| eval model_to_keep = if(count_lines > 1, if(training_score == max_training and evaluation_metric >= 0.94, "True", "False"), "None")
	| search model_to_keep IN ("True", "None")
	| table model_name
 

 

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

It’s go time — Boston, here we come!

Are you ready to take your Splunk skills to the next level? Get set, because Splunk University is back, and ...

Performance Tuning the Platform, SPL2 Templates, and More New Articles on Splunk ...

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