Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to convert my lookup field value to an executa...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

johnchamp

Explorer

02-23-2016
02:41 PM

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

1 Solution

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

MuS

SplunkTrust

02-23-2016
02:55 PM

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

johnchamp

Explorer

02-23-2016
03:07 PM

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,

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

martin_mueller

SplunkTrust

02-23-2016
03:05 PM

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

sideview

SplunkTrust

02-23-2016
03:09 PM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

martin_mueller

SplunkTrust

02-23-2016
03:11 PM

Ah yes, `search`

- I tested with `query`

, but that produces a quoted string 😞

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

johnchamp

Explorer

02-23-2016
03:57 PM

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?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

sideview

SplunkTrust

02-24-2016
08:36 AM

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

woodcock

Esteemed Legend

02-25-2016
07:02 PM

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.