topic Nested SubQuery With NOT IN Clause in Splunk Search

Nested SubQuery With NOT IN Clause

ayu2375 — Thu, 24 Nov 2022 18:14:03 GMT

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!

Re: Nested SubQuery With NOT IN Clause

richgalloway — Fri, 25 Nov 2022 02:07:37 GMT

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 ]

Re: Nested SubQuery With NOT IN Clause

yuanliu — Fri, 25 Nov 2022 02:33:51 GMT

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