Splunk Search

Equivalent of SQL WHERE IN Subquery clause?

ixixix_spl
Explorer

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!

0 Karma
1 Solution

kiamco
Path Finder

@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

View solution in original post

kiamco
Path Finder

@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
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, Karma would be appreciated.

kiamco
Path Finder

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

0 Karma

ixixix_spl
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...