lookup table mappings configuration and sample request

[dhavamanis]

Can you please tell us how to setup the lookup table for the below case,

We have indexed log like below pattern and need to lookup the data based on pattern (API_KEY and BRAND_ID). Instead of key / id values disaplay in the output results, we need to show as api_name and brand_name. Also please provide the step by step details for this configuration.

111.111.11.11 - - [18/Jul/2014:07:23:37 -0700] "GET /member/get?API_KEY=80fc7a5aa22747c380c207f1cceaa5eb&BRAND_ID=e2651af794ea4d0d8d8ad5ffa8bf4c62&id=18ea4281bb6a48609e758055d7434d4f HTTP/1.1" 200 1432 "-" "-"

Sample Output :

brand_name (from lookup table based on brand_id)
api_name (from lookup table based on api_key)
count (over the period)

Note : we have multiple brands and apikey which needs to be mapped with name to show the output results.

[somesoni2 · Accepted solution]

I guess you would need two lookup tables, one for API_KEY/api_name and other for BRAND_ID an dbrand_name mapping.

Steps: lufile_apikey_apiname.csv
1. Get all the mapping for API_KEY and api_name and save them in a csv file, say 'lufile_apikey_apiname.csv' with headers as API_KEY,api_name.
2. Create a lookup table file under appropriate app (Manager->Lookups->lookup table files) http://docs.splunk.com/Documentation/Splunk/6.0.2/Knowledge/Addfieldsfromexternaldatasources
3. Set appropriate sharing permissions.

Perform similar steps for lufile_brandid_brandname.csv with headers BRAND_ID,brand_name.

Once these are setup, you search query will look like this

your base search ..| lookup lufile_apikey_apiname.csv API_KEY OUTPUT api_name | lookup lufile_brandid_brandname.csv BRAND_ID OUTPUT brand_name | stats count by api_name,brand_name

OR

your base search ..| stats count by API_KEY, BRAND_ID| lookup lufile_apikey_apiname.csv API_KEY OUTPUT api_name | lookup lufile_brandid_brandname.csv BRAND_ID OUTPUT brand_name | table api_name,brand_name,count