topic Re: avoiding joins with rexed fields and unmatched fieldnames in the sources: in Splunk Search

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

mjones414 — Fri, 30 Apr 2021 20:49:18 GMT

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

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

ITWhisperer — Sat, 01 May 2021 09:11:04 GMT

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

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

mjones414 — Sun, 02 May 2021 00:42:36 GMT

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