Reporting

speeding up Multiple joins in a single search

seanlenes
Explorer

Hello,

I have a very involved query involving 4 joins and I am looking for a way to speed it up. The purpose of this is for a dashboard that cleanly presents the needed data in a single table instead of 5 separate panels (as per the requirements given to me). One of the problems I am running into is that some of the queries have different indexes, so I need to have the multiple searches for them, and appendcols doesn't seem to work since the only real thing in common between them is that 3 share the same index so there isn't one clean base search to use.  I'm not sure I could post the query here due to regulations, So I will try to be as specific as possible.

How smart is splunk when it comes to queries? 3 of the queries I have the same index, so could I do something like the below:

index=xyz (A and B and C) OR (D and E and F)  OR (G and H and I)
| stats based on (A and B and C)
| stats based on (D and E and F)
| stats based on (G and H and I)

 

If you have any other tips or resources on speeding up joined queries that could help, that would be great as well

Labels (1)
Tags (1)
0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

With your method, you will still need to search 3 times on the same index.

Look at the subseaches documentation.

https://docs.splunk.com/Documentation/Splunk/8.0.5/Search/Aboutsubsearches

You could use a series of sub searches, with append. but this may be slow, as they are serialized.
Look at the command "union" that allows for parallel sub searches.

 

View solution in original post

0 Karma

to4kawa
Ultra Champion

sample:

index=_internal 
| eval tmp = case(group IN ("tpool","thruput","tailingprocessor"),"first"
    ,group IN ("subtask_seconds","spacemgr","searchscheduler"),"second"
    ,group IN ("search_pool","search_health_metrics","search_concurrency"),"third"
    ,true(),"others") 
| stats count by tmp

I am not sure why you want to do join. basically, eval is enough for that.

yannK
Splunk Employee
Splunk Employee

With your method, you will still need to search 3 times on the same index.

Look at the subseaches documentation.

https://docs.splunk.com/Documentation/Splunk/8.0.5/Search/Aboutsubsearches

You could use a series of sub searches, with append. but this may be slow, as they are serialized.
Look at the command "union" that allows for parallel sub searches.

 

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...