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