Help is required regarding Splunk joining

[sg86sourav]

Hi,

I am facing some issue with Splunk query while using joining. Our requirement is find out the high response time API call [more than 3000 ms] and their corresponding region name and to achieve this I need to join two query and I am not getting both the information from a single query.

First I search for transactions having more than 3000 ms response time using below query and found around 6600 transactions-

index=* duration>3000 | dedup txnId

And then executed below query with joining to fetch region details-

index=* duration>3000 | dedup txnId|table txnId,accountNumber,duration | join accountNumber type=inner [search index=* | table accountNumber,market]

The problem is that after joining it is returning only 452 row, not the 6600 observed in the first query.

So please could you please help me to resolve this issue, is there any mistake I have done ?

[kamlesh_vaghela]

@sg86sourav

I think you are getting a single event for each account. Can you please try this?

index= duration>3000 
| dedup txnId 
| table txnId,accountNumber,duration 
| join accountNumber type=inner max=0
    [ search index= 
    | table accountNumber,market]

I have added max=0.

https://docs.splunk.com/Documentation/Splunk/7.2.5/SearchReference/Join

[sg86sourav]

Hi Kamlesh, I have tried with max=0 parameter as well, but observing same result.

[kamlesh_vaghela]

@sg86sourav

Can you please try this?

 index= duration>3000 
 | dedup txnId 
 | table txnId,accountNumber,duration 
 | join accountNumber type=inner max=0
     [ search index=  | dedup accountNumber
     | table accountNumber,market]

And

Can you please share sample output from both searches. only 5 or 10 events from both searches.

[sg86sourav]

Hi Kamlesh, getting the same result after running the latest query you shared.

Regarding sample output, I don't have access to copy data from remote machine to local.

[kamlesh_vaghela]

@sg86sourav

Can you please try this?

index= duration>3000 
  | dedup txnId 
  | table txnId,accountNumber,duration 
  | join accountNumber type=left max=0
      [ search index=  | dedup accountNumber
      | table accountNumber,market]

I just want to know that do we have any accountNumber in results which are not available in the second search.