How to Join Command to compare 2 fields as a basis of comparison?

[SplunkDash]

Hello,

How can we use 2 Fields to compare in Join Command. I have lookup table with tix1, tix2, tx3, and tx4 fields ; I also have index with tix1, tix2, ix3, and ix4 fields. How to use join command using tix1 and tix2 fields as a basis of comparison. It is working well when I use tix1 or tix2 fields as a basis for comparison. But it doesn't work when I use both tix1 and tix2. Any recommendation will be highly appreciated. Thank you!

Here is what I did with one field and working as expected

 |inputlookup x_account.csv 

| search tix2  IN(03,05)

| table tix1, tix2, tx3, and tx4

| join type=left tix1

[search index=idx_Account sourcetype="idx:events" tix2  IN(03,05)  |table Stix1, tix2, ix3, and ix4 ] 

But need to use:

|inputlookup x_account.csv 

| search tix2  IN(03,05)

| table tix1, tix2, tx3, and tx4

| join type=left tix1 tix2

[search index=idx_Account sourcetype="idx:events" tix2  IN(03,05)  |table Stix1, tix2, ix3, and ix4 ] 

[bowesmana]

How big is your join dataset? Join has limitations (max 50,000 events in the join dataset) and writing the search this way round is typically the wrong way round - you would usually search the index, then lookup the index data to get the related lookup data.

[ITWhisperer]

Joining with more than one field does work, but perhaps not in the way you expect - please can you give some examples of the rows you have in your look up table and the events you expect them to join with, particularly for event you say it isn't working?

[SplunkDash]

Hello @ITWhisperer and @bowesmana 

Volume of data around 25K events, so we should be fine based on your number. Regarding content of the fields

 tix1     tix2

010     9810!MA909

010     950021Q608

091    89120985A9

087   10982101B9

[ITWhisperer]

Can you give an example of the events which aren't being joined as you expect?

[SplunkDash]

Hello @ITWhisperer,

Thank you so much for your quick response. I got an idea to get these issues resolved.

Here is what I did:

|inputlookup x_account.csv 

| search tix2  IN(03,05)| eval  con2field=tix1 + tix2

| table tix1, tix2, tx3, tx4, con2field

| join type=left con2field

[search index=idx_Account sourcetype="idx:events" tix2  IN(03,05)  | eval  con2field=tix1 + tix2 |table Stix1, tix2, ix3, ix4, con2field ]