SPLK-1004 Using Subsearches

Using Subsearches Detailed Explanation

1. What is a Subsearch?

A subsearch is a search that runs inside square brackets [ ... ], and its results are passed into the main (outer) search.

The subsearch executes first, and then its results are used to influence or filter the main search.

Think of it like:

“Find this thing based on the results of another search.”

Basic syntax:

index=main field=[ search index=other | stats ... ]

2. Types of Subsearches

Subsearches can serve multiple purposes, mainly:

a) Inline Filtering Subsearches

These are used to dynamically filter the outer search based on a value retrieved from another search.

Example:

index=main user=[ search index=logins | head 1 | fields user ]

• The inner search (search index=logins | head 1 | fields user) runs first.

• It returns a user value, e.g., user="alice".

• The outer search becomes: index=main user="alice"

This is useful when you want to pass dynamic values into a search.

b) Join Subsearches

You can use subsearches with commands like join to combine data from multiple sources.

Example:

... | join user [ search index=lookup_data | fields user, location ]

• The subsearch finds user and location from lookup_data.

• These are joined with the outer events by matching on user.

Note: join uses inner join by default and has size and performance limitations, especially on large datasets.

3. Limitations of Subsearches

Subsearches are powerful but have strict system-imposed limits to avoid performance degradation.

Default Limits:

Limit	Value
Maximum results	10,000
Maximum runtime	60 seconds
Max subsearch depth	2 nested levels
Output size	1 MB (approximate)

If your subsearch exceeds any of these, you will get a warning or error:

Subsearch produced too many results

This can happen easily when working with large indexes or unconstrained subsearches.

Performance Concerns

• Subsearches execute before the main search begins.

• They must return small, focused datasets to avoid delays.

• Each result is wrapped in parentheses and ORed together:

  • user=(alice OR bob OR charlie)

• Large subsearches can significantly slow down your searches.

4. Alternatives to Subsearches

When subsearches become a bottleneck, or if they exceed limits, consider these alternatives:

a) lookup / inputlookup

If your subsearch is retrieving data from a static source (like a user list), use a lookup table instead.

Example:

... | lookup user_lookup user OUTPUT location

• Much more efficient than repeatedly querying another index.

• Can scale better and is easier to maintain.

b) append

Use append when you need to run multiple searches side by side, not to filter one based on the other.

Example:

search index=main
| append [ search index=secondary ]

• This returns results from both searches.

• Use source or index fields to distinguish the sources.

c) tstats

If the subsearch is querying accelerated data models, use tstats for better performance.

Example:

| tstats count from datamodel=Web where (status=404 OR status=500) by user

tstats is far more efficient than using stats or join on raw data.

Summary Table: Subsearches Overview

Aspect	Detail
Purpose	Embed one search inside another
Format	Square brackets: [ search ... ]
Common Uses	Inline filtering, joining lookup data
Limitations	10,000 results, 60 sec runtime, 1 MB result size
Risks	Can be slow, may exceed limits if not constrained
Alternatives	lookup, inputlookup, append, tstats

Using Subsearches (Additional Content)

1. Accurate Definition of Subsearches

A subsearch in Splunk is an inner search that is executed before the outer (main) search. Its results are inserted into the outer search as a literal string, typically forming field=value filters or expressions.

Mechanism:

• Executed first

• Output formatted into a string (e.g., field=(value1 OR value2 OR value3))

• Injected into the outer search

Example:

index=main user=[ search index=logins | top user | fields user ]

If the subsearch returns alice, bob, and carol, this is transformed into:

index=main user=(alice OR bob OR carol)

This makes subsearches a powerful way to inject dynamic filters, but it can trigger limitations when the result set is too large.

2. Transformative Subsearches

In addition to filtering, transformative subsearches are used to supply a value into eval, where, or other commands.

Use Case: Inject top user into an eval expression

index=web 
| eval top_user=[ search index=web | top user | head 1 | fields user ]

• If the subsearch returns alice, the expression becomes:

  eval top_user="alice"
  

This pattern is useful when:

• You need to assign dynamic values from another search

• You want to build conditional logic around a top-performing entity

3. Subsearch OR Expression Behavior

When the subsearch returns multiple values, Splunk wraps them in an OR condition:

Returned values:

user
alice
bob
carol

Converted outer search:

user=(alice OR bob OR carol)

This behavior is automatic and convenient, but introduces risks:

• Limitations:

  • Max 10,000 rows

  • Max runtime: 60 seconds

  • Max generated SPL: ~1MB

Exceeding these limits results in warnings or errors like:

Subsearch produced too many results

4. Join Type: outer – A Controlled Alternative

For cases where not all keys match, using an outer join helps retain all base data while enriching it with subsearch fields.

Example:

index=main 
| join type=outer user [ search index=lookup_data | fields user, location ]

Behavior:

• Returns all events from index=main

• Merges location when a match is found

• Unmatched rows from the outer search are still retained

Tips:

• Always limit subsearch size using fields, dedup, head, or top

• Outer joins are useful for enrichment, not heavy aggregation

• Avoid on large datasets unless necessary

5. Replacing Expensive Subsearches with Summary Indexing

If your subsearch logic is complex and frequently used (e.g., in dashboards or alerts), consider caching the results using summary indexing.

Create a Summary Index:

index=web 
| stats count by user 
| collect index=summary_users sourcetype=summary_data

Query the Summary Later:

index=summary_users sourcetype=summary_data

Benefits:

• Offloads repeated computation

• Improves dashboard load speed

• Reduces subsearch limits risk

This approach is ideal for:

• Scheduled reports

• Dashboard panel data reuse

• Long-term aggregations (weekly/monthly rollups)

Conclusion: When and How to Use Subsearches

Scenario	Recommended Approach
Dynamic filtering	Basic subsearch (field=[search...])
Dynamic value assignment	Transformative subsearch with eval
Complex joins with fallback	join type=outer with subsearch
Large repeat aggregations	Summary indexing with collect
Frequent cross-index filtering	Use lookup tables or saved reports