topic Re: Splunk optimize search query with join in Splunk Search

How to optimize search query with join?

Splunk4 — Tue, 13 Jun 2023 10:11:02 GMT

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

Re: Splunk optimize search query with join

gcusello — Fri, 09 Jun 2023 09:40:53 GMT

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

Re: Splunk optimize search query with join

Splunk4 — Mon, 12 Jun 2023 04:29:43 GMT

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.

Re: Splunk optimize search query with join

gcusello — Mon, 12 Jun 2023 05:45:50 GMT

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

Re: Splunk optimize search query with join

Splunk4 — Tue, 13 Jun 2023 04:53:33 GMT

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

Re: Splunk optimize search query with join

gcusello — Tue, 13 Jun 2023 06:30:47 GMT

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