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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...