Use lookup table to dynamically create a search string

[anjafischer]

Hello,

I have been trying to work this out for a while now. Unfortunatly without any success:

I have a lookup table that consists of two columns - metric_name and metric_function.
At the moment it looks like this:

metric_name metric_function
Event Count sum(count)
CPU consumption sum(cpu)
Mem consumption sum(mem)

These fields are used to dynamically populate PullDown menus that show the metric_name as label and set the value to metric_function. These Pulldowns are then used to generate certain charts. Moreover, I want to create a table that lists program names and ALL of the above metrics. The problem is, I cannot hardcode this table (aka stats command) because the above lookup table can be extended by more metrics.

My question is: how do I generate a search query dynamicall that gives me the following result
| stats sum(count) sum(cpu) sum(mem) ....

Has anyone any experience with this?
I have already tried the format and fieldformat commands, but without success...

A hint in the right direction would really be appreciated 🙂

UPDATE:
I have figured out a way to create the search string after the stats command like this:

| inputlookup myLookupFile.csv | fields metric_function metric_name | format "" "" "as" "" "" "" | fieldformat search=replace(search,"metric_[\w]*=","") | fields search

If I type this into the splunk search app: I get a table with a singel column named "search" that contains a single entry:

"sum(count)" as "Execution Count" "sum(cpu)" as "CPU Consumption" "sum(mem)" as "Memory ConsumptionTime"

However, when i now try to call | stats [$searchFromAbove$] it gives me an error warning:
Error in 'stats' command: The argument 'metric_function=sum(count)' is invalid.

How do I get the subsearch to relly only deliver that string with removed field names?

[anjafischer]

Never mind, i figured it out 😄

in case anyone else ever runs into this sort of problem: this is the query to generate both number arrays for a HiddenchartFormatter:

system=cics | dedup tran | lookup cics_trans_id_lookup.csv cics_trans_id as tran OUTPUT cics_trans_area_name | dedup cics_trans_area_name | stats count as n | eval numbers=mvrange(0,n+2,1) | eval leftColumns=mvjoin(numbers, ",") | eval rightColumns="0,".tostring(n+2) | table leftColumns rightColumns

the bold printed part is the actual sequence generation, the italic part of the query is the base search, so to speak, where I'm only interested in n, the number of my applications, i.e. the number of categories that I have to stack.