Splunk Search

Find Events in one index based on a result set

SMM10
Explorer

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? 

Labels (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

 

---
If this reply helps you, an upvote would be appreciated.
0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

0 Karma

jcraumer
Explorer

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

 

 

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>