Splunk Search

lookup table mappings configuration and sample request

dhavamanis
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 (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.

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...