topic Re: Help is required regarding Splunk joining in Splunk Search

Help is required regarding Splunk joining

sg86sourav — Mon, 01 Apr 2019 07:18:46 GMT

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 ?

Re: Help is required regarding Splunk joining

kamlesh_vaghela — Mon, 01 Apr 2019 08:27:38 GMT

@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

Re: Help is required regarding Splunk joining

sg86sourav — Mon, 01 Apr 2019 08:37:43 GMT

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

Re: Help is required regarding Splunk joining

kamlesh_vaghela — Mon, 01 Apr 2019 08:43:06 GMT

@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.

Re: Help is required regarding Splunk joining

sg86sourav — Mon, 01 Apr 2019 11:50:55 GMT

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.

Re: Help is required regarding Splunk joining

kamlesh_vaghela — Mon, 01 Apr 2019 12:21:08 GMT

@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.