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
SplunkTrust
SplunkTrust

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.

 

View solution in original post

0 Karma