Splunk Search

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

splunker9999
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

sundareshr
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

splunker9999
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

sundareshr
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

splunker9999
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

bshuler_splunk
Splunk Employee
Splunk Employee
0 Karma
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 ...