Splunk Enterprise

Join 2 events with a unique ID

shashank_24
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

ITWhisperer
SplunkTrust
SplunkTrust

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

shashank_24
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

richgalloway
SplunkTrust
SplunkTrust

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

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

richgalloway
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, Karma would be appreciated.

ITWhisperer
SplunkTrust
SplunkTrust

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"

Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...