Splunk Search

How to add an additional column in my results from a lookup file?

Path Finder

Hi ,

Need your help in adding additional column in my results table from lookup file

We have below search which would give us count of all our clients.

index=idx_1_* sourcetype=idx_gr_json  source="/data/bdata/serial//main/SPLUNK/splunk_output_*.Json"|stats count(GR_File_Details{}.GR_File_Instance_Id) as count by GR_File_Details{}.GCID|rename GR_File_Details{}.GCID as GCID

We have lookup file(Client_details.csv) with below fields.

GCID             Clientname
UNI              Unilever
MS               Microsoft
bofa             bankof 

Now we need to use this lookup file and pull clientname to above search based on GCID.

Can someone please help us in doing so, we are unsuccessful to achieve this?

0 Karma

Legend

Like this (GCID has to be an exact/case-sensitive match, between indexed data and the lookup file)

index=idx_1_* sourcetype=idx_gr_json  source="/data/bdata/serial//main/SPLUNK/splunk_output_*.Json" | stats count('GR_File_Details{}.GR_File_Instance_Id') as count by 'GR_File_Details{}.GCID' | rename GR_File_Details{}.GCID as GCID | lookup Client_details.csv GCID OUTPUT Clientname

Path Finder

Tried running above, but its not returing the fields from Clientname, it is displaying only if there is common values, Is there any thing we need to tweak?

  GCID  Count  Clientname
    IBM      1               
    DTN      1             
    NA         1             na
    MS         3                
0 Karma

Legend

Clientname will be added ONLY if there is an exact match. You may also want to consider trim() GCID in the event data. Like this

index=idx_1_* sourcetype=idx_gr_json  source="/data/bdata/serial//main/SPLUNK/splunk_output_*.Json" | stats count('GR_File_Details{}.GR_File_Instance_Id') as count by 'GR_File_Details{}.GCID' | rename GR_File_Details{}.GCID as GCID | eval GCID=trim(GCID) | lookup Client_details.csv GCID OUTPUT Clientname
0 Karma

Path Finder

Thanks, When I ran this search ,it not poppoing results we want. Do i need to tweak any thing ?

I am getting results as below:
GCID Count Clientname
DTN 1

IBM 1

NA 1 na
MS 2

0 Karma

Splunk Employee
Splunk Employee
0 Karma