Join 2 events with a unique ID

[shashank_24]

Hi I need some help with the join command. I have 2 events as below -

1st Event -

2020-09-03 12:50:01,811|catalina-exec-173|INFO|LoggingService|RESPONSE status=404 api=[/checkout/details]" error="detail_not_found"

2nd Event -

2020-09-03 12:54:50,915|catalina-exec-137|INFO|OrderService|Order placed successfully

 Both events have a common field uniqueID. Now what I want is extract the uniqueID's for a scenario where the 1st event occurred but the 2nd event didn't.  Means number of users got the error and didn't proceed to place the order.

I have the below query using which I was able to join the 2 events but what i want is opposite.

Can someone advice.

index=my_test host="server-1" "OrderService|Order placed successfully"
| join uniqueId max=0 
    [ search index=my_test host="server-1" status=[404] "api=[/checkout/details]" error="detail_not_found"
    | dedup uniqueId]

[shashank_24]

@ITWhisperer Thank you for this brilliant suggestion. I tried this and it worked by using fullnull and left join.
@richgalloway Thank you for the query. Stats also worked. I will actually need it when i run it for longer period as for that the sub-search won't work due to limitation.

So Both of you are stars  and saved my day 🙂

[richgalloway]

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

[richgalloway]

Sometimes join is not the answer.  Try using stats.

```Search for both successful and error events```
index=my_test host="server-1" ("OrderService|Order placed successfully" OR (status=[404] "api=[/checkout/details]" error="detail_not_found"))
```Pair the events by ID```
| stats values(*) as * by uniqueId
```Return event pairs that were not successful```
| search NOT "Order placed successfully"

[ITWhisperer · Accepted solution]

One way to approach it is to have your first search be the error events and do a left join with the search for the success events. If you evaluate a field in the success search that doesn't exist in the error search, and use fillnull to set this field to a recognised and different value (e.g. "No Order"), then you can find the events where this field is "No Order"