Splunk Search
Highlighted

Equivalent of SQL WHERE IN Subquery clause?

Explorer

Hello,

I am looking for the equivalent of performing SQL like such:

SELECT transactionid, vendor
FROM orders
WHERE transaction
id IN (SELECT transaction_id FROM events).

I am aware this a way to do this through a lookup, but I don't think it would be a good use case in this situation because there are constantly new transaction_id's generated and several thousand of them within a small timeframe, as well as my goal to create a timechart report.

As of right now I can construct a list of transactionids for orders in one search query and a list of transactionids for events in another search query, but my ultimate goal is to return order logs that have transactionids shared with the transactionids of the events log. Any help is greatly appreciated, thanks!

0 Karma
Highlighted

Re: Equivalent of SQL WHERE IN Subquery clause?

Path Finder

have you considered a join instead of a subquery it might be easier

0 Karma
Highlighted

Re: Equivalent of SQL WHERE IN Subquery clause?

Explorer

@kiamco can you give an example of how you would join two searches by a single field?

0 Karma
Highlighted

Re: Equivalent of SQL WHERE IN Subquery clause?

SplunkTrust
SplunkTrust

You may want to have a look at the "Splunk SPL for SQL Users" manual (http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/SQLtoSplunk).

See if this gets you started.

index=orders vendor=* [ search index=events transaction_id=* | fields transaction_id | format ]
---
If this reply helps you, an upvote would be appreciated.
Highlighted

Re: Equivalent of SQL WHERE IN Subquery clause?

Path Finder

@ixixixspl so I assuming that transactionid is unique

 first query
| table transcation_id
| join type=left transcation_id [|search second query
                                     |table orders]

something like that but it gets a bit complicated depending on the fields you want to join but try it out see if it works

View solution in original post