Correlating two events based on extracted field values

[Ted-Splunk]

There is a process I'm trying to track. It starts by generating a single event. Then asynchronously a second event is created. My problem is that the async process often fails. I would like to find all occurences of the first event that do not have a corresponding second event.

I know how to search for each event independently. They share a couple common identifiers that can be extracted. I have tried a subsearch and a join but have not gotten any results. As a compressed and simplified example, here is my pseudo search

index=idx1 ... (identifiers here) | rex "EventId: (?<event_id>\d+)" | join type=left event_id [ search index=idx1 ... (identifiers here) | rex "\"EventId\",\"value\":\"(?<event_id>\d+)" ]

Both events occur at about the same time, usually within a second. They share the EventId extracted field which can be considered unique within the time period I'm searching. Limits are not an issue as this process occurs about 100 times a day.

So how can I list out the EventIds from the main search that do not have a match in the second search? Thank you experts!

[bowesmana · Accepted solution]

Building on what @ITWhisperer says about join, the logic for avoiding join is to use stats, so you want to do something like this

index=idx1 ... (identifiers here) 
| rex "EventId: (?<event_id_1>\d+)" 
| rex "\"EventId\",\"value\":\"(?<event_id_2>\d+)" 
| eval event_id=coalesce(event_id_1, event_id_2)
| stats values(*) as * count by event_id
| where count=1

so your two rex statements capture to their own fields and then you find the common field event_id with coalesce, then the stats count will count them.

Depending on what your data looks like and how many events you would actually get, the stats statement can be adjusted to get the correct count of your 2 distinct message types.

The values(*) as * carries all of the other fields you want to preserve through the stats, so use a fields statement before stats to restrict what you want out.

[Ted-Splunk]

I appreciate the response! I don't really understand how it works, but I was able to use your suggestion as a guide and came up with this. The two events have different source types so I needed the OR. I had always thought "count" was just for summing up on fields, yet here the field values I need are in the results. So I guess in my situation "where count=1" works because the primary event will always have a match. So a count of 1 means the primary search matched and the secondary didn't, not the other way around.

index=idx1 
(sourcetype=source1 "Queueing create notifications for EventId:") OR 
(NotificationService CREATED EVENT sourcetype=source2)
| rex "EventId: (?<event_id1>\d+) in client (?<client_id>\d+)" 
| rex "\"eventId\",\"value\":\"(?<event_id2>\d+)"
| eval event_id=coalesce(event_id1,event_id2) | fields client_id, event_id
| stats values(*) as * count by event_id
| where count=1

[bowesmana]

You got it!

You are right in that you would use OR to search dataset1 OR dataset2 OR datasetn...

and then the use of rex/eval then plays with the data as it passes through the pipeline. When the pipeline sees an event that matches your first dataset, the first rex will extract an event id to the field event_id1 and when it sees an event matching the second rex statement it will extract a field called event_id2 to that event.

At that point you will have n event, some with event_id1 and some with event_id2

So, then to create that common field which you can use stats on, the coalesce statement simply says that

- I am going to create a new field called event_id which will get its value from whichever of the two fields event_id1 and event_id2 is not null. If it sees the first event type, then event_id becomes event_id1 and for the second event, event_id2.

At that point, every event will now have a new field called event_id.

And you perfectly picked up on the use of the fields statement to retain the fields you wanted through the stats. The stats is simply counting the number of times it sees each event id, and your guess is correct in that when count=1, it must be because there is only one event type for that event_id - if you know in your case that event_id1 will always exist then you have your answer.

[ITWhisperer]

Firstly, try to avoid joins they are limited and slow.

Secondly, the most productive way of getting an answer to your question is to provide sample events (in raw form in a code block using the </> button) which demonstrate your issue, e.g. event which have matching event ids and events which don't.

It would also be useful to know what fields you already have extracted (so we don't have duplicate any extractions you already have set up).