Joining multiple tables - about 3 or more tables

Bentash — Tue, 29 Sep 2020 19:51:30 GMT

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

Re: Joining multiple tables - about 3 or more tables

DalJeanis — Thu, 07 Jun 2018 14:35:46 GMT

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

Re: Joining multiple tables - about 3 or more tables

Bentash — Thu, 07 Jun 2018 16:12:15 GMT

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?

topic Re: Joining multiple tables - about 3 or more tables in Splunk Search

Joining multiple tables - about 3 or more tables

Re: Joining multiple tables - about 3 or more tables

Re: Joining multiple tables - about 3 or more tables