topic Re: How to use a csv as a mapping for a table without the use of join in Splunk Search

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

phamxuantung — Fri, 14 Jan 2022 04:40:23 GMT

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

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.

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

ITWhisperer — Fri, 14 Jan 2022 06:49:16 GMT

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

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

phamxuantung — Tue, 15 Feb 2022 07:46:59 GMT

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

it should be in one line for each events.

ALTERNATEACQUIRER is the "Acquirerbank" in my original question.

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

ITWhisperer — Tue, 15 Feb 2022 07:54:43 GMT

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

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

phamxuantung — Tue, 15 Feb 2022 09:38:53 GMT

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.