Splunk Search

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

mjones414
Contributor

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

Labels (3)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

<P style=" text-align: center; "><span class="lia-inline-image-display-wrapper lia-image-align-center" ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

<FONT size="5"><FONT size="5" color="#FF00FF">Get the latest news and updates from the Splunk Community ...