Automatic lookup and metrics index

[jwillaime]

Hello,

I configured an automatic lookup table to be active on a metrics index via Splunk Web (I do not have access right to the indexers/search-heads directly). I try configuring it via both source (linking the source file from which I monitor metric collection) and sourcetype (which is metrics_csv).

My lookup table is quite simple (measuring only two metrics for now):

metric_id,metricname
1111,CPU Utilization
2222,Memory Utilization

The lookup rule is the following:

metric_lookup_definition metric_id AS metric_name OUTPUTNEW metric_id AS metric_id metricname AS full_name

And the search I am doing is similar to this one:

| mstats prestats=true avg(_value) max(_value) WHERE sourcetype=metrics_csv metric_name="*"  component_ID="a05170000001" span=1s by metric_name
| timechart avg(_value) AS "Avg", max(_value) AS "Max" by full_name

I expected to get the "human readable" names for the metrics ID ("CPU Utilization" and "Memory Utilization"), but I have one "NULL" value instead.

I have no problems when explicitly calling the lookup in the search:

| mstats prestats=true avg(_value) max(_value) WHERE sourcetype=metrics_csv metric_name="*"  component_ID="a05170000001" span=1s by metric_name
|lookup metric_lookup_definition metric_id AS metric_name OUTPUTNEW metric_id AS metric_id metricname AS full_name
| timechart avg(_value) AS "Avg", max(_value) AS "Max" by full_name

Is it possible to use automatic lookup tables with metrics indexes? Did I do something wrong?

Thank you in advance.

[tcole_splunk]

Hi everyone,

Automatic lookups do not work with metrics. The reason behind this is that automatic lookups are applied to individual events, whereas metrics are analyzed as an aggregate. Please see this documentation for more information on searching and monitoring metrics.

[tcole_splunk]

http://docs.splunk.com/Documentation/Splunk/7.0.0/Metrics/Search

[bsonposh · Accepted solution]

I'm not sure how metrics react to lookups and I will research that personally but I think the best way to do what you want is to use a _dimension. In your CSV add a column called full_name and put the human readable name there.

| mstats prestats=true avg(_value) max(_value) 
         WHERE sourcetype=metrics_csv metric_name="*" 
         component_ID="a05170000001" 
         span=1s 
         by full_name
| timechart avg(_value) AS "Avg", 
            max(_value) AS "Max" by full_name

With this process there are less "pieces" to break and you don't have to maintain the lookup and with the way, metrics are charged.. it's free.

Update on lookup:
I think the reason it isn't working is that mstats doesn't return metadata like sourcetype, source or host (the information used for automatic lookup.) If you added sourcetype as part of the "by" statement it may work but again, I think your best option is to add it as a dimension. The performance impact of splitting by sourcetype isn't worth it IMO.