I need a different way to join two searches

[rodolfotva]

Hi, I know this is a hot topic and there is answers everywhere, but i couldn't figure out by my self what to do.

Suddenly the join stops work and my search is not performing as spect anymore, nobody from infa gave me a reasonable explanation for that, so i have to figure out a different way .

Original Search

index=aws-prd-01 application.name=domestic-batch context=BATCH action=SEND_EMAIL  (status=STARTED OR status="NOT RUN")
              | rename status as initialStatus
              | fields jobId initialStatus
              | join type=left jobId [search index=aws-prd-01 application.name=domestic-batch context=BATCH action=SEND_EMAIL (status=COMPLETED OR status=FAILED)
              | rename status as finalStatus | fields jobId finalStatus]
              | table jobId initialStatus finalStatus
              | sort -timestamp

Original result

jobId	initialStatus	finalStatus
01	STARTED	COMPLETED
02	STARTED	FAILED

First search with no changes

index=aws-prd-01 application.name=domestic-batch context=BATCH action=SEND_EMAIL  (status=STARTED OR status="NOT RUN") | table jobId, status

Result

jobId	status
01	STARTED
02	STARTED

Second search with no changes

index=aws-prd-01 application.name=domestic-batch context=BATCH action=SEND_EMAIL (status=COMPLETED OR status=FAILED) | table jobId, status

Result

jobId	status
01	COMPLETED
02	FAILED

thanks a lot

[richgalloway · Accepted solution]

You don't say what the current results are for the combined query, but perhaps a different approach will work.  The two searches can be combined into a single search.

index=aws-prd-01 application.name=domestic-batch context=BATCH action=SEND_EMAIL  (status=STARTED OR status="NOT RUN" OR status=COMPLETED OR status=FAILED)
| eval initialStatus = if(status="STARTED" OR status="NOT RUN", status, null()),
       finalStatus = if (status="COMPLETED" OR status="FAILED", status, null())
| stats values(*) as * by jobId
| table jobId initialStatus finalStatus

This search avoids the limitations of join and only touches the index once.

[ITWhisperer]

Try to avoid using join - subsearches are limited

index=aws-prd-01 application.name=domestic-batch context=BATCH action=SEND_EMAIL  (status=STARTED OR status="NOT RUN" OR status=COMPLETED OR status=FAILED)
| eval initialStatus=if(status="STARTED" OR status="NOT RUN", status, null())
| eval finalStatus=if(status="COMPLETED" OR status="FAILED", status, null())
| stats values(initialStatus) as initialStatus values(finalStatus) as finalStatus by jobId