Splunk Search

Nested SubQuery With NOT IN Clause

ayu2375
Engager

Hello,

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

SELECT transaction_id, vendor
FROM orders
WHERE transaction_id NOT IN (SELECT transaction_id FROM events).

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 do not share transaction_ids with the transaction_ids of the events log. Any help is greatly appreciated, thanks!

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

There are many ways to do this, but the most appropriate method depends a lot on the actual data.  You really want to illustrate the dataset and explain the outcome, maybe with mockups.  It is fine to illustrate some ideas using a different language.  But how would orders and events be represented in Splunk, for example?  Do transaction_id and vendor already exist in data sources or is there some maneuver to extract them?  Such details can make a difference because SPL is fundamentally different from SQL.

Assuming orders and events are from two sourcetypes and fields are already extracted, you can do

sourcetype IN (orders, events)
| stats values(sourcetype) as sourcetype values(vendor) as vendor by transaction_id
| where sourcetype == events and mvcount(sourcetype) > 1

 

richgalloway
SplunkTrust
SplunkTrust

Splunk has written a manual to help SQL users transition to SPL.  See https://docs.splunk.com/Documentation/Splunk/9.0.2/SearchReference/SQLtoSplunk

The NOT IN construct in SQL can be expressed as NOT and a subsearch.

index=orders vendor=* NOT [ index=events transaction_id=* | fields transaction_id ]
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...