Splunk Search

Subsearch like correlated subquery in SQL

Path Finder

Is it possible with subsearch to pass a list of search results to the outside search? similar to a SQL correlated subquery?

Background: I have an event that lists an ID and a ReferenceID. The ReferenceID will be a previous ID. Often however, there has been several days, if not weeks between the original ID and the ReferenceID. This makes a Transaction search ineffective for this query.

What I'd like to do, is to obtain a list of ReferenceIDs that match some criteria (quick) and then pass each of those into an outer search, so the outer search runs for each of the results returned from the inner search (not the combined results of the inner search). Is this possible?

Tags (1)
2 Solutions

Splunk Employee
Splunk Employee

Hi Yancy,

This is possible. Something to note about subsearches is the format of what is passed from the inner search to the outer search is important. If you are looking to pass a list of ReferenceIDs, then use the fields command at the end of your inner search. Otherwise, Splunk will by default pass the events themselves to the outer search.

In pseudo search, your use case will look something like this:

... <my outer search> [search ReferenceID=abc | fields + ReferenceID]

If you post some sample data, we can help you construct the specific query.

View solution in original post

Splunk Employee
Splunk Employee

It is possible to run an outer search the way you want with the map command, one time over each inner search result. However, this is extremely inefficient in Splunk, and it is likely that there is a much better way to do it using a subsearch as described by hulahoop. The difference is that you'll have to execute n+1 searches (where N is the number of inner search results) instead of 2 searches. To a first approximation, this will take (n+1)/2 times as long.

View solution in original post

Path Finder

Splunk For SQL Users is also a good resource for this type of question.

Splunk Employee
Splunk Employee

It is possible to run an outer search the way you want with the map command, one time over each inner search result. However, this is extremely inefficient in Splunk, and it is likely that there is a much better way to do it using a subsearch as described by hulahoop. The difference is that you'll have to execute n+1 searches (where N is the number of inner search results) instead of 2 searches. To a first approximation, this will take (n+1)/2 times as long.

View solution in original post

Splunk Employee
Splunk Employee

Hi Yancy,

This is possible. Something to note about subsearches is the format of what is passed from the inner search to the outer search is important. If you are looking to pass a list of ReferenceIDs, then use the fields command at the end of your inner search. Otherwise, Splunk will by default pass the events themselves to the outer search.

In pseudo search, your use case will look something like this:

... <my outer search> [search ReferenceID=abc | fields + ReferenceID]

If you post some sample data, we can help you construct the specific query.

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!