Two searches but only need to return results of the second search that match the first

[RickerNJ]

ok so...I have been banging my head against the wall on this one for a bit. I have tried using join (which I don't and can't use because the second search needs to return all results, more than 50k events), tried appends commands, union and a few others. I may be missing something or it just may not be possible.

Currently I am working with | multisearch and both searches return results but the second search is not populating the field (territory) based on the hits from the first search. The results look like this and the territory column goes on for a few pages. This will be run on a 24hour run cycle btw, that's the reason I can not use join, a 24 hour run on that second search does not return all results due to the join cap.

As you can see my problem - trying to just return results for that territory if it matches a hit from the first search.

This is what I am currently running and what it outputs above:

---

| multisearch

[ search index=mft_prod* sourcetype=UploadedDocumentsPath RecipientAddress!="company.com" (RecipientAddress="@gmail" OR RecipientAddress="@live" OR RecipientAddress="@yahoo" OR RecipientAddress="@aol" OR RecipientAddress="@hotmail" OR RecipientAddress="@outlook" OR RecipientAddress="@Verizon" OR RecipientAddress="@comcast" OR RecipientAddress="@icloud" OR RecipientAddress="@mail.ru")
| eval Total_MB_Size=round((fileSize/1024/1024),2)
| where Total_MB_Size > 100
| fields Total_MB_Size, SenderAddress, RecipientAddress]

[ search
index=snow_glbl sourcetype=snow:sys_user earliest=-5d@d
| fields dv_email, dv_u_territory]

| eval dv_email=SenderAddress

| table SenderAddress, RecipientAddress, Total_MB_Size, dv_u_territory

---

Any ideas what I am missing or doing wrong with this one?

[chrisyounger · Accepted solution]

Looking at this a second time, if I were you, I would create a regularly scheduled search that parses your ServiceNow data into a CSV. Something like this, scheduled to happen daily (or whatever you want):

index=snow_glbl sourcetype=snow:sys_user | stats latest(email) as email latest(dv_u_territory) as dv_u_territory by sys_id |outputlookup servicenow_email_to_territory.csv

Then you can use this in any search like so:

index=mft_prod* sourcetype=UploadedDocumentsPath RecipientAddress!="company.com" (RecipientAddress="@gmail" OR RecipientAddress="@live" OR RecipientAddress="@yahoo" OR RecipientAddress="@aol" OR RecipientAddress="@hotmail" OR RecipientAddress="@outlook" OR RecipientAddress="@verizon" OR RecipientAddress="@comcast" OR RecipientAddress="@icloud" OR RecipientAddress="@mail.ru" 
| eval Total_MB_Size=round((fileSize/1024/1024),2) 
| search Total_MB_Size > 100 
| lookup servicenow_email_to_territory.csv dv_u_territory as SenderAddress OUTPUT dv_u_territory
| table SenderAddress, RecipientAddress, Total_MB_Size, dv_u_territory

[RickerNJ]

@chrisyoungerjds

Thank you for the response. I did not even think of dumping the snow data to a csv for use in lookup. I am working on this now and will report back.

[RickerNJ]

not working - keeping getting
"Streamed search execute failed because: Error in 'lookup' command: The lookup table 'servicenow_email_to_territory.csv' does not exist or is not available."

The report I setup like you suggested runs and if I do an | inputlookup on the file it returns results. I checked permissions on the lookup and their Global.

Im still playing around with it but figured id let you know - ill update if i getting it working.

[RickerNJ]

Going to mark that second one as the answer. I believe it's something related to permissions, even though everything is set correctly. Have an email over to one of the engineers. Thank you again for the help!! @chrisyoungerjds

[chrisyounger]

Hi @RickerNJ

Give this a try

(index=mft_prod* sourcetype=UploadedDocumentsPath RecipientAddress!="company.com" (RecipientAddress="@gmail" OR RecipientAddress="@live" OR RecipientAddress="@yahoo" OR RecipientAddress="@aol" OR RecipientAddress="@hotmail" OR RecipientAddress="@outlook" OR RecipientAddress="@verizon" OR RecipientAddress="@comcast" OR RecipientAddress="@icloud" OR RecipientAddress="@mail.ru") OR index=snow_glbl sourcetype=snow:sys_user earliest=-5d@d) 
| eval Total_MB_Size=round((fileSize/1024/1024),2)  
| search Total_MB_Size > 100  
| eval email = coalesce(SenderAddress, dv_email) 
| eventstats latest(dv_u_territory) as dv_u_territory by email
| search Total_MB_Size=*
| table SenderAddress, RecipientAddress, Total_MB_Size, dv_u_territory

Hope it helps