Splunk Dev

How to join two indexes by two fields?

SCSC
Explorer

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  
Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

You can do this using stats - example with your data

| makeresults
| eval _raw="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"
| multikv forceheader=1
| table field1 field2 field3
| eval index="A"
| append [
  | makeresults
  | eval _raw="field4	field5	field6
A	1	3
B	2	4
C	1	5
C	1	6"
  | multikv forceheader=1
  | table field4 field5 field6
  | eval index="B"
]
| eval f1=coalesce(field1, field4)
| eval f2=coalesce(field2, field5)
| stats values(field3) as f3 values(field6) as f6 by f1 f2

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

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You can do this using stats - example with your data

| makeresults
| eval _raw="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"
| multikv forceheader=1
| table field1 field2 field3
| eval index="A"
| append [
  | makeresults
  | eval _raw="field4	field5	field6
A	1	3
B	2	4
C	1	5
C	1	6"
  | multikv forceheader=1
  | table field4 field5 field6
  | eval index="B"
]
| eval f1=coalesce(field1, field4)
| eval f2=coalesce(field2, field5)
| stats values(field3) as f3 values(field6) as f6 by f1 f2

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

 

0 Karma

SCSC
Explorer

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

to

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

Tags (1)
0 Karma

SCSC
Explorer

I tried the way you taught me.

index="indexA"
| table field1, field2, field3
| append [
| search index="indexB"
| table field4, field5, field6
]
| eval f1=coalesce(field1,field4)
| eval f2=coalesce(field2,field5)
| stats values(field3) as f3 values(field6) as f6 by f1 f2


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)

SCSC_0-1656570007604.png

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

SCSC
Explorer

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_NOWORK_NOINTERVAL_DIFF
1F0038000624
5F0028000221
2F0038000537
5F0039000192
6F0043000923
1F0038000633
3F0012000632
5F0028000247
3F0012000532
2F0038000538
6F0043000935
3F0012000625
1F0038000616
4F0038000214
1F003800063
2F0038000535
4F003800026
4F003800025
1F0012001023
4F0038000221
3F003800033
2F0038000531
1F001200109
3F0038000321
4F0038000214
5F0028000164
1F0012001012
4F003800027
6F0043000920
1F0012000934
3F001200035
2F003800042
3F0012001212
1F0038000918
2F0038000829
3F001200125
4F0012000815
3F00120012136
5F0028000634
1F001200167
6F0028000317
1F0012001657

 

indexB.csv

PROCESS_NOTICKET_IDREASON_NO
4F0012000863
1F0012001014
Tags (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

SCSC
Explorer

I could do what I wanted to do!

I owe it to you. Thank you very much.

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...