Splunk Search

Query Help !!

rakesh_498115
Motivator

Hi

I have to use a query similar to sql like this in splunk.

select * from table1 where color NOT IN [select color from table2]

I have tried like this..but it didnt worked..plesae help

sourcetype="X" "OrderPending" Label_Text="Message sent to Customer" buyerID NOT in [search sourcetype="X" "RequestOrder" Label_Text="Message received from customer" | fields buyerID ]

I need to display all the records which doesnot cotain the buyerID in the requests message...

sourcetype="X" "OrderPending" Label_Text="Message sent to Customer"

sourcetype="X" "RequestOrder" Label_Text="Message received from customer"

These two are two seperate events...having buyerID in common...how do i write the query..pls help...

Tags (1)
0 Karma
1 Solution

Ayn
Legend

The subsearch will return a list of OR-separated search directives that is already formatted so that the search command can use it directly. For instance if your subsearch yields the values 1, 2 and 3 for the field buyerID, the subsearch will return

( ( buyerID="1" ) OR ( buyerID="2" ) OR ( buyerID="3" ) )

You can check this yourself by running this search as the main search and append | format at the end - this will give you the exact string that will be returned when using the search as a subsearch.

So, rewriting the query with this in mind, you want a search that gives you this:

sourcetype="X" "OrderPending" Label_Text="Message sent to Customer" NOT ( ( buyerID="1" ) OR ( buyerID="2" ) OR ( buyerID="3" ) )

This is satisfied by using the subsearch like I showed above. Like this:

sourcetype="X" "OrderPending" Label_Text="Message sent to Customer" NOT [search sourcetype="X" "RequestOrder" Label_Text="Message received from customer" | fields buyerID ]

View solution in original post

0 Karma

Ayn
Legend

The subsearch will return a list of OR-separated search directives that is already formatted so that the search command can use it directly. For instance if your subsearch yields the values 1, 2 and 3 for the field buyerID, the subsearch will return

( ( buyerID="1" ) OR ( buyerID="2" ) OR ( buyerID="3" ) )

You can check this yourself by running this search as the main search and append | format at the end - this will give you the exact string that will be returned when using the search as a subsearch.

So, rewriting the query with this in mind, you want a search that gives you this:

sourcetype="X" "OrderPending" Label_Text="Message sent to Customer" NOT ( ( buyerID="1" ) OR ( buyerID="2" ) OR ( buyerID="3" ) )

This is satisfied by using the subsearch like I showed above. Like this:

sourcetype="X" "OrderPending" Label_Text="Message sent to Customer" NOT [search sourcetype="X" "RequestOrder" Label_Text="Message received from customer" | fields buyerID ]
0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...