Splunk Enterprise

Join 2 events with a unique ID

Path Finder

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]

 

Labels (1)
Tags (3)
0 Karma
1 Solution

Builder

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"

View solution in original post

Path Finder

@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 🙂

0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

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"
---
If this reply helps you, an upvote would be appreciated.

Builder

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"

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!