I have a lookup file in which one of the field values is a formula.
test.csv (lookup file)
This is my search:
index=main sourcetype="generatedLogs" earliest=-2m|fields name, counter|join name[|inputlookup test.csv|]
Since Splunk considers the field value as text, I cannot execute it as a formula.
Is there any possible way to achieve this?
Thanks in advance.
you could have the formula split up in the lookup like:
and use it in the search:
index=main sourcetype="generatedLogs" earliest=-2m | fields name, counter | inputlookup append=t test.csv | eval result=value1*value2/value3
Hope this helps ...
Thanks for the answer.
this solution works the formula with the same signature, but i have a different set of formulas by name in lookup table. and i want to make a generalized solution for this requirement.
If all else fails, you can write a custom search command to evaluate the formula embedded in a string field.
It is possible, although it's not very obvious how to do it.
You have to use a subsearch for it.
Here is an example. My "base search" here in my example is just
| stats count | fields - count | eval x=12 Which gives a single row with a single field "x" set to "12".
and then what comes after that is the peculiar use of a subsearch to take what is really just a string "(x/2)*(x/2)" and evaluate it as the results of an eval command.
| stats count | fields - count | eval x=12 | eval y=[| stats count | fields - count | eval search="(x/2)*(x/2)" ]
You'll see the output where x is of course still "12" but there is also a y field whose value is "36", as computed by the formula.
The key thing is you need to have only one field in the subsearch, and that field MUST be called "search". This acts as a special case in the subsearch code I believe. Note that if you try "query" instead of "search", note that this evaluation trick does not work.
Here is some content from the official docs about other differences between the special-cased "query" and "search" field behavior in subsearches - http://docs.splunk.com/Documentation/Splunk/6.1.8/Search/Changetheformatofsubsearchresults
Why exactly "search" works here and "query" does not, frankly is a bit of a mystery. I suppose you should just remember that they are different from each other and a bit secretive from the rest of us.
thanks for the solution. This is exactly what i was looking for.
but one question though, as we cannot pass a field to the subsearch.
| stats count | fields - count |eval formula="(x/2)*(x/2)" |eval x=12 | eval y=[| stats count | fields - count |eval f=formula|eval search=f ]
how to make the query dynamic?
Yes.... a big shortcoming. Since in your case you have to get the formula from the outer search, and since there's no way to pass it into the inner search, you may be out of luck. the other "subsearch-ish" looking things in Splunk like join/append/map, don't allow this trick (where they can evaluate a string as a SPL expression). It's quite possible that if you keep looking you'll find one that does though....
A custom search command written in python might be better, although of course evaluating strings of untrusted data so as to evaluate arbitrary code is up there on the list of worst security ideas ever, so keep that in mind.
Also beware in your last comment - you have more than one field coming out of the subsearch and you'll end up with malformed syntax. You need only the "search" field to come back from the subsearch and here you have a "f" field as well.
You can pass a field to an inner search by using
map. Take this for example:
| inputcsv myFileWithFormulasInFiledCalledFormula | map search="| inputcsv myFileWithFieldsXandY | eval result = $Formula$"
Inside this search you could do the subsearch thing, too.