topic Re: Find Events in one index based on a result set in Splunk Search

Find Events in one index based on a result set

SMM10 — Wed, 10 Nov 2021 17:54:59 GMT

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?

Re: Find Events in one index based on a result set

jcraumer — Wed, 10 Nov 2021 18:52:51 GMT

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

Re: Find Events in one index based on a result set

isoutamo — Wed, 10 Nov 2021 19:45:13 GMT

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

Re: Find Events in one index based on a result set

richgalloway — Wed, 10 Nov 2021 19:48:42 GMT

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.