topic Re: how to join 2 lookup files to combine all the rows. in Splunk Search

how to join 2 lookup files to combine all the rows.

Anud — Wed, 08 Nov 2023 13:24:45 GMT

how to join 2 lookup files to combine all the rows.

I used this query but not giving proper values and used join/append no use.
| inputlookup fileA
table A E F
|join
[
inputlookup fileB.csv]
table E A B C
One file data looks:

A	E	F
234	CAR	2
456	BUS	3

Second file data:

A	B	C
234	MON	3
234	TUES	4
234	WED	5
234	THUR	1
234	FRI	2
234	SAT	1
456	MON	3
456	TUES	4
456	WED	5
456	THUR	1
456	FRI	2
456	SAT	1

Final output be like :

E	A	B	C
CAR	234	MON	3
CAR	234	TUES	4
CAR	234	WED	5
CAR	234	THUR	1
CAR	234	FRI	2
CAR	234	SAT	1
BUS	456	MON	3
BUS	456	TUES	4
BUS	456	WED	5
BUS	456	THUR	1
BUS	456	FRI	2
BUS	456	SAT	1

Thanks in Advance..!!

Re: how to join 2 lookup files to combine all the rows.

FelixLeh — Wed, 08 Nov 2023 13:27:37 GMT

| inputlookup fileB.csv table A E F |lookup fileA.csv A OUTPUT E

Re: how to join 2 lookup files to combine all the rows.

Anud — Wed, 08 Nov 2023 13:55:28 GMT

sorry i didnt get you
this is the file flow

One fileA data looks:

A	E	F
234	CAR	2
456	BUS	3

Second fileB data:

A	B	C
234	MON	3
234	TUES	4
234	WED	5
234	THUR	1
234	FRI	2
234	SAT	1
456	MON	3
456	TUES	4
456	WED	5
456	THUR	1
456	FRI	2
456	SAT	1

Final output be like :

E	A	B	C
CAR	234	MON	3
CAR	234	TUES	4
CAR	234	WED	5
CAR	234	THUR	1
CAR	234	FRI	2
CAR	234	SAT	1
BUS	456	MON	3
BUS	456	TUES	4
BUS	456	WED	5
BUS	456	THUR	1
BUS	456	FRI	2
BUS	456	SAT	1

Re: how to join 2 lookup files to combine all the rows.

FelixLeh — Wed, 08 Nov 2023 14:16:50 GMT

Yes.
1. You have to Load the Second lookup into your search.
You do so by loading the lookup file with the inputlookup command.

|inputlookup fileB.csv

2. A lookup that is inside splunk can be used to add data onto existing events or table data.
To do so you have to use the lookup command. You tell Splunk the name of the lookup, which field it shall use to add the data and which fields to add from the lookup

| lookup fileA.csv A OUTPUT E

Since field A are both in fileA and fileB you can use it to enrich your table with data from the other lookup. You tell splunk that you want to add data from fileA.csv and that the file that is present in both datasets is A then you tell Splunk to OUTPUT the field E to the current table.

This results in a query like in my previous answer. When the correct fieldnames and lookup file names are used this should lead to your desired output.

Re: how to join 2 lookup files to combine all the rows.

Anud — Wed, 08 Nov 2023 17:27:53 GMT

Thanks for the explanation..!!

This is not working.

| inputlookup fileB.csv
table A E F
|lookup fileA.csv A OUTPUT E

This is the actual result, but not getting the proper results.

Re: how to join 2 lookup files to combine all the rows.

bowesmana — Wed, 08 Nov 2023 22:47:58 GMT

@Anud Your search is NOT doing what @FelixLeh suggested.

The idea is that you load the SECOND lookup (fileB) first and then lookup the common field A to get the required field E from the FIRST lookup.

Your example shows that fileB contains the data and fileA contains the missing field E (CAR/BUS) that is needed to enrich fileB data.

Note that your actual search uses append with a subsearch - you should not do it that way, as inputlookup already has an append option and this method does not have the limitations of a subsearch, i.e.

| inputlookup append=t file

Re: how to join 2 lookup files to combine all the rows.

FelixLeh — Thu, 09 Nov 2023 08:46:09 GMT

@bowesmana am I misunderstanding what @Anud wants to achieve? For me it sounded like a simple lookup combined with a inputlookup.

Re: how to join 2 lookup files to combine all the rows.

bowesmana — Thu, 09 Nov 2023 23:23:08 GMT

@FelixLeh no - you are absolutely right and your suggestion looks fine, but @Anud was using append and a subsearch with inputlookup and not what you suggested...