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
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 stat
ing 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?