Find Events in one index based on a result set

[SMM10]

I want to find items in one index based on results from another index's search. I have the following but only get a handful of results for some reason.

index=a sourcetype=test
|join id [search index b | rename id as idb]
|stats count by id, idb

Is this the best way to accomplish it and any reason I only get a small number of results? 

[richgalloway]

Join is one way to do it, but can be inefficient.  If you use join, understand that Splunk will match events based only on the specified field name(s) (or all common fields if none are specified).

Another way to combine results is using append.

index=a sourcetype=test
| append [search index b | rename id as idb]
|stats count by id, idb

Yet another way, and probably the best way if I understand the use case correctly, is to use a subsearch.  A subsearch runs before the main search and its results become part of the main search.

index=a sourcetype=test [search index b | fields id | format ]
|stats count by id

The format command converts the subsearch results into a boolean expression the main search can evaluate.  It's important for the subsearch to return field names that exist in the main search otherwise it may fail to find the right results.

[isoutamo]

Hi

quite often you should avoid join to join datasets. Here is excellent presentation why https://conf.splunk.com/files/2020/slides/TRU1761C.pdf

There are some other presentations too which are worth of look.

r. Ismo

[jcraumer]

The join command works much like it would in SQL. 

If Index A has the field idb, it will display the events where idb matches found in both Index A and B. 

The query as you stated will only provide events where the value is found in both Indexes.

If you wanted all events from A and any events from B that match on idb you can add the type:

index=a sourcetype=test
|join type=left id [search index b | rename id as idb]
|stats count by id, idb