Splunk Search

How to use a csv as a mapping for a table without the use of join

phamxuantung
Communicator

Hi, I have a search that produce the following table

 

Organization|Amount|AcquirerBank
Or_A        |2000  |1234
Or_A        |4000  |2345
Or_B        |1200  |3456
            |4020  |4567
Or_C        |1456  |5678     

 

And then I have a csv file that provide the bank code with the bank name as a mapping csv as

 

AcquirerBank|BankName
1234        |BankA
2345        |BankB
4567        |BankC
5678        |BankD

 

The target table should look something like this

 

Organization|Amount|AcquirerBank|BankName
Or_A        |2000  |1234        |BankA
Or_A        |4000  |2345        |BankB
Or_B        |1200  |3456        |
            |4020  |4567        |BankC
Or_C        |1456  |5678        |BankD

 

 I try to use join like this

 

index=index
|table Organization, Amount, AcquirerBank
|join AcquirerBank
[inputlookup bank_mapping.csv
|table AcquirerBank, BankName]
|table Organization, Amount, AcquirerBank, BankName

 

But I encounter 2 problems:

1. My index have around a million events, and [join] have a limited number of events it can join, so my result table was lack in result.

2. Also [join] don't show enough results if the mapping csv don't have the data, as the example above, if I use [join], OrB with the field Acquirer that don't exist in mapping csv will not show up.

Anyone have a alternative to [join] that can resolve above problems?

Thank you in advance.

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The lookup command does what you want

index=index
|table Organization, Amount, AcquirerBank
|lookup bank_mapping.csv AcquirerBank
|table Organization, Amount, AcquirerBank, BankName

https://docs.splunk.com/Documentation/Splunk/8.2.4/SearchReference/Lookup

0 Karma

phamxuantung
Communicator

Sorry for the late reply, but the lookup command don't give me the desired results, I want the result divided to neat 1 line, instead it show like this

Capture.PNG

it should be in one line for each events.

ALTERNATEACQUIRER is the "Acquirerbank" in my original question.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

There is obviously more to this search than you are letting on, which is fair enough. Having said that, you can collapse the multi-value fields into unique values using the values aggregator

| stats values(BANK_CODE) as BANK_CODE values(TCTQT) as TCTQT ... by ...
0 Karma

phamxuantung
Communicator

I think the natural of my search is fairly simple.

I have an index that have around a million events, each event is a transaction information with needed information which is (_time, ALTERNATEACQUIRER, AMOUNT).

The final result need to be a table with each transaction separately on each row (for search and analyze purpose later), with the column: _time, ALTERNATEACQUIRER, AMOUNT, BANK_CODE, TCTQT.

I have a csv file name BANK_ACQ_BIN.csv that have ALTERNATEACQUIRER with corresponding BANK_CODE and TCTQT.

I try to use join before, but as aforementioned in my original post, [join] function, while it can produce a table satisfy my requirment, can only produce a limited number of events, and don't show the event that have ALTERNATEACQUIRER=null or don't have a matching ALTERNATEACQUIRER in the csv file.

I try to use Automate Lookup but it don't work either.

Sorry if I make you confuse reading this.

0 Karma
Get Updates on the Splunk Community!

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...