Splunk Search

Joining multiple tables - about 3 or more tables


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


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


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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...