CODEWITHSUNDEEP
splunksplunk-query
saurabh choudhary
saurabh choudhary

Reputation: 93

how do i pass a result from one search into IN clause of another search in splunk?

i run a query and get list of custId in form of table. how do i pass this result into another search query inside IN clause.

eg:

search 1: index=* "successful login for"|table custID this gives me table with column custID.

Then i have to run

index=* "mail sent by"|where custID IN (search 1) |table CustID,_time

Upvotes: 8

Views: 16613

Answers (3)

Brent Bradburn
Brent Bradburn

Reputation: 54989

By its nature, Splunk search can return multiple items. Generally, this takes the form of a list of events or a table. Subsearch is no different -- it may return multiple results, of course.

Subsearch output is converted to a query term that is used directly to constrain your search (via format):

This command is used implicitly by subsearches. This command takes the results of a subsearch, formats the results into a single result and places that result into a new field called search.

The above quote from the documentation is a cryptic way of saying that the subsearch results are converted into a query expression to match any of the subsearch outputs.

As such, the results of subsearch are effectively the same as applying the IN operator -- each subsearch result will be a matched value. So you don't need to use IN to achieve the intended results.

Example: The OP's use-case

index=* "mail sent by"|where custID IN (search 1) |table CustID,_time

would be written as:

index=* "mail sent by" [search {search 1}] |table CustID,_time

Since (search 1) produces a table with column custID, the subsearch needs no additional massaging to achieve the specified result. The subsearch will be replaced with something like:

( (custID="result1") OR (custID="result") OR ...)

Example: Selecting items matching the max value

[search index=main |stats max(batch) as batch]

The subsearch returns a table with a single column and a single item. For the outer query to work as intended, it is important to rename the column (using as) from 'max(batch)' to 'batch' so that it will match against the 'batch' field in the original data.

The resulting query expression will look like:

( batch="{max(batch)}" )

Upvotes: 0

drizin
drizin

Reputation: 1975

index=myindex <mainSearchConditions>
custID IN (
   [search index=myindex <subsearchConditions> | table custID | dedup custID 
   | stats values(eval("\"".custID."\"")) as search delim="," | nomv search]
)

Upvotes: 5

RichG
RichG

Reputation: 9936

Use a subsearch. You'll have to experiment with format options to get the output to be compatible with IN.

index=* "mail sent by"|where custID IN ([search index=* "successful login for"|fields custID | format]) |table CustID,_time

If you can't get the format output right, you may have to use the old method without IN.

index=* "mail sent by"|where [search index=* "successful login for"|fields custID | format] |table CustID,_time

BTW, index=* is not a good practice for Production. Use the real index name for better performance.

Upvotes: 5

Related Questions