topic Re: How to convert my lookup field value to an executable formula? in Splunk Search

How to convert my lookup field value to an executable formula?

johnchamp — Tue, 23 Feb 2016 22:41:21 GMT

Hi,

I have a lookup file in which one of the field values is a formula.

test.csv (lookup file)
name,value
first,counter*100/2

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.
John

Re: How to convert my lookup field value to an executable formula?

MuS — Tue, 23 Feb 2016 22:55:34 GMT

Hi johnchamp,

you could have the formula split up in the lookup like:

name,value1,value2,value3
first,counter,100,2

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 ...

cheers, MuS

Re: How to convert my lookup field value to an executable formula?

martin_mueller — Tue, 23 Feb 2016 23:05:32 GMT

If all else fails, you can write a custom search command to evaluate the formula embedded in a string field.

Re: How to convert my lookup field value to an executable formula?

johnchamp — Tue, 23 Feb 2016 23:07:13 GMT

Hi MuS,

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.

Thanks,

Re: How to convert my lookup field value to an executable formula?

sideview — Tue, 23 Feb 2016 23:09:21 GMT

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.

UPDATE:

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.

Re: How to convert my lookup field value to an executable formula?

martin_mueller — Tue, 23 Feb 2016 23:11:50 GMT

Ah yes, search - I tested with query, but that produces a quoted string 😞

Re: How to convert my lookup field value to an executable formula?

johnchamp — Tue, 23 Feb 2016 23:57:36 GMT

Hi sideview,

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?

Re: How to convert my lookup field value to an executable formula?

sideview — Wed, 24 Feb 2016 16:36:25 GMT

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.

Re: How to convert my lookup field value to an executable formula?

woodcock — Fri, 26 Feb 2016 03:02:21 GMT

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.