How to filter small table results by large table results?

[queriousGeorge]

I have two searches that will return orderNumbers

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

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

[bowesmana]

@queriousGeorge 

You can do this is a single search for both data sets and then calculating

index=main "Failed insert" OR "Successful insert"
| eval isFailed=if(match(_raw, "Failed insert"), 1, 0)
| stats min(isFailed) as isFailed by orderNumber
| where isFailed=1

so is 'isFailed' is set to 1 if the event is a failed one and then by finding the minimum you can see that any order numbers that have a value 0 have succeeded at least once, where the minimum value of isFailed of 1 indicates a failure with no success.

[queriousGeorge]

This ended up working for me:


`index=main "failed insert" | stats count by orderNumber | eval previousSuccess=0 | table orderNumber previousSuccess | join type=left orderNumber [search index=main “successful insert” | eval previousSuccess=1 | table orderNumber previousSuccess] | table orderNumber previousSuccess | where previousSuccess=0`

I needed a count of not only the fails, but the fails with a previous success.

[bowesmana]

You should try to avoid join - and in almost all cases, you can with stats.

If you want only failures which have also succeeded, then replace my previous last 2 lines with

| stats values(isFailed) as isFailed by orderNumber
| where mvcount(isFailed)=2

 So, this will trigger when you have both a 0 and 1

join has limitations, it's slower and can cause unpredictable results depending on your data volume.

Note also that neither of these will deal with any time ordering of the events. You're looking for 'previous' success, but this will not show you whether the success comes before the failure.