Splunk Search

how to join the two tables?

youngrap
Explorer

I have 2 tables
I'd like to join the tables.

for example : 

A table

str1
str2
str3


B table

str4val1oval1
str5val2oval2
str6val3oval3


result : A + B table

str1str4val1oval1
str1str5val2oval2
str1str6val3oval3
str2str4val1oval1
str2str5val2oval2
str2str6val3oval3
str3str4val1oval1
str3str5val2oval2
str3str6val3oval3


thank you.

Labels (2)
Tags (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

Assuming f1.csv contains the values of table A with field name f1 and tableb.csv contains the values of table b with field names C1, C2 and C3 the following does what you want

| inputlookup f1.csv
| append
[ 
| inputlookup tableb.csv
| eval B=C1.",".C2.",".C3
| table B
]
| eventstats values(B) as B
| where !isnull(f1)
| mvexpand B
| rex field=B "(?<C1>[^,]*),(?<C2>[^,]*),(?<C3>.*)"
| table f1, C1, C2, C3

Hope this helps

View solution in original post

0 Karma

to4kawa
Ultra Champion

Are you going to talk about Splunk? where is the field name? 

sample:

| makeresults
| eval Atable=split("str1,str2,str3",",")
| mvexpand Atable
| append [|makeresults| eval _raw="str,val,oval
str4,val1,oval1
str5,val2,oval2
str6,val3,oval3"
| multikv forceheader=1 ]
| eventstats list(Atable) as Atable
| mvexpand Atable
| where !isnull(linecount)
| table Atable str val oval

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Assuming f1.csv contains the values of table A with field name f1 and tableb.csv contains the values of table b with field names C1, C2 and C3 the following does what you want

| inputlookup f1.csv
| append
[ 
| inputlookup tableb.csv
| eval B=C1.",".C2.",".C3
| table B
]
| eventstats values(B) as B
| where !isnull(f1)
| mvexpand B
| rex field=B "(?<C1>[^,]*),(?<C2>[^,]*),(?<C3>.*)"
| table f1, C1, C2, C3

Hope this helps

0 Karma

youngrap
Explorer

Thank you, I solved it:)

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Onboarding New Hires & ...

This is the fifth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...

The Visibility Gap: Hybrid Networks and IT Services

The most forward thinking enterprises among us see their network as much more than infrastructure – it's their ...