Splunk Search

Joining multiple tables - about 3 or more tables

Bentash
Explorer

I have about 4 different tables that i am trying to join

table 1 and table two have a common id, sys_id

and when you join those two there is another common field parent_id which is also available in table 3

in this case how do i join the three tables since table 3 has parent_id and its also in the other two join

join table1 + table2, using sys_id
join [table1+table2] + table 3, using parent_id

Tags (2)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Okay, first, the word "join" tends to have the wrong connotation here. There is a keyword join in splunk that represents a particular way of connecting tables. There are a half dozen other ways to join tables, and join is not usually the best.

Start by reading this reference ... https://answers.splunk.com/answers/561130/how-to-join-two-tables-where-the-key-is-named-diff.html

Then apply the following pseudocode, when every table has a single matching key...

 your search that selects ALL the records you want from any table 
| fields ... list all the fields you want from any table...
| eval matchfield=case(if it is table 1, the matchfield from table 1, 
       if it is table 2, the matchfield from table 2... etc)
| stats values(*) as * by matchfield

The codes is slightly more complicated when you have a second key somewhere. This gives you an example of rolling data over from a third record onto the second one before stating the final answer.

assumptions -

table 1 has index=index1,   key1  data1
table 2 has index=index2    key2 (which is the same data as key1) subkey2 (which is the same data as key3) data2 
table 3 has index=index3    key3 (which is the same data as subkey2) data3  

sample code -

 your search that selects ALL the records you want from any table 
| fields index key1 key2 key3 subkey2 data1 data2 data3 

| rename COMMENT as "roll data from index3 to index2 records by subkey/key3 then drop index3 records"
| eval subkey2=coalesce(subkey2,key3)
| eventstats values(data3) as data3 by subkey2
| where index!="index3"

| rename COMMENT as "set key1 and stats together all  data from the remaining records"
 | eval key1=case(index="index1", key1, 
       index="index2", key2)
| stats values(*) as * by key1

Bentash
Explorer

Thanks DalJeanis, this makes sense , but what if in the assumption table 2 has a key1 also but those are different values from table 1 but you want to pipe the field for key1 in table 1?

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