All Apps and Add-ons
Highlighted

Use lookup table to dynamically create a search string

Path Finder

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 - metricname and metricfunction.
At the moment it looks like this:

metricname metricfunction
Event Count sum(count)
CPU consumption sum(cpu)
Mem consumption sum(mem)

These fields are used to dynamically populate PullDown menus that show the metricname as label and set the value to metricfunction. 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 metricfunction metricname | 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?

0 Karma
Highlighted

Re: Use lookup table to dynamically create a search string

Path Finder

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 cicstransidlookup.csv cicstransid as tran OUTPUT cicstransareaname | dedup cicstransarea_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.

0 Karma