avoiding joins with rexed fields and unmatched fieldnames in the sources:

[mjones414]

I would love a little guidance on how I could improve this search by getting away from Join.  I think I am hitting some result limits in doing so, but I could not get the same results using appendcols:  I've also tried putting (sourctype=mysourcetype OR source=my_other_source) and renaming the field in the other source to match the field in the first source but that didn't work either.

...| stats latest(u_subscription_type) latest(sys_created_on) latest(dv_u_expiration_date) latest(dv_sys_updated_on) latest(u_status) latest(u_corresponding_ritm_number) latest(dv_u_corresponding_ritm_number) by dv_u_info,dv_u_sso,u_logonid
| `rename(latest)`
| eval record_updated=strptime(dv_sys_updated_on,"%Y-%m-%d %H:%M:%S"),record_created=strptime(sys_created_on,"%Y-%m-%d %H:%M:%S"),record_expires=strptime(dv_u_expiration_date, "%Y-%m-%d"),now=now()
| eval USBViolation=if(now > record_expires, "yes", "no")
| join type=left
[ search (source=my_other_source)
| rex field=horrible_field "#50\s+\-\s+(?P<business_justification>.+?)\s+\-\sBusiness\sCase"
| eval u_corresponding_ritm_number=request_item
|stats latest(business_justification) latest(dv_comments_and_work_notes) by u_corresponding_ritm_number
| `rename(latest)`]
| fields - now,record_created,record_exires,record_updated,record_expires

[ITWhisperer · Accepted solution]

Assuming that fields in one search do not appear in the other search, and that u_corresponding_ritm_number/request_item is unique, you could try something like this:

(search one OR search two)
| rex field=horrible_field "#50\s+\-\s+(?P<business_justification>.+?)\s+\-\sBusiness\sCase"
| eval u_corresponding_ritm_number=coalesce(u_corresponding_ritm_number,request_item)
| eventstats latest(business_justification) as  business_justification latest(dv_comments_and_work_notes) as dv_comments_and_work_notes by u_corresponding_ritm_number
| stats latest(u_subscription_type) latest(sys_created_on) latest(dv_u_expiration_date) latest(dv_sys_updated_on) latest(u_status) latest(u_corresponding_ritm_number) latest(dv_u_corresponding_ritm_number) latest(business_justification) latest(dv_comments_and_work_notes) by dv_u_info,dv_u_sso,u_logonid
| `rename(latest)`
| eval record_updated=strptime(dv_sys_updated_on,"%Y-%m-%d %H:%M:%S"),record_created=strptime(sys_created_on,"%Y-%m-%d %H:%M:%S"),record_expires=strptime(dv_u_expiration_date, "%Y-%m-%d"),now=now()
| eval USBViolation=if(now > record_expires, "yes", "no")
| fields - now,record_created,record_exires,record_updated,record_expires

[mjones414]

This is awesome, I completely forgot about eventstats!  Thanks for this great example of getting past join limitations!