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:
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 !
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
If your problem is resolved, then please click the "Accept as Solution" button to help future readers.
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.
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.
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.
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