Splunk Search
Highlighted

lookup table mappings configuration and sample request

Builder

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 (APIKEY and BRANDID). Instead of key / id values disaplay in the output results, we need to show as apiname and brandname. 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?APIKEY=80fc7a5aa22747c380c207f1cceaa5eb&BRANDID=e2651af794ea4d0d8d8ad5ffa8bf4c62&id=18ea4281bb6a48609e758055d7434d4f HTTP/1.1" 200 1432 "-" "-"

Sample Output :

brandname (from lookup table based on brandid)
apiname (from lookup table based on apikey)
count (over the period)

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

Tags (1)
0 Karma
Highlighted

Re: lookup table mappings configuration and sample request

SplunkTrust
SplunkTrust

I guess you would need two lookup tables, one for APIKEY/apiname and other for BRANDID an dbrandname mapping.

Steps: lufileapikeyapiname.csv
1. Get all the mapping for APIKEY and apiname and save them in a csv file, say 'lufileapikeyapiname.csv' with headers as APIKEY,apiname.
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 lufilebrandidbrandname.csv with headers BRANDID,brandname.

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

View solution in original post