Equivalent of SQL WHERE IN Subquery clause?

[ixixix_spl]

Hello,

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

SELECT transaction_id, 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 transaction_ids for orders in one search query and a list of transaction_ids for events in another search query, but my ultimate goal is to return order logs that have transaction_ids shared with the transaction_ids of the events log. Any help is greatly appreciated, thanks!

[kiamco · Accepted solution]

@ixixix_spl so I assuming that transaction_id 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

[richgalloway]

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 ]

[kiamco]

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

[ixixix_spl]

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