topic Left join not returning values as expected. in Splunk Search

Left join not returning values as expected.

yuvrajsharma_13 — Sat, 27 Jan 2024 23:40:59 GMT

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

Re: Left join not returning values as expected.

yuvrajsharma_13 — Sat, 27 Jan 2024 23:42:19 GMT

@gcusello

Re: Left join not returning values as expected.

tscroggins — Sun, 28 Jan 2024 00:58:09 GMT

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 ]