Splunk search string (without using JOIN)

[Nicholas_Key]

How does the 'optimized' splunk search string (without using JOIN) looks like for the following search string?

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

[gkanapathy · Accepted solution]

sourcetype=table_name1 [ search sourcetype=table_name2 | return 10000 column_name ] | fields column_name(s)

which works very well if there are fewer than 10000 distinct column_name values in table_name2, or generally when table_name2 is quite a bit smaller than table_name1.

Depending on the sizes of table_name1, table_name1, and the resulting join, there may be better optimizations, but the above is rather common.