Evaluate a new column based on formula from a lookup file

[its_shubham]

Hello!

Have this requirement:

• Have a business process. For each business process, some KPIs have been identified.
• Have configured a lookup where I am storing KPI related columns alongwith process name.
• My ask is simple, lookup where i have stored kpi related information, filtering out this month's data leaves me four rows: Two rows pertaining to same process but two different KPIs Two rows pertaining two other different processes but one KPI each

P1, KPI1, col1, col2, kpi_value
P2, KPI21, col1, col2, kpi_value
P2, KPI22, col3, col4, kpi_value
P3, KPI31, col1, col2, kpi_value

• Have configured another lookup, storing business process name and kpi name alongwith formula to evaluate that kpi. P1, KPI1, formula1 (col1 + col2) P2, KPI21, formula21 (col1 + col2) P2, KPI22, formula22 (col3 + col4) P3, KPI31, formula31 (col1 + col2)

Now kpi_value for each of the KPI in the first lookup has to be evaluated using the formula from the second lookup.

Have already tried several other answers but to no avail.

| eval kpi_value = [|inputlookup formulae.csv | eval search=formula]

But the above query always picks up the first column from the second lookup and evaluates the value. Not every KPI has those same columns. For example: if the formulae.csv lookup has col1 + col2 in the formula value, it evaluates for all KPIs except for P2, KPI22

Please help.

[woodcock]

I have ready this many times and do not get it. Is your first list of things the content of your first lookup or your data. I am assuming the former. Also, show us some sample event data and what you expect the output to be.

[ololdach]

Hi,

the challenge I see is that you want the subsearch to return exactly one formula for the given KPI and Process based on the parameters in the main search event. The trouble is that the subsearch runs before the main search and hence you simply cannot pass any parameters. You could, however, use a dashboard token in your subsearch, but I don't see how it could help you solve this case. Also, use | return $variable in your subsearch to return the content of your variable: | return $formula

My approach to solve this would be by implementing a custom search command.

Cheers
Oliver

[richgalloway]

The lookup where you have stored kpi related information should have five fields. I'll call them "process", "kpiName", "measurement1", "measurement2", and "kpi_value". These column names cannot change, regardless of where the values in them comes from. Therefore, the SPL for the final result (no need for a formula lookup) would be

... | eval kpi_value = measurement1 + measurement2 | ...

[its_shubham]

but this measurement1 + measurement2 must come from a lookup file.. its a requirement

[ololdach]

Let's get to the facts: You want to treat every single event differently, drawing your formula from a lookup table. The only way to substitute a formula into a query that I know of is by using a subquery, like you did. Fact: This substitution works globally, because it takes place before your main query starts. If you want to substitute the formula for ALL events alike this works like a charm. If, however, you want to use a different formula based on the P and KPI of every single event, you simply can't because you cannot use a different subquery on every single event.

The only way I see to solve this is by using a custom search command:

• Lookup the formula from your lookup
• call a streaming command that gets the formula and all the other fields from the event
• assemble a new query string from the event that splunk passes to you and that looks somewhat like '|makeresults | eval v1="$" ... (assign all the values passed) | eval kpi_value=$formula" '
• call splunk through the REST api to execute that single query
• pass the result back by setting the kpi_value to the result of the REST call

Of course you might also try to solve it without implementing some python by using the splunk rest command and assemble the search string using eval.

[its_shubham]

@ololdach will like to try it