topic Re: Join 2 index by 2 fields in Splunk Dev

How to join two indexes by two fields?

SCSC — Wed, 29 Jun 2022 16:18:34 GMT

indexA

field1	field2	field3
A	1	1
A	1	2
A	1	3
A	2	5
B	1	4
B	2	3
B	3	2
C	1	6
C	2	7

indexB

field4	field5	field6
A	1	3
B	2	4
C	1	5
C	1	6

I want to join these 2 indexes by 2 fields (field1=field4 AND field2=field5)

Result :

field1	field2	field3	field6
A	1	1	3
A	1	2
A	1	3
A	2	5
B	1	4
B	2	3	4
B	3	2
C	1	6	5
			6
C	2	7

Re: Join 2 index by 2 fields

bowesmana — Wed, 29 Jun 2022 06:48:59 GMT

You can do this using stats - example with your data

It's best to avoid 'join' construct in Splunk - so by creating the common fields f1 and f2 which represent the fields you want to join by, you can then do stats.

Your search would do a search like this (using 'if' rather than coalesce)

search index_a_dataset OR index_b_dataset | eval f1=if(index="A", field1, field4) | eval f2=if(index="B", field2, field5) | stats values(field3) as f3 values(field6) as f6 by f1 f2

Re: Join 2 index by 2 fields

SCSC — Thu, 30 Jun 2022 04:30:34 GMT

>Your search would do a search like this (using 'if' rather than coalesce)

search index_a_dataset OR index_b_dataset
| eval f1=if(index="A", field1, field4)
| eval f2=if(index="B", field2, field5)
| stats values(field3) as f3 values(field6) as f6 by f1 f2

can I "join" to use this search sentence without "join" command?

| eval f1=if(index="A", field1, field4)
| eval f2=if(index="B", field2, field5)

Can I rewrite

| eval f2=if(index="B", field2, field5)

| eval f2=if(index="A", field2,field5)
?

Re: Join 2 index by 2 fields

SCSC — Thu, 30 Jun 2022 06:20:19 GMT

I tried the way you taught me.

But if f6 is not NULL, f3 is NULL and id f3 is not NULL, f6 is NULL 😞
( can only take one field's figure)

Re: Join 2 index by 2 fields

bowesmana — Thu, 30 Jun 2022 23:00:06 GMT

Without seeing f1 and f2 in this context it's not easy to see what the problem is, however, it would seem like that f1 and f2 fields are not common between the two data sets.

Note that using

search A
append search B

is bot as good as doing search A OR search B - given this simple example.

Can you provide a data sample to make it easier to provide the solution.

Re: Join 2 index by 2 fields

SCSC — Fri, 01 Jul 2022 00:09:23 GMT

This is a sample.
(Both of indexes have other fields. I took out only 3 fields what I needed.)

indexA.PROCESS_NO = indexB.KOUTEI_NO

indexA.TICKET_ID = indexB.WORK_NO

indexA.csv

KOUTEI_NO	WORK_NO	INTERVAL_DIFF
1	F00380006	24
5	F00280002	21
2	F00380005	37
5	F00390001	92
6	F00430009	23
1	F00380006	33
3	F00120006	32
5	F00280002	47
3	F00120005	32
2	F00380005	38
6	F00430009	35
3	F00120006	25
1	F00380006	16
4	F00380002	14
1	F00380006	3
2	F00380005	35
4	F00380002	6
4	F00380002	5
1	F00120010	23
4	F00380002	21
3	F00380003	3
2	F00380005	31
1	F00120010	9
3	F00380003	21
4	F00380002	14
5	F00280001	64
1	F00120010	12
4	F00380002	7
6	F00430009	20
1	F00120009	34
3	F00120003	5
2	F00380004	2
3	F00120012	12
1	F00380009	18
2	F00380008	29
3	F00120012	5
4	F00120008	15
3	F00120012	136
5	F00280006	34
1	F00120016	7
6	F00280003	17
1	F00120016	57

indexB.csv

PROCESS_NO	TICKET_ID	REASON_NO
4	F00120008	63
1	F00120010	14

Re: Join 2 index by 2 fields

bowesmana — Sun, 03 Jul 2022 07:47:42 GMT

Here is the search based on that data

| makeresults | eval _raw="KOUTEI_NO WORK_NO INTERVAL_DIFF 1 F00380006 24 5 F00280002 21 2 F00380005 37 5 F00390001 92 6 F00430009 23 1 F00380006 33 3 F00120006 32 5 F00280002 47 3 F00120005 32 2 F00380005 38 6 F00430009 35 3 F00120006 25 1 F00380006 16 4 F00380002 14 1 F00380006 3 2 F00380005 35 4 F00380002 6 4 F00380002 5 1 F00120010 23 4 F00380002 21 3 F00380003 3 2 F00380005 31 1 F00120010 9 3 F00380003 21 4 F00380002 14 5 F00280001 64 1 F00120010 12 4 F00380002 7 6 F00430009 20 1 F00120009 34 3 F00120003 5 2 F00380004 2 3 F00120012 12 1 F00380009 18 2 F00380008 29 3 F00120012 5 4 F00120008 15 3 F00120012 136 5 F00280006 34 1 F00120016 7 6 F00280003 17 1 F00120016 57" | multikv forceheader=1 | table KOUTEI_NO WORK_NO INTERVAL_DIFF | append [ | makeresults | eval _raw="PROCESS_NO TICKET_ID REASON_NO 4 F00120008 63 1 F00120010 14" | multikv forceheader=1 | table PROCESS_NO TICKET_ID REASON_NO ] | eval f1=coalesce(KOUTEI_NO,PROCESS_NO) | eval f2=coalesce(WORK_NO,TICKET_ID) | stats values(INTERVAL_DIFF) as INTERVAL_DIFF values(REASON_NO) as REASON_NO by f1 f2

I have first set up the data with the 2 makeresults commands and then used the two coalesce statements to create f1 and f2 and then the stats command does the final aggregation

Re: Join 2 index by 2 fields

SCSC — Mon, 04 Jul 2022 00:56:03 GMT

I could do what I wanted to do!

I owe it to you. Thank you very much.