Splunk Search

Left join not returning values as expected.

yuvrajsharma_13
Explorer

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

 

Labels (2)
0 Karma
1 Solution

tscroggins
Influencer

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 ]

View solution in original post

tscroggins
Influencer

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
Explorer
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...