Query Help !!

[rakesh_498115]

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

[Ayn · Accepted solution]

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 ]