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 sysid
join [table1+table2] + table 3, using parentid
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.
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