topic Re: Join with subsearch doesn't gives me expected results in Splunk Search

Join with subsearch doesn't gives me expected results

arulbalans — Tue, 29 Sep 2020 10:06:57 GMT

Query1-Results:
ProxiesProcessed,Status

Query2-Results:
ProxiesProcessed,Audio_Tracks,year_mm_dd

Join Query:

index=index1 
host=node1 
source="results.log" 
"item: " AND "valid_audio_path: " |eval ProxiesProcessed=trim(substr(_raw,101,11)) | dedup ProxiesProcessed |rename ProxiesProcessed as P_ProxiesProcessed 
|eval Audio_Tracks = trim(substr(_raw,130,len(_raw)-129)) 
|eval year_mm_dd = trim(substr(Audio_Tracks,36,07)) 
|fields P_ProxiesProcessed,Audio_Tracks,year_mm_dd
|join type=left max=0 P_ProxiesProcessed 
[search index=index1 host=node1 source="results.log" 
"Item Processed :: " 
|eval ProxiesProcessed=trim(substr(_raw,112,10)) 
|eval Status=trim(substr(_raw,144,len(_raw)-143)) 
|dedup ProxiesProcessed 
|where Status="already_transcoded" 
|fields ProxiesProcessed,Status 
|rename ProxiesProcessed  as P_ProxiesProcessed 
|rename Status  as S_Status ]
|table P_ProxiesProcessed,Audio_Tracks,year_mm_dd,S_Status

I'm trying to join with the ProxiesProcessed field to get matching results with Query1 & Query2

If I execute the Query1 (used in subsearch) separately, gives me 300 events matched.

If I execute the Query2 (used in outer search) separately, gives me 20k events matched.

Any help is really appreciated.

Thanks, Arul

Re: Join with subsearch doesn't gives me expected results

woodcock — Wed, 29 Jun 2016 15:39:03 GMT

Try this:

index=index1 host=node1 source="results.log" (("item: " AND "valid_audio_path: ") OR ( "Item Processed :: "))
| eval type=if(searchmatch("item: "), "Left", "Right")
| eval ProxiesProcessed=if((type="Left"), trim(substr(_raw,101,11)), trim(substr(_raw,112,10)))
| eval Status=if((type="Right"), trim(substr(_raw,144,len(_raw)-143), Status) 
| dedup ProxiesProcessed type
| search type="Left" OR Status="already_transcoded"
| rename ProxiesProcessed AS P_ProxiesProcessed 
| eval Audio_Tracks =if((type="Right"), trim(substr(_raw,130,len(_raw)-129)), Audio_Tracks)
| eval year_mm_dd =if((type="Right"), trim(substr(Audio_Tracks,36,07)), year_mm_dd)
| fields P_ProxiesProcessed Status Audio_Tracks year_mm_dd
| stats dc(type) AS numTypes values(*) AS * BY P_ProxiesProcessed
| rename Status AS S_Status
| table P_ProxiesProcessed Audio_Tracks year_mm_dd S_Status numTypes

This gives the fully merged set (full join). For each function, tack on the appropriate remaining search string:

For Left Join:

| search type="Left"

For right join:

| search type="Right"

For XOR (outer join):

| search numTypes= 1

For inner join:

| search numTypes>1

Re: Join with subsearch doesn't gives me expected results

arulbalans — Tue, 05 Jul 2016 22:04:58 GMT

Mr. Woodcock,
It works to me.
I appreciate your help on this part, Thank you so much.

Thanks, Arul

Re: Join with subsearch doesn't gives me expected results

arulbalans — Tue, 29 Sep 2020 10:10:29 GMT

Mr. Woodcock,
One more help from you.

Audit Logs:
2016-06-12 00:48:29,834 INFO [MainThread][PID:3143] item: AR001SJFBS valid_audio_path: /PROXY_AUDIO/2011/05/31/AR001SJFBS_3.mp2||/stornext/MAM_LOWRES_5/PROXY_AUDIO/2011/05/31/AR001SJFBS_4.mp2
2016-06-12 00:48:29,834 INFO [MainThread][PID:3143] Item Submitted :: AR001SJFBS
2016-06-12 00:48:40,730 INFO [MainThread][PID:3143] Item Processed :: AR001SJFBS, Transcode Status :: error

2016-06-27 08:30:20,169 INFO [MainThread][PID:29112] item: AR001SJFBS valid_audio_path: /PROXY_AUDIO/2011/05/31/AR001SJFBS_3.mp2||/stornext/MAM_LOWRES_5/PROXY_AUDIO/2011/05/31/AR001SJFBS_4.mp2
2016-06-27 08:30:20,169 INFO [MainThread][PID:29112] Item Submitted :: AR001SJFBS
2016-06-27 08:51:56,680 INFO [MainThread][PID:29112] update metadat:: file system check True, new access path L:\PROXY\2011\05\31\AR001SJFBS.mp4
2016-06-27 08:52:13,879 INFO [MainThread][PID:29112] Existing proxy backup at P:\MPEG_Backup\PROXY\2011\05\31\AR001SJFBS.mpeg ::
2016-06-27 08:52:13,879 INFO [MainThread][PID:29112] Item Processed :: AR001SJFBS, Transcode Status :: completed

Question, from the above log
I'm re-submitting jobs those are failed with error/aborted/failed status. So possibilities of repeated proxies present in the list.
I've to treat the transcode status of "AR001SJFBS" as "completed" instead "error" since completed is the latest status.

Thanks, Arul

Re: Join with subsearch doesn't gives me expected results

woodcock — Sat, 09 Jul 2016 00:05:43 GMT

I do not understand. It would probably be best to start over and ask a new question but be sure to be as specific as possible.

Re: Join with subsearch doesn't gives me expected results

arulbalans — Tue, 12 Jul 2016 21:01:47 GMT

Yup, the same question I posted here: https://answers.splunk.com/answers/427007/get-the-latest-event-status-of-the-jobsre-submitte.html, thanks.