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!
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