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]
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"
@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 🙂
If your problem is resolved, then please click the "Accept as Solution" button to help future readers.
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"
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"