topic anqRe: Subquery in an else statement in Splunk Search

Subquery in an else statement

frozenpy — Tue, 17 Nov 2020 13:39:54 GMT

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:

Read from first lookup extract a value name associated to a maximum in another column (done)
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)
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:

Thank you in advance !

Re: Subquery in an else statement

richgalloway — Tue, 17 Nov 2020 14:44:53 GMT

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.

anqRe: Subquery in an else statement

frozenpy — Tue, 17 Nov 2020 15:17:51 GMT

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.

Re: anqRe: Subquery in an else statement

richgalloway — Tue, 17 Nov 2020 16:27:53 GMT

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.

Re: anqRe: Subquery in an else statement

frozenpy — Wed, 18 Nov 2020 11:01:02 GMT

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 richgalloway

|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

Re: Subquery in an else statement

richgalloway — Wed, 18 Nov 2020 13:23:00 GMT

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