topic Splunk Join Optimisation in Splunk Search

Splunk Join Optimisation

deepuhassan — Thu, 28 Jan 2021 11:43:42 GMT

Hi,

I have the below query which does the search on two different sources in the same index and join the results based app correlation id to get results and perform the stats operation. However, the source files are huge and hence the join is taking too longs to get me the results.

index=server sourcetype=perfromance source="*performance.log" component_role=consumer

| join app_id [ search index=server sourcetype=component source="*component.log" | rename appCorId as app_id ]

| stats count(eval=(process_result="COMPLETED")) as Completed count(eval=(process_result="FAILED")) as Failed

This is a simple join but taking huge time when do a search for 24 hours.

Please help optimize this query.

Thanks,

Sandeep

Re: Splunk Join Optimisation

to4kawa — Thu, 28 Jan 2021 12:17:06 GMT

index=server (sourcetype=perfromance source="*performance.log" component_role=consumer ) OR (sourcetype=component source="*component.log") | eval app_id=coalesce(appCorId,app_id) | stats count(eval=(process_result="COMPLETED")) as Completed count(eval(process_result="FAILED")) as Failed dc(source) as flag by app_id | where flag > 1 | stats sum(Completed) as Completed sum(Failed) as Failed

Re: Splunk Join Optimisation

deepuhassan — Thu, 28 Jan 2021 23:23:53 GMT

Thanks.. It is partially working.

However, would you please explain how the steps you have taken to form the query?

What shall I do if the field name used for correlation is same in both the sources?

Re: Splunk Join Optimisation

deepuhassan — Thu, 28 Jan 2021 23:39:06 GMT

I have tried using the technique you have given me for the below query but it is not working. The below query will be used to calculate the time difference of an event logged in 2 different sources which has common correlation id. Since I am using join it is taking longer time.

Basically I am trying to find out the end to end time involved for a message which started from Kafka producer and processed by a consumer.

index=server sourcetype="performance" source="*/performance.log" perf_component_role=kafkaProducer component_name="test-api-v1" event_status=COMPLETE | eval publishedtime=_time
| join app_correlation_id [search index=server component_name="test-api-v2" environment=sit1 "Process updated Successfully !!" | eval processedtime=_time]
| eval endToEndTime = (processedtime -publishedtime) | table app_correlation_id publishedtime processedtime endToEndTime

Re: Splunk Join Optimisation

to4kawa — Fri, 29 Jan 2021 02:28:22 GMT

However, would you please explain how the steps you have taken to form the query?

->https://conf.splunk.com/files/2019/slides/FNC2751.pdf

Please see here.

What shall I do if the field name used for correlation is same in both the sources?

-> remove eval with coalesce

Re: Splunk Join Optimisation

deepuhassan — Fri, 29 Jan 2021 03:30:55 GMT

Thanks for the document.

I am not able to optimize the below query :

Re: Splunk Join Optimisation

to4kawa — Fri, 29 Jan 2021 03:50:12 GMT

stats range() by app_correlation_id is useful. you can do it.