How to join two searches that both have subsearches and transactions

[DEAD_BEEF]

I have an index with email data. With it, I have two separate searches that utilize subsearches to put together a set of logs, logs_A and logs_B. All the relevant logs are logs_A + logs_B but I haven't figured out how to combine them. logs_A have a dcid but no message ID. logs_B has a message ID but no message dcid.

query A --> logs_A

index=email 
| [search index=email sender=jsmith@aol.com 
| dedup dcid server
| table dcid server ]
| transaction dcid server

query B --> logs_B

index=email 
| [search index=email sender=jsmith@aol.com 
| dedup message_id server
| table message_id server ]
| transaction message_id server

According to the docs, "If there is a transitive relationship between the fields in the fields list and if the related events appear in the correct sequence, each with a different timestamp, transaction command will try to use it." I tried combining my two queries into one and changing the order of server, dcid, message_id but I could not get it to work no matter what I tried. Logs_A tend to come before B, but not always so I think that's why the transitive property isn't working. Typically A and B are within 2-3 seconds (at most) from each other, more often than note they share timestamps. Any ideas would be appreciated.

I tried running both searches (each with its subsearch) and then using append but that didn't seem to work either. Feel like I'm going in circles on this one...

[Shan · Accepted solution]

@DEAD_BEEF,

I have provided some sample example with test data.
You can replace the logic with your query.
As you have server field in both the query you can go for the join option . To match the both result set.

| makeresults 
| eval tableval1=mvappend("1","2","3","4","5","6")
| eval tableval2 =mvappend("a","b","c","d","e","f")
| mvexpand tableval1
| mvexpand tableval2
| table tableval1 tableval2
| join type=inner   [| makeresults          
          | eval lookupval1 =mvappend("1","3","6")
          | eval  lookupval2=mvappend("1000","1111","2222")
          | mvexpand lookupval1
          | mvexpand lookupval2
          | rename lookupval1 as tableval1
          | table tableval1 lookupval2
          ]
          | table tableval1 lookupval2

---

| makeresults 
| eval tableval1=mvappend("1","2","3","4","5","6")
| eval tableval2 =mvappend("a","b","c","d","e","f")
| mvexpand tableval1
| mvexpand tableval2
| table tableval1 tableval2
| appendcols  [| makeresults          
          | eval lookupval1 =mvappend("1","3","6")
          | eval  lookupval2=mvappend("1000","1111","2222")
          | mvexpand lookupval1
          | mvexpand lookupval2
          | table lookupval1 lookupval2
          ]      
          | eval tableval2=if(tableval1=lookupval1,lookupval2,tableval2)     
          | table *

---

| makeresults 
| eval tableval1=mvappend("1","2","3","4","5","6")
| eval tableval2 =mvappend("a","b","c","d","e","f")
| mvexpand tableval1
| mvexpand tableval2
| table tableval1 tableval2
| append  [| makeresults          
          | eval lookupval1 =mvappend("1","3","6")
          | eval  lookupval2=mvappend("1000","1111","2222")
          | mvexpand lookupval1
          | mvexpand lookupval2
          | table lookupval1 lookupval2
          ]                
          | eval tableval2=if(tableval1=lookupval1,lookupval2,tableval2)     
          | table tableval1 tableval2 lookupval1 lookupval2

Thanks..