Splunk Search

Subsearch all events in another index


I have two indexes

Index accounts: [user. payroll]

Index employees: [user, emp_details, emp_information]


I am trying to use a search to search all the 1 million users in index users to search for the corresponding details of the same user in different index which contains 20 million records.

I tried something like

index=accounts user=*

| join type=left user [search index=employees | fields user,  emp_details,  emp_information]
| table user, emp_details, emp_information


But its not search all the users and joining all the users. 


Labels (3)
0 Karma


You can't reliably use subsearches as the volumes are too big and you can only join on 50,000 rows.

As @aromanauskas suggests, use a index 1 OR index2 approach and combine the two data sets using stats ... by common field.


0 Karma

Path Finder

So the dataset here is important so not sure which of these you're really looking for. 

If you just want to search all the data and combine it by user.

(index=accounts user=*) OR (index=employees  user=*  emp_details=*  emp_information=*)
| stats latest(emp_details) AS emp_details latest(emp_information) AS emp_information values(index) AS indexes by user 

But that assumes you want to combine things regardless of if user exists in 1 or 2 indexes.  

If you just want to do a search for users in one index ONLY if they exist in the other index.. 

index=employees emp_information=* emp_details [ | search index=accounts user=* | stats count by user | fields user | format ] | table user, emp_details, emp_information 

The problem here though is you don't get a notification if the user existed in accounts but NOT in employees. 

If you're looking for something else please describe it. 

0 Karma
Get Updates on the Splunk Community!

Observability | How to Think About Instrumentation Overhead (White Paper)

Novice observability practitioners are often overly obsessed with performance. They might approach ...

Cloud Platform | Get Resiliency in the Cloud Event (Register Now!)

IDC Report: Enterprises Gain Higher Efficiency and Resiliency With Migration to Cloud  Today many enterprises ...

The Great Resilience Quest: 10th Leaderboard Update

The tenth leaderboard update (11.23-12.05) for The Great Resilience Quest is out >> As our brave ...