Is there any way to simplify this query

[apusuluri]

If I execute the below query for selected time  like 20 hours  its taking longer time and calling events are 2,72,000 .How to simplify this query for getting the result in 15 to 20 seconds.

index=asvservices authenticateByRedirectFinish
(*)
| join request_correlation_id
[
search
index= asvservices stepup_validate ("isMatchFound\\\":true")
| spath "policy_metadata_policy_name" | search "policy_metadata_policy_name" = stepup_validate
| fields "request_correlation_id"
]
| spath "metadata_endpoint_service_name"
| spath "protocol_response_detail"
| search "metadata_endpoint_service_name"=authenticateByRedirectFinish
| rename "protocol_response_detail" as response

[apusuluri]

By using join in the query impacting the performance.What is the alternative way with out using join in the above query

[richgalloway]

Aside from the obvious (replace the join command), what do you mean by "optimize"?  What is the problem and what are the goals?  What does the data look like and what should the results be?  How many events are being processed?

The join command appears to add no value.  It matches (only) the request_correlation_id field from the subsearch to the same field from the main search.  No other fields from the subsearch are included so why bother with the join?

[apusuluri]

By using join in the query impacting the performance.What is the alternative way with out using join in the above query

[richgalloway]

I asked five questions to get more information to better help and you chose to answer none.  We're not off to a good start. 😀

The join command appears to do nothing.  The alternative to such a command is to remove it.

[apusuluri]

If I run the query for 20 hours time frame taking longer time and calling events are 2,72,000 .I need the search results in lesser time like 20 seconds.How to simplify the query for getting the result in 15 to 20 seconds.

[richgalloway]

Can you put a number on "taking a longer time"?  How much longer than 15-20 seconds?  Again I ask, how many events are being processed?  Millions of events will take a long time to process no matter how efficient the search is.  How many indexers are searching this data?  The more indexers that participate in the search (assuming the events are evenly distributed among them), the faster the search will be.

Adding a sourcetype to the base search may help.  It may also help to add a fields command immediately after the base search.  That may reduce the number of fields being transported. resulting in a faster search.  Place the search after the first spath to help reduce the number of events the second spath needs to process.

index=asvservices sourcetype=foo "authenticateByRedirectFinish"
| fields metadata_endpoint_service_name protocol_response_detail
| spath "metadata_endpoint_service_name"
| search "metadata_endpoint_service_name"=authenticateByRedirectFinish
| spath "protocol_response_detail"
| rename "protocol_response_detail" as response

[apusuluri]

Thank you for sharing the details.Your prompt response is greatly appreciated.
how many events are being processed:  124,878 events i
Duration: 184.767 seconds
 How many indexers are searching this data: One index(asvservices)
Please help me on improving the performance and duration time should be 15 to 20 seconds

Query:

index=asvservices authenticateByRedirectFinish (*) | join request_correlation_id [ search index= asvservices stepup_validate ("isMatchFound\\\":true") | spath "policy_metadata_policy_name" | search "policy_metadata_policy_name" = stepup_validate | fields "request_correlation_id" ] | spath "metadata_endpoint_service_name" | spath "protocol_response_detail" | search "metadata_endpoint_service_name"=authenticateByRedirectFinish | rename "protocol_response_detail" as response

[richgalloway]

124,878 events is not a lot for Splunk to process.  It should not take 3 minutes to search them.  What other activity is happening on the system?

I understand one index is being searched, but how many indexers are conducting that search?  More is better.  Do the indexers meet the minimum hardware specifications?  Are the disks fast enough?

Have you tried the query without the join command?