topic Subsearch like correlated subquery in SQL in Splunk Search

Subsearch like correlated subquery in SQL

Yancy — Sat, 30 Jan 2010 03:58:16 GMT

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?

Re: Subsearch like correlated subquery in SQL

hulahoop — Sat, 30 Jan 2010 05:15:29 GMT

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.

Re: Subsearch like correlated subquery in SQL

gkanapathy — Mon, 01 Feb 2010 16:14:49 GMT

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.

Re: Subsearch like correlated subquery in SQL

Yancy — Wed, 14 Apr 2010 02:43:11 GMT

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