Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท Search Processing Language

Splunk Subsearches, append, join, appendcols and transaction

Combine data in SPL: subsearches in square brackets, join, append, appendcols, multisearch, union, transaction and the faster stats-based alternatives for correlating events.

Real questions often need more than one search: "Which users who failed to log in also made a purchase?" or "How long did each session last?" SPL offers several ways to combine results. This lesson explains each one and, importantly, when to prefer the faster alternative.

Subsearches

A subsearch is a search in square brackets whose results feed the outer search. It runs first.

SPL
index=web [ search index=auth action=failure | top limit=5 clientip | fields clientip ]

The subsearch returns the five IPs with the most failures. Splunk turns the result into an OR expression: (clientip="198.51.100.9") OR (clientip="203.0.113.7") ... and the outer search finds all web events from those IPs.

Rules and limits you must know:

  • The subsearch must return the field names you want to search on (use fields or table at the end).
  • By default a subsearch returns at most 10,000 results and must finish in about 60 seconds; larger results are silently truncated, which gives wrong answers without an error. (The limits are in limits.conf.)
  • Rename fields in the subsearch to match the outer field: | rename src AS clientip.
  • To search a field that goes by a different name, return a field named search or query.
Python Stand-in: a subsearch feeding an outer search

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

join

join is like a SQL join: it matches results of the main search to results of a second search on shared fields.

SPL
index=orders
| join type=left order_id [ search index=payments | fields order_id, payment_status ]

type=inner (default) keeps only matches; type=left keeps all rows from the main search. It is easy to write and slow, because the right-hand side is a subsearch with the same limits (50,000 rows and a time limit by default) and joins run on the search head. Use it for small lookups only.

append, appendcols, multisearch, union

Command Effect
append [ search ... ] Adds the subsearch rows below the current results
appendcols [ search ... ] Adds subsearch columns beside the rows, matched by position (not by key)
multisearch [search ...] [search ...] Runs streaming searches in parallel and merges events
union Combines datasets; can run streaming parts in parallel
SPL
index=web | stats count AS web_requests
| append [ search index=api | stats count AS api_requests ]

The faster way: stats on combined searches

Instead of join, search both sources at once and let stats correlate by a shared field. It scales because it is a single pass:

SPL
(index=orders) OR (index=payments)
| stats values(customer) AS customer, values(payment_status) AS payment_status, count by order_id

Or the classic "last event wins" pattern:

SPL
(index=a) OR (index=b)
| eval id = coalesce(order_id, orderId)
| stats first(status_a) AS status_a, first(status_b) AS status_b by id
Python Stand-in: stats by key instead of join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

transaction

transaction groups related events into one transaction event with duration and eventcount fields.

SPL
index=web | transaction clientip maxspan=30m maxpause=5m
index=app  | transaction session_id startswith="login" endswith="logout"
  • maxspan: maximum length of a group; maxpause: maximum gap between consecutive events.
  • startswith and endswith define the first and last event.
  • maxevents limits group size.

transaction is convenient but memory-hungry and slow. For most needs, group with stats:

SPL
index=web
| stats earliest(_time) AS start, latest(_time) AS end, count AS events by session_id
| eval duration = end - start

Use transaction only when you need the combined raw text or start/end text conditions.

Python Stand-in: sessions by time gap (transaction maxpause)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choosing the right tool

Need Use
Filter by a list computed by another search Subsearch
Enrich with a small table lookup (see lookups)
Correlate big sources on a key stats over both sources
Stack results append
Session from time gaps stats with streamstats, or transaction
SQL-like left join, small data join

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens when a subsearch returns more than its result limit?

    Truncation without a clear error can give wrong answers, which is why large joins should use stats.
  2. 2. Which approach scales best for correlating two large sources on a shared key?

    A single pass with stats avoids subsearch limits.
  3. 3. What does appendcols match on?

    appendcols places subsearch columns beside rows by position, not by key.
  4. 4. What does maxpause control in transaction?

    maxspan limits total length; maxpause limits the gap between events.

Summary

  • Subsearches in [ ] feed filters to an outer search but are capped at about 10,000 results.
  • join, append, appendcols combine datasets; use them for small data.
  • Prefer one search over both sources with stats ... by key.
  • transaction builds sessions; stats with earliest/latest is usually faster.

Next: Fields and metadata