How to do a subsearch in Splunk?

When we debug an application, we may need to do some data aggregation to know what happened. So, like in SQL, we can do some sub-searches in Splunk to quickly retrieve a lot of information.

Simple search

First, we will check how to do a simple search and how the data is retrieved.

For what happened next, we will use the following example :

an api that always log the transaction id [transaction_id] and a generic error code [error_code] (if the transaction was incorrect) before to answer to the user

a log with the transaction id [transaction_id] and with the exception content [exception] if a field was missing

So here, with simple searches, we can search which transactions failed

error_code=* | table transaction_id

transaction_id
1
2
3

or search an error log

transaction_id="1" AND exception=* | table timestamp, transaction_id, exception

timestamp	transaction_id	exception
2021-01-01 00:00:00.000	1	Missing field

Subsearch

Now that we see what we can do with simple searches, we will be able to combine them to retrieve all the transaction_id with an exception!

So how do we do a subsearch?

In your Splunk search, you just have to add

[ search [subsearch content] ]

example

[ search transaction_id="1" ]

So in our example, the search that we need is

[search error_code=* | table transaction_id ] AND exception=* | table timestamp, transaction_id, exception

And we will have

timestamp	transaction_id	exception
2021-01-01 00:00:00.000	1	Missing field
2021-01-03 00:00:00.000	3	Auth failed

The transaction_id 2 is missing because it wasn't a transaction with an error.

But how does it works?

It's quite simple! In my example, I did a simple search that returns only one information per log.

error_code=* | table transaction_id

transaction_id
1
2
3

So when you are doing this kind of search as a subsearch, Splunk transforms it to OR condition for each line.

[search error_code=* | table transaction_id ] AND exception=*

becomes

(transaction_id = "1" OR transaction_id = "2" OR transaction_id = "3") AND exception=*

And if you are retrieving more than one info in your subsearch, Splunk will transform it as an if condition, where each tuple is a matching case. (The condition to be valid is to match all values from the same line.)

[search error_code=* | table transaction_id, timestamp ] AND exception=*

becomes

(
  (transaction_id = "1" AND timestamp = "2021-01-01 00:00:00.000") OR  
  (transaction_id = "2" AND timestamp = "2021-01-02 00:00:00.000") OR
  (transaction_id = "3" AND timestamp = "2021-01-03 00:00:00.000") 
) AND exception=*