How to optimize search query with join?

[Splunk4]

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

[gcusello]

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

[Splunk4]

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.

[gcusello]

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

[Splunk4]

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

[gcusello]

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