speeding up Multiple joins in a single search

[seanlenes]

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

[to4kawa]

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 · Accepted solution]

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.