Splunk Search

How to optimize search query with join?

Splunk4
Explorer

Hi All,

i am using below query to get the common results on the basis of correlation_id but it is very slow,I need to optimize it to get the proper results

 

 

index=kong_fincrimekyc_prod
|rename request.headers.x-int-clientapplication as "client" correlation-id as "correlation_id"
|table Error_Reason,correlation_id,client,upstream_uri|where isnotnull(client)
|join type=outer correlation_id [ search index=fincrimekyc_prod source="prod-ms-vix-adapter" sourcetype=kyc_app_logs "com.nab.ms.vix.adapter.exception.VixExceptionHandler" "ERROR" OR "Exception"
| rex "Caused by(?<Error_Reason>.*?)\\\n"
| rex "correlation_id=\\\"(?<correlation_id>.*?)\\\"\,"|table Error_Reason,correlation_id]
|stats values(Error_Reason) as "Error_Reason" values(client) values(correlation_id) by upstream_uri|where isnotnull(Error_Reason)

 

 

Please help on this

Thanks

Labels (3)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Splunk4,

Splunk isn't a database and al the people (me too in the beginning) that arrive from the traditional IT start using the join command:
join command must be used only if there isn't any other solution and with searches with few results for performces reasons and because there's the limit of 50,000 results.

Anyway, you could use the stats command to join the two searches using this approach:

(index=kong_fincrimekyc_prod) OR (index=fincrimekyc_prod source="prod-ms-vix-adapter" sourcetype=kyc_app_logs "com.nab.ms.vix.adapter.exception.VixExceptionHandler" "ERROR" OR "Exception")
| rename request.headers.x-int-clientapplication as "client" correlation-id as "correlation_id"
| rex "Caused by(?<Error_Reason>.*?)\\\n"
| rex "correlation_id=\\\"(?<correlation_id>.*?)\\\"\,"
| stats 
   values(Error_Reason) AS "Error_Reason" 
   values(client) AS client
   values(upstream_uri) AS upstream_uri
   BY correlation_id
| stats 
   values(Error_Reason) AS "Error_Reason" 
   values(client) AS client 
   values(correlation_id) AS correlation_id
   BY upstream_uri
| where isnotnull(Error_Reason)

In few words you use the by clouse of stats command to correlate two searches in the main search.

Obviously, I cannot test this search, see my approach and adapt it to your case.

Ciao.

Giuseppe

0 Karma

Splunk4
Explorer

i tried the above query but it is not returning any results.Looks like it is not renaming and extracting the fields the fields due to which the last condition is matching for error reason null.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Splunk4,

as I said, this is am approach, you have to adapt this approach to your data, probably the problem is in the regex to extract correlation_id.

Anyway, debug this search deleting a row from the end to understand where is the issue.

Ciao 

Giuseppe

0 Karma

Splunk4
Explorer

When i am running the search only with one index then the field is getting extracted but it is not getting extracted when running by including the both.Not sure what can be the reason

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Splunk4,

as I said, to debug your search you can have two approach: starting by the beginning or by the end.

Starting from the end, you have to remove, one by one, a complete row understanding if there's some blocking command and/or analyzing if there are all the fields, e.g., in your case,

at first delete the last where command to see if it causes the null result,

then you have two following stats command, check if the key of the second is present on the first and what are the values.

Starting from the beginning run the main search (with both the indexes and see if you have all the fields to use in the following commands, especially the BY clauses in stats commands.

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...