topic Re: How do I do SQL-like MINUS? in Splunk Search

How do I do SQL-like MINUS?

chatsai — Thu, 03 Nov 2016 22:40:59 GMT

All OrderId
This query gives all distinct orderID

basesearch  | dedup orderID | table orderID

This query gives all distinct orderID with status=success

basesearch status=success | dedup orderID | table orderID

How do I find a table with all the failed orderID with a single nested query?
I have tried the following query

| set diff [search basesearch  | dedup orderID | table orderID] [search basesearch status=sucess | dedup orderID | table orderID]

But it is not returning the failed orders.

Re: How do I do SQL-like MINUS?

Richfez — Thu, 03 Nov 2016 23:32:16 GMT

What is returned when you do

basesearch status!=sucess | dedup orderID | table orderID

basesearch status=failure | dedup orderID | table orderID

(By the way, be sure to use the correct criteria, either "success" or "sucess")

Re: How do I do SQL-like MINUS?

Richfez — Thu, 03 Nov 2016 23:59:00 GMT

Oh, should have mentioned one last option:

basesearch NOT status=sucess | dedup orderID | table orderID

Re: How do I do SQL-like MINUS?

chatsai — Fri, 04 Nov 2016 00:07:26 GMT

Oh.. I should have made some clarifications. There are multiple events associate with a single orderID. Some events have status=failure and some events have status=success. An orderID will be considered as successful if one of its event has status=success.

I am looking for orderID with all of its associate events with status=failure.

Re: How do I do SQL-like MINUS?

Richfez — Fri, 04 Nov 2016 00:14:34 GMT

No problem, I sort of figured that had to be a reason why it's not that easy. 🙂

But, maybe ...

basesearch | transaction orderID | search NOT status=success

There might be tweaking, but when you create a transaction out of these, if there are multiple values for a particular status it will add them all. (If there's only one, it collapses it to a single entry). So then you should be able to search where there is no "status=success" in the piled-up transaction like we do.

There are other ways to accomplish this, but that one might work.

Re: How do I do SQL-like MINUS?

gokadroid — Fri, 04 Nov 2016 00:51:45 GMT

Given that:
1) There are multiple events associated with a single orderID where orderID acts as primary key to track these events.
2) orderID is considered success if even one event is successful
3) Looking for orderID with all of its associate events with status=failure

then can you try this:

your base query NOT [ your base query status=success | dedup orderID | table orderID ]
  | dedup orderID
   | table orderID

this should remove all the orderID related events from the search where you had atleast one success. The remaining ones are the ones with neither success nor partial success.