How to Improve a search with Join or suggest alternate way

[RamMur]

Hello,

I'm trying to join based on a common field using a similar query like below, however, the in the result i only get partial results from the right side, probably because the search volume ( i guess), or may be my query is not right, can we do this without join or properly use join ? TIA

index=provisioning_index sourcetype=PCF:log source_type=APP/PROC/WEB message_type=OUT cf_org_name=org1 cf_app_name=APP1 LOG_LEVEL="ERROR" service=service1 errorCd="DOC-MGMT*"
|fields _time errorCd errorDetails stateCode letterId documentId
|rex field=_raw "errorDetails=(?<errorDetails>.*?)\s*:"
|join left=lerr right=rlkp type=left where lerr.documentId = rlkp.documentId max=0
[search index=provisioning_index sourcetype=PCF:log source_type=APP/PROC/WEB message_type=OUT cf_org_name=org1 cf_app_name=APP1 NOT letterId=null operation=generateInstantDocument
|fields _time errorCd errorDetails stateCode letterId documentId]
| table _time lerr.errorCd lerr.errorDetails rlkp.stateCode rlkp.letterId lerr.documentId

[bowesmana]

The general way to "join" is to use stats by X and in your case you are searching two data sets, so you could do something like this

index=provisioning_index sourcetype=PCF:log source_type=APP/PROC/WEB message_type=OUT cf_org_name=org1 cf_app_name=APP1 (LOG_LEVEL="ERROR" service=service1 errorCd="DOC-MGMT*" OR (NOT letterId=null operation=generateInstantDocument)
| rex field=_raw "errorDetails=(?<errorDetails>.*?)\s*:" 
| fields _time errorCd errorDetails stateCode letterId documentId 
| stats values(*) as * by documentId 

and depending on what output you are trying to achieve, you can add in BY fields to split by. Not sure which data is coming from which data source as your fields statements are the same for both cases.

Not sure why you originally had the errorDetails field in the fields statement BEFORE the rex statement if you are extracting that fields in the rex.

[RamMur]

Thanks for the Reply, rex statement was supposed to be ahead like you mentioned. i also missed the table row in my query, basically querying the same data source. I have a common field documentId, using which trying to join 2 logs so i can get additional fields in my result from the right side query result. 

index=provisioning_index sourcetype=PCF:log source_type=APP/PROC/WEB message_type=OUT cf_org_name=org1 cf_app_name=app1 LOG_LEVEL="ERROR" service=service1 errorCd="DOC-MGMT*"
|rex field=_raw "errorDetails=(?<errorDetails>.*?)\s*:"
|fields _time errorCd errorDetails stateCode letterId documentId
|join left=lerr right=rlkp type=left where lerr.documentId = rlkp.documentId max=0
[search index=provisioning_index sourcetype=PCF:log source_type=APP/PROC/WEB message_type=OUT cf_org_name=org1 cf_app_name=app1 NOT letterId=null operation=generateInstantDocument
|fields _time errorCd errorDetails stateCode letterId documentId]
| table _time lerr.errorCd lerr.errorDetails rlkp.stateCode rlkp.letterId lerr.documentId

The result i'm seeing that that rlkp.letterId is only populated for few rows and not the whole set. And the volume its search is large/huge.

[bowesmana]

Have you tried the search I showed - does it give you something approximating what you are after?

[RamMur]

sorry, not really, the search on the right of the ioin, is a error log with documentId field but nothing else, the query on the right side of the join, is a search that will result in additioanl fiedls along with documentId, my attempt is to grab those additional fields , only when the documentId's match. i think query, gives everything, and doesnt give me results that has matching documentId's.