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!

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