Splunk Search

How to lookup field from csv file using automatic lookups?

dhavamanis
Builder

We have indexed csv file and it has field brand_id, can you please provide steps how to lookup this brand_id field equivalent to brand_name using automatic lookup in the output.

Input data (csv) :

brand_id,field1,field2,field3
1,11,111,1111
2,22,222,2222
3,33,333,3333
1,22,222,2222
2,11,111,1111
1,11,111,1111

Required output (brandwise stats count):

brand_id,brand_name,count
1,xyz,3
2,abc,2
3,pqr,1

Tags (2)
1 Solution

strive
Influencer

Follow below steps

Step 1: Add CSV file containing columns Brand_ID, Brand_Name under lookups folder

Step 2: Add a Stanza in your transforms.conf file like this
[csv_brand_info]
filename = brand_info.csv
max_matches = 1
case_sensitive_match = false

Step 3: Write search to include lookup like this

your search.. | stats count as Count by brand_id | lookup  csv_brand_info Brand_ID as brand_id OUTPUT Brand_ID Brand_Name | table Brand_ID Brand_Name Count

View solution in original post

strive
Influencer

Follow below steps

Step 1: Add CSV file containing columns Brand_ID, Brand_Name under lookups folder

Step 2: Add a Stanza in your transforms.conf file like this
[csv_brand_info]
filename = brand_info.csv
max_matches = 1
case_sensitive_match = false

Step 3: Write search to include lookup like this

your search.. | stats count as Count by brand_id | lookup  csv_brand_info Brand_ID as brand_id OUTPUT Brand_ID Brand_Name | table Brand_ID Brand_Name Count

somesoni2
Revered Legend

Provided you have already configured a lookup table file with fields "brand_id" and "brand_name", with name say brandlookup.csv, then try this

index=yourindex source=yourcsvfile  | stats count by brand_id | lookup brandlookup.csv brand_id OUTPUT brand_name | table brand_id, brand_name, count

somesoni2
Revered Legend

The case of the field name matters. If the field name is 'brand_id' , use the same during lookup. (I see the case is different for brand_id, also ensure case for brand_name)

0 Karma

dhavamanis
Builder

Thanks if we try this,

index="idxmember" | stats count by brand_id | lookup lufile_brandid_brandname.csv BRAND_ID OUTPUT BRAND_NAME | table brand_id, BRAND_NAME, count

we are not getting the BRAND_NAME in the output.

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Hi Splunky people! We are excited to share the newest updates in Splunk Enterprise 9.3!Admins and Analyst can ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...