Left join not returning values as expected.

[yuvrajsharma_13]

I am joining two splunk query to capture the  values which is not present in subquery. 

Trying to find the account which opend today but not posted. But quary not retuning any values.

Let me know if we have other way to get the values ?

Query 1 :  Returns Account opened today. 

index=a  "digital account opened" | rename msg.requestID AccountID | table AccountID

Query 2 : Account posted today.

index=b "/api/posted" 200  | rex "GET /api/posted (?<accountID>\d+) HTTP 1.1" table AccountID

Final Query : 

index=a  "digital account opened" | rename msg.requestID AccountID | table AccountID

 | join type=left  AccountIDOpened [ search index=b "/api/posted" 200  | rex "GET /api/posted (?<accountID>\d+) HTTP 1.1" table AccountID ] | search AccountIDOpened =null | table _time,AccountIDOpened

[tscroggins · Accepted solution]

Hi @yuvrajsharma_13,

You're attempting to join both searches by a field named AccountIDOpened, which neither search includes.

Are you trying to return all results in the outer/left search that are not present in the inner/right search or vice versa?

Based on your description, you can find accounts that were opened but not posted by searching for opened account and excluding accounts that were posted using a subsearch:

index=a "digital account opened" NOT 
    [ search index=b "/api/posted" 200 
    | rex "GET /api/posted (?<requestID>\d+) HTTP 1.1" 
    | rename AccountID as msg.requestID
    | table msg.requestID ]

[yuvrajsharma_13]

@gcusello