Splunk filter one search by another

I have two searches that will return orderNumbers

search1

index=main "Failed insert" | table orderNumber
//returns small list

search2

index=main "Successful insert" | table orderNumber
//returns huge list


I want a list of "Failed insert" orderNumbers that have NOT had a "Successful insert" previously. How can I use the results of the second search to filter the results of the first search? 

I tried:


index=main "Failed insert"
NOT [
index=main "Successful insert" | table orderNumber]
| dedup orderNumber
| table orderNumber

...but it returned orderNumbers that did have "Successful insert" events.

I believe this is what you're looking for:

index=main sourcetype=srctp orderNumber=* "failed insert" NOT
    [search index=main sourcetype=srctp orderNumber=* "successful insert"
    | stats count by orderNumber
    | fields - count ]
| stats count by orderNumber
| fields - count

First, stats is going to be a lot more performant than dedup

Second, so long as your "successful insert" search is 10k items or fewer, it should complete

If it's longer than 10k items, you may need to do something like this:

index=main sourcetype=srctp orderNumber=* ("failed insert" OR "successful insert")
| rex field=_raw "(?<insert>\w+ )insert"
| stats values(insert) as inserts by orderNumber
| search inserts="*failed*"
| where mvcount(inserts)<2

What this should do is extract the type of insert ("failed" or "successful") into a new field named insert

Then stats values() all of the insert types each orderNumber had

Then ensure we're only looking at orderNumber entries that have a "failed" insert

Then check to make sure there is only one entry in the values()'d field (ie there is no "successful" entry present