Joining two searches on different indexes based on lookup values and return calculated values from both searches

[ADRIANODL]

Hi folks,
This is a complex question, so bear with me. We have 2 heavy searches that return calculated and lookup values as per below.

The first search returns information about emails with a certain subject sent by an individual:
Search 1:

    index=o365 tag=email SenderAddress=*@mycompany.com RecipientAddress!=*@mycompany.com 
    | lookup tablename Email_Address as SenderAddress OUTPUT Full_Name JobTitle Manager_Name Office_Location Job_Level Mobile_Number Country as CountryCD 
    | eval hasNameInSubject=case(like(lower(Subject), "%" . lower(Full_Name) . "%"), 1, 1=1, 0)
    | search hasNameInsubject=1 OR Subject="*confidential*" ) 
    | bucket _time span=1d 
    | stats values(Full_Name) as FullName values(JobTitle) as JobTitle  values(Manager_Name) as ManagerName values(Office_Location) as Location values(Job_Level) as JobLevel list(RecipientAddress) as Recipients list(Subject) as Subject dc(_time) as num_days sum(hasNameInSubject) as NameInSubject by SenderAddress 
    | where num_days>1 OR mvcount(recipients) > 3 OR mvcount(Subject)>3 
    | search JobTitle!="*Manager*" AND JobTitle!="*Director*" 
| sort -  NameInSubject

Results look something like this:
SenderAddress, FullName, JobTitle, ManagerName, Location, JobLevel, Recipients, Subject, num_days, NameInSubject
john@mycompany.com, John Doe, Clerk, Joe Bloggs, London, 3, email@email.com, Re:Confidential John Doe, 3, 7

The second search returns information about users copying data to USB drives:

index=beta sourcetype="activities"
| spath
| search "tags{}.category_id"=USBTFR
|rex field=User_Name "(?<User_Name>\w{3,6})" 
|eval User_Name=upper(User_Name) 
|lookup tablename LogonID as User_Name OUTPUT EmailAddress DisplayName BusinessUnit Department Mobile JobTitle JobLevel 
|stats count by DisplayName, EmailAddress, Mobile, JobTitle, BusinessUnit, Department,  JobLevel
|sort -count
|rename count as "Number of Transfered Files"

Results look like this:
DisplayName, EmailAddress, Mobile, JobTitle, BusinessUnit, Department, JobLevel, Number of Transfered Files
John Doe, john@mycompany.com, +444444444444, Clerk, Sales, North, 3, 56000

What I wanted to have as a single table is:
SenderAddress, FullName, JobTitle, ManagerName, Location, JobLevel, Recipients, Subject, num_days, NameInSubject, Number of Transfered Files (the last column coming from the second search)

I hope this makes sense. If not, please let me know...

Thank you!

[woodcock]

Find a way to get combined results starting with a base search like this:

(index=o365 tag=email SenderAddress=*@mycompany.com RecipientAddress!=*@mycompany.com) OR (index=beta sourcetype="activities") | ...

Then wrap it up as shown in this stand-alone demo here:

|makeresults | eval _raw="SenderAddress=john@mycompany.com,FullName=John Doe,JobTitle=Clerk,ManagerNameJoe Bloggs,Location=London,JobLevel=3,Recipients=email@email.com,Subject=Re:Confidential,NameInSubject=John_Doe,num_days=3"
| append [|makeresults | eval _raw="DisplayName=John Doe,EmailAddress=john@mycompany.com,Mobile=+444444444444,JobTitle=Clerk,BusinessUnit=Sales,Department=North,JobLevel=3,Number_of_Transfered_Files=56000" ]
| kv

| eval JOINER = coalesce(EmailAddress, SenderAddress)
| stats values(*) AS * BY JOINER